<jupyter_start><jupyter_text>COVID CASES<jupyter_code># Create reference to CSV file
csv_path = "Resources/COVID - State - Daily.csv"

Covid_bystate_df = pd.read_csv(csv_path, low_memory=False)
Covid_bystate_df.head()

#Drop COVID Columns - state
covid_cases_df = Covid_bystate_df.drop(columns=['death_count', 'new_death_count', 'test_count', 'case_rate', 'death_rate', 'test_rate', 'new_positives_rate', 'new_test_count', 'new_case_rate', 'new_death_rate', 'new_test_rate'])
covid_cases_df.head()
# covid_data = covid_cases_df.loc[covid_cases_df["month"].isin([2,3,4,5,6, 7, 8])] 
covid_data = covid_cases_df.loc[covid_cases_df["month"].isin([2,3,4,5,6, 7, 8])]
covid_data. head()
#join both DataFrame to get state names on the covid case dataframe
cases_by_state = pd.merge(covid_data,fips_df, on=["statefips"])
cases_by_state.head()
#Query data types: Note that new case count has some objects
# covid_byState.dtypes
cases_by_state.dtypes
#Replace "." by 0 - create a new dataframe
# covid_byState.replace(to_replace =".",
#                  value =0) 
cases_by_state.replace(to_replace =".",
                 value =0)
cases_by_state.head()
cases_by_state.dtypes
# covid_byState.new_case_count=covid_byState.new_case_count.astype('int64')
cases_by_state.new_case_count = cases_by_state.new_case_count.astype('float64')
# covid_byState.dtypes
cases_by_state.dtypes
#concatenate month, date and year to create a date

cases_by_state['date'] = cases_by_state['month'].astype(str) + '-' + cases_by_state['day'].astype(str) + '-' + cases_by_state['year'].astype(str) 
cases_by_state.head()
#convert date column into datetime format
cases_by_state['date'] = pd.to_datetime(cases_by_state["date"].astype(str))
cases_by_state.head()
#Drop Year, month and date
cases_by_state.drop({'year', 'month', 'day'}, axis=1, inplace=True)

#Rearrange columns
cases_by_state[['date', 'statefips', 'state_name', 'case_count', 'new_case_count']]
cases_by_state.head()
#7 days average
# covid_SB_rev["Bi - revenue_all"] = covid_SB_rev.iloc[:,2].rolling(window=30).mean()
# covid_SB_rev["Bi - revenue_ss40"] = covid_SB_rev.iloc[:,3].rolling(window=30).mean()
# covid_SB_rev["Bi - revenue_ss60"] = covid_SB_rev.iloc[:,4].rolling(window=30).mean()
# covid_SB_rev
cases_by_state.to_csv('state covid_data.csv', header=True, index=False) 
cases_by_state.to_json(r'C:\Users\ideuk\Desktop\ETL project\state covid_data.json')<jupyter_output><empty_output><jupyter_text>CONNECT TO POSTGRESS - From previous project - to remove?<jupyter_code>rds_connection_string = f"postgres:postgres@localhost:5432/ETL_project"
engine = create_engine(f'postgresql://{rds_connection_string}')
engine.table_names()
#table_name = 'covid_data'
cases_by_state.to_sql(name='covid_data', con=engine, if_exists='replace', index=False)
#table_name = 'revenue_data'
revenue_by_state.to_sql(name='revenue_data', con=engine, if_exists='replace', index=False)
#Confirm data has been added by querying the customer_location table

pd.read_sql_query('select * from covid_data', con=engine).head()
#Confirm data has been added by querying the customer_location table

pd.read_sql_query('select * from revenue_data', con=engine).head()
#Join revenue and covid data with statefips id and save it as a dataframe

# covid_revenue = pd.read_sql_query('SELECT covid_data.year,covid_data.month,covid_data.day, covid_data.statefips,covid_data.new_case_count, revenue_data.revenue_all FROM covid_data INNER JOIN revenue_data ON covid_data.statefips=revenue_data.statefips;', con=engine)
# covid_revenue<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13499859668818412, 'fraction_numerical': 0.011226494527083918, 'mean_word_length': 7.650485436893204, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 9, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '593943', 'license': 'MIT', 'n_tokens_mistral': 1361, 'n_tokens_neox': 1332, 'n_words': 231, 'path': 'TEST/Clean up SB & covid/SB Revenue & COVID.ipynb', 'repo_name': 'LoriWard/COVID-Trends-Website', 'size': '3563'}