<jupyter_start><jupyter_text>Aspatial data manipulation: Numpy and Pandas  NumpyNumPy is the fundamental package for scientific computing in Python. It is a Python library that provides a multidimensional array object, various derived objects (such as masked arrays and matrices), and an assortment of routines for fast operations on arrays, including mathematical, logical, shape manipulation, sorting, selecting, I/O, discrete Fourier transforms, basic linear algebra, basic statistical operations, random simulation and much more.You can import the numpy library as shown below:```pythonimport numpy as np```source: https://numpy.org/doc/stable/<jupyter_code>import numpy as np<jupyter_output><empty_output><jupyter_text>The core of the `numpy` package is the `array` class. Let's examine that first. We can make an array out of a sequence, like a list.<jupyter_code>d = [1, 2, 3, 4, 5, 6]
np.array(d)<jupyter_output><empty_output><jupyter_text>Note that arrays must be "homogeneous", unlike `list`, in that the data types of each element must be the same. The data type of the array is upcast to be able to represent all of the data. So, if only one element is a float, all elements will be converted to floats.<jupyter_code>d = [1, 2, 3.1415, 4, 5, 6]
np.array(d)<jupyter_output><empty_output><jupyter_text>Arrays are like multidimensional sequences. We can create a 2D array by supplying a list of lists as the argument.<jupyter_code>arr = np.array([[1., 2., 3.,], [4., 5., 6.]])
print(arr.shape)
arr<jupyter_output><empty_output><jupyter_text>You can set the `array.shape` attribute to change the shape of the array. This attribute does not change the elements of the array, or how it is stored in memory, just how it is seen.<jupyter_code>arr.shape = (3, 2)
print(arr.shape)
arr<jupyter_output><empty_output><jupyter_text>--- *Exercise*1. Create an array, named `arr1`, that has value from 1 through 9. 2. Assign `arr1` to `arr2` and resize the array into 3 by 3 (3 rows and 3 columns). ---<jupyter_code># Your code here

""" Test code for the previous function. This cell should NOT give any errors when it is run."""

# Check your result here. 
assert np.array_equiv(arr1, np.array(list(range(1, 10))))
assert arr2.shape == (3,3)

print("Success!")<jupyter_output><empty_output><jupyter_text>Pandas`pandas` is a Python package providing fast, flexible, and expressive data structures designed to make working with “relational” or “labeled” data both easy and intuitive. It is built on top of `NumPy` and is intended to integrate well within a scientific computing environment with many other 3rd party libraries.You can import the `pandas` library as shown below:```pythonimport pandas as pd``` Data Structure| Diemensions | Name | Description | Usage || :-: | :-: | :-: | :-: || 1D | Series | 1D labeled homogeneously-typed array | pd.Series() || 2D | DataFrame | General 2D labeled, size-mutable tabular structure  with potentially heterogeneously-typed column | pd.DataFrame() |source: https://pandas.pydata.org/docs/getting_started/overview.html  Difference between Numpy and Pandas| Comparision | Numpy | Pandas | | :-: | :-: | :-: || Input data | Preferable for Numerical data | Prefereable for Tabular data || Objects | Array | Data frame and Series || Efficiency | Memory efficient | Comsume more memory || Data size | Better performance for 50 K or less | Better performance for 500 K or more rows || Indexing | Very fast indexing | Relatively slow indexing || Data demension | Can represent up to 2D with DataFrame  Panel used to handle 3D data but deprecated in 0.24.0 | Multi-dimension data presentation  with multi-dimensional arrays (ndarray) |source: https://www.geeksforgeeks.org/difference-between-pandas-vs-numpy/<jupyter_code>import pandas as pd
pd.__version__ # Check your pandas version<jupyter_output><empty_output><jupyter_text>Mockup AnalysisHere is the narrative of our mockup analysis.  Problem statement:Due to the lack and shortage of healthcare resources, many COVID-19 patients could not receive timely and adequate treatments, resulting in more fatality from the disease.  Goal of the analysis:We as researchers suppose that insufficient healthcare availability causes more fatality from COVID-19, so we are investigating the relationship between the availability of insensitive care unit (ICU) beds and the case-fatality ratio of COVID-19.  Data employed: * (Daily collected) confirmed case and fatality of COVID-19 per county* (Daily collected) available ICU beds count per Trauma Service Area (TSA*)* Lookup table for counties associated with each TSA\* TSAs are the regions aggregating 254 counties in Texas into 22 Regional Advisory Council that sets the guidelines for the trauma care system. ![TSA Map in Texas](https://www.dshs.texas.gov/assets/0/76/110/783/8589936060/8589938383/554b3559-ab9d-4461-82e9-61b9ef17b4a4.jpg)Data source: https://dshs.texas.gov/coronavirus/AdditionalData.aspx Steps* Import and check the input data * Aggregate county-level data (daily confirmed cases and death) into Trauma Service Area (TSA) for the future comparison* Compute 7-days moving mean* Conduct a correlation analysis  1. Importing dataYou can import existing dataset with the various formats, such as excel, csv, tsv, or json, but not limited to.  Check out https://pandas.pydata.org/docs/reference/io.html.  Covered functions*  pd.read_excel()  *  pd.read_csv() *  pd.read_json() *  df.astype() *  df.head() *  df.set_index()<jupyter_code>daily_death = pd.read_excel('./data/daily_death.xlsx') # read an excel file
daily_death = daily_death.set_index('County') # specify the index of this table so that we can call the value easily in the later computational process
daily_death = daily_death.astype(float) # change the data type of cells as float (from integer)
daily_death # this will call the instance of table
daily_case = pd.read_csv('./data/daily_case.csv') # read a csv file
daily_case = daily_case.set_index('County')
daily_case.head(10) # df.head() function will call the top(n) rows
icu_empty = pd.read_csv('./data/icu_empty.tsv', sep='\t')  # we can also load tsv (tap seperated value) with read_csv() function
icu_empty = icu_empty.set_index('TSA')
icu_empty  # in case the table is not long enough, it will show the entire table. 
tsa_county = pd.read_json('./data/tsa_county.json') # load a json file
tsa_county.head() # df.head() function calls five rows as a default value<jupyter_output><empty_output><jupyter_text>2. Aggregate county-level data into Trauma Service Area (TSA) Given that the COVID-19 related data is collected based on the county level and the ICU availability data is based on the TSA level, we need to match their spatial resolution.  covered functions*  pd.DataFrame()  *  df.unique() or  Series.unique()  *  df.columns  *  df.iterrows()  *  Series.to_list()  Method 1: create an empty dataframe and enter data for each row (maybe a verbose appraoch)<jupyter_code># create an empty dataframe having TSA as the index and dates as column headers
daily_death_tsa = pd.DataFrame(index=tsa_county['TSA'].unique(), columns=daily_death.columns)
daily_death_tsa
for idx, row in daily_death_tsa.iterrows():  # iterating each row of the dataframe
    temp_list = tsa_county.loc[tsa_county['TSA'] == idx, 'County'].to_list() # Return associate county of TSA into a list
    daily_death_tsa.loc[idx] = daily_death.loc[temp_list].sum()  # Sum the value (death) of counties 
    
