<jupyter_start><jupyter_text>Step 3 - Prepare Data - Task 4 - Transform DataThis notebook provides the Python code for taking Covid total cases at county level and creating state level aggregation.  It demonstrates how you can transform the data, sort the data, and engineer new features.  Its results are stored in an intermediate file for rest of exercises.Students will be developing a similar notebook for total deaths.  The corresponding notebook is included in the answer section.  Import Libraries<jupyter_code>import os
import pandas as pd
from datetime import date<jupyter_output><empty_output><jupyter_text>Set up Environment Flag<jupyter_code>using_Google_colab = False
using_Anaconda_on_Mac_or_Linux = True
using_Anaconda_on_windows = False

if using_Google_colab:
    dir_input = "/content/drive/MyDrive/COVID_Project/input"
if using_Anaconda_on_Mac_or_Linux:
    dir_input = "../input"
if using_Anaconda_on_windows:
    dir_input = r"..\input"<jupyter_output><empty_output><jupyter_text>Connect to Google DriveThis step will only be executed if you have set environment flag using_Google_colab to True<jupyter_code>if using_Google_colab:
    from google.colab import drive
    drive.mount('/content/drive')<jupyter_output><empty_output><jupyter_text>PD4.1 (Activity 1) Read file in your chosen environment<jupyter_code>df_total_cases = pd.read_csv(os.path.join(dir_input, "USA_Facts", "covid_confirmed_usafacts.csv"))
df_total_cases = df_total_cases.astype({'countyFIPS': str}).astype({'stateFIPS': str})
df_total_cases<jupyter_output><empty_output><jupyter_text>PD 4.1 (Activity 2) Select data for LA County<jupyter_code>df_total_cases_LA = df_total_cases[df_total_cases['County Name'] == 'Los Angeles County']
df_total_cases_LA<jupyter_output><empty_output><jupyter_text>PD 4.1 (Activity 3) Transform LA County data to total cases by date<jupyter_code>df_total_cases_LA_by_date = df_total_cases_LA.melt(id_vars=['State', 
                                                            'stateFIPS', 
                                                            'County Name',
                                                            'countyFIPS'],
                                                   var_name='Date', 
                                                   value_name='Total Cases')
df_total_cases_LA_by_date<jupyter_output><empty_output><jupyter_text>PD 4.2 (Activity 4) Transform all County data to total cases by date<jupyter_code>df_total_county_cases_by_date = df_total_cases.melt(id_vars=['State', 
                                                      'stateFIPS', 
                                                      'County Name',
                                                      'countyFIPS'],
                                             var_name='Date', 
                                             value_name='Total Cases')
df_total_county_cases_by_date<jupyter_output><empty_output><jupyter_text>PD4.3 (Activity 5)  Group total cases by state<jupyter_code>df_total_cases_by_state = df_total_cases.groupby(['State', 'stateFIPS']).sum().reset_index()
df_total_cases_by_state<jupyter_output><empty_output><jupyter_text>PD 4.3 (Activity 6) Transform state total cases to total cases by date<jupyter_code>df_total_cases_by_state_by_date = df_total_cases_by_state.melt(id_vars=['State','stateFIPS'], 
                                                               var_name='Date', 
                                                               value_name='Total Cases')
df_total_cases_by_state_by_date<jupyter_output><empty_output><jupyter_text>Bonus Activity - Pivot is the reverse of melt - you can revert back using pivot_table<jupyter_code>df_pivot_table = pd.pivot_table(df_total_cases_by_state_by_date, 
                                values='Total Cases', 
                                columns='Date', 
                                index=['State','stateFIPS'])
df_pivot_table<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07573844988639232, 'fraction_numerical': 0.005049229992426155, 'mean_word_length': 2.601818181818182, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 41, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 24, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '622089', 'license': 'MIT', 'n_tokens_mistral': 1151, 'n_tokens_neox': 1103, 'n_words': 243, 'path': 'Notebook-Class-exercises/Step-3-Prepare-Data-Task-4-Transform.ipynb', 'repo_name': 'drarvindsathi/covid_project', 'size': '3961'}