<jupyter_start><jupyter_text>Midterm leyu,lin(Jack) 817372912 [![]( https://img.shields.io/badge/Python-v3.7-orange)]()[![](https://img.shields.io/badge/pandas-v1-blue)]()[![](https://img.shields.io/badge/sklearn-v0.2-red)]()  Imports<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pylab as plt
import seaborn as sns
from datetime import datetime
import os 
import glob
from sklearn import tree
from sklearn.feature_extraction import DictVectorizer
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import LabelEncoder
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import accuracy_score<jupyter_output><empty_output><jupyter_text>Data File Paths<jupyter_code>covid_confirmed_usafacts = 'covid_confirmed_usafacts.csv'
covid_county_population_usafacts = 'covid_county_population_usafacts.csv'
covid_deaths_usafacts = 'covid_deaths_usafacts.csv'

name_path = 'names'<jupyter_output><empty_output><jupyter_text>Create Panda DataFrame<jupyter_code>df_case = pd.read_csv(covid_confirmed_usafacts)
df_population = pd.read_csv(covid_county_population_usafacts)
df_deaths = pd.read_csv(covid_deaths_usafacts)<jupyter_output><empty_output><jupyter_text>Questions  1. Compute the total confirmed cases per day in the country. Produce a table and a line plot.<jupyter_code># select columns need to sum up
all_cases = df_case[[col for col in df_case.columns if '2020' in col]].copy()

# sum up columns
all_cases.loc['total_case'] = all_cases.apply(lambda x: x.sum())
#display result
df_q1= all_cases.iloc[-1:]

#create new table with date and cases
df_q1 = df_q1.stack().reset_index(-1).iloc[:, ::-1]
df_q1.columns = ['total_cases', 'date']

#line plot
df_q1.plot(x='date', y='total_cases',rot=45,
                title='Confirmed cases per day in USA',
                grid=True,
                style='*-',
                figsize=(10,6))

df_q1 = df_q1[['date','total_cases']]
df_q1.reset_index(drop=True, inplace=True)
df_q1<jupyter_output><empty_output><jupyter_text>2. Compute the total confirmed cases per week in the country. Produce a table of results. Plot the results using a log line plot. That is the log of the number of cases. A log plot of an exponential process will produce a straight line. Does the resulting plot look like an exponential?<jupyter_code>dt_date =  pd.to_datetime(df_q1['date'])
dt_weeks = dt_date.dt.week

#get total case per weeks by take max of each week
df_cases_weeks = df_q1.groupby(dt_weeks)['total_cases'].max().reset_index()
cases_weeks = df_cases_weeks.rename(columns={'date':'weeks'})

cases_weeks.plot(x='weeks', y='total_cases',
                title='Confirmed cases per week in USA',
                logy=True,
                grid=True,
                style='o-',
                figsize=(8,6))

cases_weeks<jupyter_output><empty_output><jupyter_text>Does the resulting plot look like an exponential?* The results seems partial exponential, from above fig can tell from week 9 to 14 almost a staright line. There was tremendous increasing from week 9 to week 14  3. Compute the number of new confirmed cases per week in the the country. Produce a table of results.<jupyter_code># compute new cases use last week subtract previous week
list_new_case = []
list_week =[]
for i in range(len('total_cases')):
    val = cases_weeks.loc[i+1] - cases_weeks.loc[i]
    period = (cases_weeks['weeks'].loc[i],cases_weeks['weeks'].loc[i+1])  
    list_new_case.append(val)
    list_week.append(period)
df_new_case = pd.DataFrame(list_new_case)
df_period = pd.DataFrame(list_week)
df_new_case= pd.concat([df_new_case, df_period], axis=1)

df_new_case.columns =['period',
                      'new_cases',
                      'previous_week',
                      'next_week']

df_new_case[['previous_week','next_week','period','new_cases']]<jupyter_output><empty_output><jupyter_text>4. Repeat 2 with the number of known deaths.<jupyter_code># count deaths cases for each day 
day_deaths = df_deaths[[col for col in df_deaths.columns if '2020' in col]].copy()
day_deaths.loc['total_deaths'] = day_deaths.apply(lambda x: x.sum())

#similar to question 1 and question 2
day_deaths= day_deaths.iloc[-1:]
day_deaths = day_deaths.stack().reset_index(-1).iloc[:, ::-1]
day_deaths.columns = ['known_deaths', 'date']

# count deaths per week
dt_date =  pd.to_datetime(day_deaths['date'])
dt_weeks = dt_date.dt.week
df_deaths_weeks = day_deaths.groupby(dt_weeks)['known_deaths'].max().reset_index()
deaths_weeks = df_deaths_weeks.rename(columns={'date':'weeks'})

# pick death is 'known' or greater than 0
deaths_weeks = deaths_weeks[deaths_weeks['known_deaths'] > 0]

# line plot
deaths_weeks.plot(x='weeks', y='known_deaths',
                title='Confirmed deaths per week in USA',
                logy=True,
                grid=True,
                style='o-',
                figsize=(8,6))

deaths_weeks<jupyter_output><empty_output><jupyter_text>Does the resulting plot look like an exponential?* The results seems almost exponential, from above fig can tell from week 10 to 14 is a staright line. There was tremendous increasing from week 10 to week 14  5. Find the 10 counties with the most known number of covid-19 cases. Plot the number of cases each week. What are the differences or similarities?<jupyter_code># select columns need to compare
counties_cases = df_case[[col for col in df_case.columns if '2020' in col]]

# find out total cases by counties
df_case['counties_cases'] = counties_cases.apply(lambda x: x.max(), axis =1)
df_counties_cases = df_case.groupby(['County Name','State'])[
                                'counties_cases'].sum().reset_index()

# show most 10 cases of counties, filter out 'unknown counties'
df_most10_counties = df_counties_cases[df_counties_cases['County Name']
                              .str.contains('County')].nlargest(10,'counties_cases')
df_most10_counties

df_counties = df_case.groupby('County Name').sum().reset_index()
df_top10_counties = df_counties[df_counties['County Name']
                               .str.contains('County')].nlargest(10,'counties_cases')

#transpose and extract week to create better table
df_top10_T = df_top10_counties.iloc[:,3:-1].T
dt_index = pd.to_datetime(df_top10_T.index)
df_top10_T.index = dt_index.week
df_top10_T

df_week_deaths = df_top10_T.groupby(df_top10_T.index).max()
# assign column names
df_week_deaths.columns = df_top10_counties['County Name']

fig, axes = plt.subplots(nrows=2, ncols=1)
#line plot
ax1 = df_week_deaths.plot(title='Top 10 known cases counties per week',
                         figsize=(14,16),
                         style='.-',
                         drawstyle="steps",
                         ax=axes[0],
                         grid=True,
                         linewidth=0.5)

ax2 = df_week_deaths.plot(title='Top 10 known cases counties per week',
                         figsize=(14,16), 
                         style='o-',
                         ax=axes[1],
                         grid=True,
                         linewidth=0.5)
ax1.set_xlabel("Weeks(fig1)")
ax2.set_xlabel("Weeks(fig2)")
plt.show()<jupyter_output><empty_output><jupyter_text>*  Differences     * NY and seattle (Queens county/ Kings County) has tremendous increased after week 13 from fig 1*  Similarities    * all counties Covid cases was growing start at week 10 both fig 1 and 2    * before weeks 10 there was most unknown (Incubation period likely 4-9 weeks)    * the density is very high during week 11 to week 12 from figh 1  6. Compute the number of known cases per population in each county that have cases ofcovid-19. Produce a bar graph and table of the results for the 10 counties with the highest values and the 10 counties with the lowest values.<jupyter_code># merge population and case dataframe
df_case_ppl= pd.concat([df_case, df_population['population']], axis=1)

# select row need to sum up
rows_county = df_case_ppl[[col for col in df_case.columns if '2020' in col]]

# sum up all period get total cases
df_case_ppl['county_cases'] = rows_county.apply(lambda x: x.max(), axis =1)

# sum up population and cases by county
df_total_case = df_case_ppl.groupby(['County Name','State'])[
                                'county_cases'].sum().reset_index()
df_total_population = df_case_ppl.groupby(['County Name','State'])[
                              'population'].sum().reset_index()
df_tc_population = pd.concat([df_total_case,
                              df_total_population['population']],
                              axis=1)

#take out unknown counties
df_tc_population = df_tc_population[df_tc_population['County Name']
                              .str.contains('County')]

# select population is above 0 consider  as "known"
# select cases is above 0 consider  as "known"
df_tc_population = df_tc_population[
                   df_tc_population['population'] > 0]
df_tc_population = df_tc_population[
                   df_tc_population['county_cases'] > 0]

# calculate cases per population
df_tc_population['case_per_population'] =\
                df_tc_population['county_cases']/df_tc_population['population']* 100000
#   10 highest values counties
df_top10 = df_tc_population.nlargest(10,'case_per_population')

# bar plot
df_top10.plot.bar(x='County Name', y='case_per_population', 
                  rot=45, title='Top 10 counties (cases/per 100,000 ppl)',
                  figsize=(10,6))
df_top10
# 10 lowest values counties
df_min10 = df_tc_population.nsmallest(10,'case_per_population')

# bar plot
df_min10.plot.bar(x='County Name', y='case_per_population', 
                  rot=45, title='Lowest 10 counties (cases/population)',
                  figsize=(10,6))
df_min10<jupyter_output><empty_output><jupyter_text>7. Compute the death rate (deaths/ known cases) in each county that have both deaths and known cases. Compute the mean and standard deviation of the results.* The death case in data some rows was decrease* I filter out invalid value e.g below 0* I filter out county that is unknown<jupyter_code># count deaths cases for each county  similar to previous question
all_deaths = df_deaths[[col for col in df_deaths.columns if '2020' in col]]
df_deaths['county_deaths'] = all_deaths.apply(lambda x: x.max(), axis =1)
df_total_deaths = df_deaths.groupby(['County Name','State'])[
                                'county_deaths'].sum().reset_index()

# merge deaths and cases dataframe and pick death cases greater than 0
df_case_death= pd.concat([df_counties_cases, df_total_deaths['county_deaths']], axis=1)
df_case_death = df_case_death[df_case_death['counties_cases'] > 0]


#take out unknown counties
df_case_death = df_case_death[df_case_death['County Name']
                              .str.contains('County')]

#calculate death rate
df_case_death['deaths_rate'] =  df_case_death['county_deaths']/ df_case_death['counties_cases']

df_case_death
#mean stdev of result
df_stdev = df_case_death.iloc[:,2:].std().to_frame()
df_mean = df_case_death.iloc[:,2:].mean().to_frame()
df_stdev.columns = ['standard deviation']
df_mean.columns = ['mean']
df_mean_stdev = df_stdev.join(df_mean)

# add min/max for each row
df_mean_stdev['max'] = [df_case_death['counties_cases'].max(),
                        df_case_death['county_deaths'].max(),
                        df_case_death['deaths_rate'].max()]
df_mean_stdev['min'] = [df_case_death['counties_cases'].min(),
                        df_case_death['county_deaths'].min(),
                        df_case_death['deaths_rate'].min()]
# add number of valid counties counted

print('After filtered Num of counties:',df_case_death['County Name'].count())
df_mean_stdev.iloc[:,::-1]<jupyter_output>After filtered Num of counties: 2353<jupyter_text>8. Produce a violin plot of the death rates found in 7* I filted death rate above 1 because deaths/known cases. All cases could be death cases, but cant go beyond* ppl dead more than ppl you know which not make sense<jupyter_code>#filter unreason data
filtered_deaths = df_case_death[df_case_death['deaths_rate'] < 1]

plt.subplots(figsize=(8,6))
plt.title('Violin plot of the death rates')
sns.violinplot(y = filtered_deaths['deaths_rate'])
plt.show()<jupyter_output><empty_output><jupyter_text>9. There is some hope that covid-19 may have peaked in NYC (counties Bronx, Kings, New York, Queens and Richmond). Is there any evidence of that in the data?<jupyter_code># count all known cases of all counties in NYC 
df_counties_cases = df_case.groupby(['County Name','State'])[
                                'counties_cases'].sum().reset_index()
df_top_NYC = df_counties_cases[df_counties_cases['State'].str.contains('NY') ]
df_top_NYC = df_top_NYC.nlargest(15,'counties_cases')

#highligh name we need
df_top_NYC.style.apply(lambda x: ['background: lightgreen' 
                                  if (x['County Name'] == 'Bronx County' 
                                      or x['County Name'] == 'Kings County' 
                                      or x['County Name'] == 'New York County'
                                      or x['County Name'] == 'Queens County'
                                      or x['County Name'] == 'Richmond County')
                                  else '' for i in x], axis=1)<jupyter_output><empty_output><jupyter_text>* Clearly, from highlight above Queens County is the peak. Then Kings CountyBut Bronx, New York,Richmond was not even top 3.  10.Seattle (King county in Washington) also hopes that they have peaked. Is there any evidence of that in the data?<jupyter_code># count all cases of all counties in Washington 
df_top_WA = df_counties_cases[df_counties_cases['State'].str.contains('WA') ]
df_top_WA = df_top_WA.nlargest(15,'counties_cases')

#highligh name we need
df_top_WA.style.apply(lambda x: ['background: lightgreen' 
                                  if (x['County Name'] == 'King County')
                                  else '' for i in x], axis=1)<jupyter_output><empty_output><jupyter_text>* Clearly, from highlight above King County is the peak (Seattle).  Names  1. Combine the files into one dataframe that contains year, name, sex and number of peopleborn that year. Create a training set and a test set from this dataframe.<jupyter_code># combine files slice the year add to columns
txt_list = []
files = glob.glob(name_path + "/*.txt")

for file in files:
    year = year = os.path.basename(file)[3:7]
    txt_list.append(pd.read_csv(file, header=None).assign(year=year))

df_comb = pd.concat(txt_list, ignore_index=True)
df_comb.to_csv("name.csv", header=False, index=False)<jupyter_output><empty_output><jupyter_text>Name file path.<jupyter_code>name_csv = 'name.csv'
df_name = pd.read_csv(name_csv)
df_name = pd.DataFrame(np.vstack([df_name.columns, df_name]))<jupyter_output><empty_output><jupyter_text>Data Checking<jupyter_code>df_name.columns=['name','sex','births','year']
df_name.head(10)
df_name.size
df_name.dtypes
df_name.isnull().isnull().sum()
#change types and convert sex to 0 or 1
df_name['year'] = pd.to_numeric(df_name['year'])
df_name['births'] = pd.to_numeric(df_name['births'])
df_name.sex.replace({'F':0,'M':1}, inplace=True)
#convert name object to numerical values
df_name['name'] = LabelEncoder().fit_transform(df_name['name'].values)
df_name
# group by name and sex and year count the raito of birth
df_name['name_ratio'] = df_name.births / df_name.births.sum()
df_name<jupyter_output><empty_output><jupyter_text>create traning and testing set<jupyter_code>#convert object to features
X = df_name[['name','births','year']] # feature 1,2,3
Y = df_name['sex']  # label

#Obersvation assignment 33% to testing set. 67% for tranning set 
X_train, X_test, y_train, y_test = train_test_split(X, Y, test_size=0.33, random_state = 0)<jupyter_output><empty_output><jupyter_text>2. For the first model determine for each name which sex used the name most in the training data. How good is the model on the test set? How to measure good?<jupyter_code>count = pd.DataFrame({'name': X_test['name'], 
                      'sex': y_test}).groupby(['name', 'sex'])['name']\
                                     .count()
#  count most sex determine name
def first_model(name, count):
    if (name,0) in count:
        female_count = count[(name, 0)]
    else:
        female_count = 0
    if (name,1) in count:
        male_count = count[(name, 1)]
    else:
        male_count = 0
    if female_count > male_count:
        return 0 
    else:
        return 1<jupyter_output><empty_output><jupyter_text>By mearse how good or the fitness. * use 33% test_set get response in this case is  660k columns out  of 2000k  * compare with actual value in y test get the accuracy* could try more different  test sizes  from 20 to 40<jupyter_code>x_test_result = pd.Series(X_test['name']).apply(lambda x: first_model(x, count))
model_test_score = accuracy_score(y_test, x_test_result)
print('accuracy of model1 test:',model_test_score*100,'%')<jupyter_output>accuracy of model1 test: 90.80634722045876 %<jupyter_text>3. For the second model create a decision tree using the training data. How good is the model on the test set?<jupyter_code># use sklearn descision tree train model
second_model = tree.DecisionTreeClassifier()
second_model.fit(X_train,y_train)
print('accuracy of model2 test:',second_model.score(X_test,y_test)*100,'%')
print('accuracy of model2 train:',second_model.score(X_train,y_train)*100,'%')<jupyter_output>accuracy of model2 test: 93.24431038700827 %
accuracy of model2 train: 99.88651789936091 %<jupyter_text>4. How do the two models compare? * from above question we can see second model has better accuracy or fitness to actual value in y_test* decision tree has considered year may affect people's choices, there are may be a trend along the year<jupyter_code># further compare
# take top 1000 data for comparsion
X = df_name[['name','births','year']].head(1000)
Y = df_name['sex'].head(1000)

#assign differ test size. try 38% test
X_train, X_test, y_train, y_test = train_test_split(X, Y, test_size=0.38, shuffle=True)
x_test_result = pd.Series(X_test['name']).apply(lambda x: first_model(x, count))
model_test_score = accuracy_score(y_test, x_test_result)
print('accuracy of model1 test:',model_test_score*100,'%')
second_model = tree.DecisionTreeClassifier()
second_model.fit(X_train,y_train)
print('accuracy of model2 test:',second_model.score(X_test,y_test)*100,'%')<jupyter_output>accuracy of model2 test: 94.47368421052632 %<jupyter_text>* reduce features take out year<jupyter_code>X = df_name[['name','births']].head(1000)
Y = df_name['sex'].head(1000)

#assign differ test size. try 38% test
X_train, X_test, y_train, y_test = train_test_split(X, Y, test_size=0.38, shuffle=True)
x_test_result = pd.Series(X_test['name']).apply(lambda x: first_model(x, count))
model_test_score = accuracy_score(y_test, x_test_result)
print('accuracy of model1:',model_test_score*100,'%')
second_model = tree.DecisionTreeClassifier()
second_model.fit(X_train,y_train)
print('accuracy of model2 without year:',second_model.score(X_test,y_test)*100,'%')<jupyter_output>accuracy of model2 without year: 93.42105263157895 %
{'fraction_non_alphanumeric': 0.11164947379389392, 'fraction_numerical': 0.023028029592581014, 'mean_word_length': 4.0420278434462835, 'pattern_counts': {'":': 0, '<': 85, '<?xml version=': 0, '>': 89, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 18, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '446352', 'license': 'MIT', 'n_tokens_mistral': 6734, 'n_tokens_neox': 6178, 'n_words': 1649, 'path': 'Midterm_leyu.ipynb', 'repo_name': 'leyulin/CS696', 'size': '19194'}