daily_death_tsa.head()<jupyter_output><empty_output><jupyter_text>Slicing dataframes*  df.at : Access a single value for a row/column label pair.*  df.loc : Access a group of rows and columns by label(s).*  df.iat : Access a single value for a row/column pair by integer position.*  df.iloc : Access a group of rows and columns by integer position(s).<jupyter_code>daily_death_tsa.at['A', '06/01/2020']
daily_death_tsa.iat[0, 0]
daily_death_tsa.loc['A', ['06/01/2020', '06/02/2020', '06/03/2020']]
daily_death_tsa.iloc[0, 0:3]<jupyter_output><empty_output><jupyter_text>--- *Exercise*1. Slice the fatality of July 4, 2020, in TSA Q, and assign the result as `df1`.2. Sum the fatality between December 29, 2020, and December 31, 2020, in TSA E, and assign the result as `f1`.---<jupyter_code># Your code here


""" Test code for the previous function. This cell should NOT give any errors when it is run."""

# Check your result here. 
assert df1 == 34
assert f1 == 279

print("Success!")<jupyter_output><empty_output><jupyter_text>Method 2: using 'Groupby' function of dataframe covered functions*  df.join() *  df.drop() *  df.groupby()<jupyter_code># In order to use `groupby()` function, we need to have the TSA names in the dataframe. 
daily_case_tsa = daily_case.join(tsa_county.set_index('County')) # Join tsa_county table into daily_case table based on the index (Couunty)
daily_case_tsa = daily_case_tsa.drop(columns=['FIPS']) # Remove an uncessary column for groupby, which is FIPS code
daily_case_tsa
daily_case_tsa = daily_case_tsa.groupby(by='TSA').sum() # This function groups all the cells in the dataframe based on 'TSA' and return the sum.
daily_case_tsa<jupyter_output><empty_output><jupyter_text>3. Moving 7 days meanGiven the fact that the census is usually collected during the weekdays, we may want to smooth the temporal fluctuation to avoid the potential bias from the days that the census took place.  covered functions*  df.plot()  or  Series.plot() *  df.copy() *  df.fillna() *  df.transpose() *  df.shape import  datetime ,  tqdm *  datetime.datetime.strptime() *  datetime.timedelta() * tqdm(df.iterrows(), total=df.shape[0])<jupyter_code># Too much dynamic temporal fluctuation from the raw data
daily_death_tsa.loc['E'].plot()
# Same here. 
daily_case_tsa.loc['E'].plot()
import datetime  # a package to manipulate date and time data
# First, we create a list that has all the target dates
from_date = '06/01/2020'
to_date = '12/31/2020'

start_date = datetime.datetime.strptime(from_date, "%m/%d/%Y")  # Function to change inputs from string to datetime
end_date = datetime.datetime.strptime(to_date, "%m/%d/%Y")
print(from_date, type(from_date))
print(start_date, type(start_date))

focus_dates = []
delta = datetime.timedelta(days=1) # determine the gap between two dates as a day
print(delta, type(delta))

while start_date <= end_date:
    focus_dates.append(start_date.strftime("%m/%d/%Y"))
    start_date += delta
    
print(focus_dates)
# Make a dictionary that has keys as target date and values as the date that should be averaged. 
focus_date_dict = {}
time_delta = [3, 2, 1, 0, -1, -2, -3]

for idx, date in enumerate(focus_dates):
    temp_list = []
    for delta in time_delta:
        temp_list.append(
            str(
                (datetime.datetime.strptime(focus_dates[idx], "%m/%d/%Y") - datetime.timedelta(days=delta)
                ).strftime("%m/%d/%Y"))
        )
        
    focus_date_dict[date] = temp_list
    
# Manually enter the dates that would have missing values
focus_date_dict['06/01/2020'] = ['06/01/2020', '06/02/2020', '06/03/2020', '06/04/2020']
focus_date_dict['06/02/2020'] = ['06/01/2020', '06/02/2020', '06/03/2020', '06/04/2020', '06/05/2020']
focus_date_dict['06/03/2020'] = ['06/01/2020', '06/02/2020', '06/03/2020', '06/04/2020', '06/05/2020', '06/06/2020']
focus_date_dict['12/29/2020'] = ['12/26/2020', '12/27/2020', '12/28/2020', '12/29/2020', '12/30/2020', '12/31/2020']
focus_date_dict['12/30/2020'] = ['12/27/2020', '12/28/2020', '12/29/2020', '12/30/2020', '12/31/2020']
focus_date_dict['12/31/2020'] = ['12/28/2020', '12/29/2020', '12/30/2020', '12/31/2020']
print(focus_date_dict)
from tqdm import tqdm  # useful package to estimate computation time of loops
# Another verbose approach (looping through rows)
mean_death_tsa = daily_death_tsa.copy(deep=True)

# Iterating through the rows and tqdm package estimates the total computational time
for idx, row in tqdm(mean_death_tsa.iterrows(), total=mean_death_tsa.shape[0]): 
    for col in mean_death_tsa.columns: # Iterate through column here
        mean_death_tsa.at[idx, col] = daily_death_tsa.loc[idx, focus_date_dict[col]].mean()

mean_death_tsa.head()
# Maybe a better approach
mean_case_tsa = daily_case_tsa.copy(deep=True)

for col in mean_case_tsa.columns: # Just iterate through column (no rows iteration)
    # Simply assigned the mean value to the entire Series
    mean_case_tsa[col] = daily_case_tsa[focus_date_dict[col]].mean(axis=1) 

mean_case_tsa
# result of moving mean
mean_case_tsa.loc['E'].plot()
daily_case_tsa.loc['E'].plot()
# To compute using two dataframe that has the same structure, you can simply do the below. 
mean_ratio = mean_death_tsa / mean_case_tsa * 100
mean_ratio = mean_ratio.fillna(0)
mean_ratio.head()
mean_ratio.transpose().plot(figsize=(20,10))
# Calculate 7 days mean for the ICU beds availability
mean_icu = icu_empty.copy(deep=True)

for col in icu_empty.columns:
    mean_icu[col] = icu_empty[focus_date_dict[col]].mean(axis=1)

mean_icu
mean_icu.transpose().plot(figsize=(20, 10))<jupyter_output><empty_output><jupyter_text>4. Correlation analysis: Examine the relationship between ICU availability and COVID-19 fatality covered functions*  df.corr()*  df.plot.scatter()<jupyter_code>for_plot = pd.DataFrame(columns=['case_fatality_ratio', 'empty_ICU_ratio'])

for idx, row in tqdm(mean_icu.iterrows(), total=mean_icu.shape[0]):
    for col in mean_icu.columns:
        for_plot = for_plot.append({'case_fatality_ratio': mean_ratio.loc[idx, col], 'empty_ICU_ratio': mean_icu.loc[idx,col]} ,ignore_index=True)

for_plot
for_plot.plot.scatter('case_fatality_ratio', 'empty_ICU_ratio', figsize=(10,10))
for_plot.corr()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11042899948841628, 'fraction_numerical': 0.0391726960461887, 'mean_word_length': 5.004387889425186, 'pattern_counts': {'":': 0, '<': 58, '<?xml version=': 0, '>': 57, 'https://': 6, 'lorem ipsum': 0, 'www.': 2, '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': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '297185', 'license': 'MIT', 'n_tokens_mistral': 4849, 'n_tokens_neox': 4343, 'n_words': 1481, 'path': 'Week3/Aspatial_data_manipulation.ipynb', 'repo_name': 'jparkgeo/GEOG489', 'size': '13683'}