dipanb
commented
1 year ago !/usr/bin/env python
coding: utf-8
In[1]:
import pandas as pd import numpy as np import os os.chdir("Z:\Healthcare Research\Healthcare Spending\Research Papers\HRS\HRS_2018v2 - 2023\Dipan") df = pd.read_csv('HRS Columns Filtered.csv',index_col = [0]) df.reset_index(inplace=True,drop=True)
In[2]:
df['r1drinkn'] = df['r1drinkr'] df['r2drinkn'] = df['r2drinkr']
In[3]:
df['Is_Dead'] = [1 if df['radyear'].iloc[i].item()>0 else 0 for i in range(df.shape[0])]
In[45]:
df['Is_Dead'].sum()
In[4]:
df['Death_Wave'] = 100 for i in range(df.shape[0]): if df['Is_Dead'].iloc[i].item()==1: for j in range(1,15): if df[f'r{j}iwstat'].iloc[i]=='5.nr,died this wv': df['Death_Wave'].iloc[i] = j
In[47]:
df['Death_Wave'].value_counts()
In[60]:
Extended_Df = pd.DataFrame(columns = ['Household_Id','Is_Dead','Death_Wave','Death_Year','Death_Age', 'Gender','Race','Years_Education', 'Highest_Degree','Education_Category','Religion','Veteran_Status','Mother_Years_Of_Education', 'Father_Years_Of_Education','Division_Birth','Sample_Wt','Cohort_Of_Respondent', 'Division_At_Birth',
'Marital_Status','Partnership_Status',
'Wave_No',
'Times_Married','Length_Current_Marriage','Length_Longest_Marriage','Times_Divorced',
'Household_Size','Whether_Couple_HH','HH_Wt','Person_Level_Wt','Self_Report_Health',
'Self_Report_Health_Change_3_Category','Frequent_Vigorous_Physical_Activity','Frequent_Moderate_Physical_Activity','Frequent_Light_Physical_Activity',
'BMI','Height','Weight','Back_Problem','Smoke','Smoke_Now','Drink','Drink_Days_Per_Week',
'Drink_Per_Day','Blood_Pressure','Diabetes','Cancer','Lung_Disease','Heart_Problem','Stroke',
'Pyschological_Problem','Arthritis','Alzheimers','Dementia',
'Sum_Conditions','Systolic_BP','Diastolic_BP','Pulse',
'Non_Housing_Assets','Total_Assets','Income','Capital_Income_HH','Reports_Retired',
'Retirement_Year','Planned_Retirement_Year','Job_History','Job_Years','Household_Size',
'Number_Living_Children','Father_Alive','Mother_Alive','Father_Age','Mother_Age',
'Marital_Status','Partnership_Status','Current_Age'])count = 0 for i in range(df.shape[0]):
for i in range(10):
print(f'{i}/{df.shape[0]-1}')
if df['Death_Wave'].iloc[i]>2:
for wave in range(3,15):
if df['Death_Wave'].iloc[i]>wave:print(df['hhidpn'].iloc[i],df['radage_y'].iloc[i])
print(f'{i} {wave}')
c_list = []
c_list.extend(df[['hhidpn','Is_Dead','Death_Wave','radyear','radage_y','ragender','raracem','raedyrs',
'raedegrm','raeduc','rarelig','ravetrn','rameduc','rafeduc','rabplace','rawtsamp',
'racohbyr','rabplace']].iloc[i])
b_list = []
b_list = [wave,
df[f'r{wave}mrct'].iloc[i],df[f'r{wave}mcurln'].iloc[i],df[f'r{wave}mlenm'].iloc[i],
df[f'r{wave}mdiv'].iloc[i],df[f'h{wave}hhresp'].iloc[i],df[f'h{wave}cpl'].iloc[i],
df[f'r{wave}wthh'].iloc[i],df[f'r{wave}wtresp'].iloc[i],df[f'r{wave}shlt'].iloc[i],
df[f'r{wave}hltc3'].iloc[i],
# df[f'r{wave}vgactx'].iloc[i],df[f'r{wave}mdactx'].iloc[i],
# df[f'r{wave}ltactx'].iloc[i],
df[f'r{wave}bmi'].iloc[i],df[f'r{wave}height'].iloc[i],
df[f'r{wave}weight'].iloc[i],df[f'r{wave}back'].iloc[i],df[f'r{wave}smokev'].iloc[i],
df[f'r{wave}smoken'].iloc[i],df[f'r{wave}drink'].iloc[i],df[f'r{wave}drinkd'].iloc[i],
df[f'r{wave}drinkn'].iloc[i],df[f'r{wave}hibpe'].iloc[i],df[f'r{wave}diabe'].iloc[i],
df[f'r{wave}cancre'].iloc[i],df[f'r{wave}lunge'].iloc[i],df[f'r{wave}hearte'].iloc[i],
df[f'r{wave}stroke'].iloc[i],df[f'r{wave}psyche'].iloc[i],df[f'r{wave}arthre'].iloc[i],
# df[f'r{wave}alzhee'].iloc[i],df[f'r{wave}demene'].iloc[i],
df[f'r{wave}conde'].iloc[i],
# df[f'r{wave}bpsys'].iloc[i],df[f'r{wave}bpdia'].iloc[i],df[f'r{wave}bppuls'].iloc[i],
df[f'h{wave}atotn'].iloc[i],df[f'h{wave}atotb'].iloc[i],df[f'r{wave}iearn'].iloc[i],
df[f'h{wave}icap'].iloc[i],df[f'r{wave}sayret'].iloc[i],df[f'r{wave}retyr'].iloc[i],
df[f'r{wave}rplnyr'].iloc[i],df[f'r{wave}jjobs'].iloc[i],df[f'r{wave}jyears'].iloc[i],
df[f'h{wave}hhres'].iloc[i],df[f'h{wave}child'].iloc[i],df[f'r{wave}dadliv'].iloc[i],
df[f'r{wave}momliv'].iloc[i],df[f'r{wave}dadage'].iloc[i],df[f'r{wave}momage'].iloc[i],
df[f'r{wave}mstat'].iloc[i],df[f'r{wave}mpart'].iloc[i],df[f'r{wave}agey_e'].iloc[i]]
c_list.extend(b_list)print(f'{i} {wave} {len(c_list)}')
Extended_Df.loc[count] = c_list
count = count + 1In[61]:
Extended_Df['Death_Age'].head(25)
In[6]:
Extended_Df.to_csv('HRS Expanded v2.csv')
In[63]:
Extended_Df = pd.read_csv('HRS Expanded v2.csv', index_col = 0)
In[64]:
Extended_Df = Extended_Df.loc[:,~Extended_Df.columns.duplicated()].copy()
Extended_Df.drop('Household_Id', inplace=True, axis=1)
Extended_Df.drop('Sample_Wt', inplace=True, axis=1) Extended_Df.drop('HH_Wt', inplace=True, axis=1) Extended_Df.drop('Person_Level_Wt', inplace=True, axis=1)
Extended_Df.drop('Self_Report_Health_Change_3_Category', inplace=True, axis=1) Extended_Df.drop('Self_Report_Health', inplace=True, axis=1)
Extended_Df.drop('Systolic_BP', inplace=True, axis=1)
Extended_Df.drop('Pulse', inplace=True, axis=1)
Extended_Df.drop('Diastolic_BP', inplace=True, axis=1)
Extended_Df.drop('Dementia', inplace=True, axis=1)
Extended_Df.drop('Alzheimers', inplace=True, axis=1)
Extended_Df.drop('Back_Problem', inplace=True, axis=1)
Extended_Df.drop('Frequent_Vigorous_Physical_Activity', inplace=True, axis=1)
Extended_Df.drop('Frequent_Moderate_Physical_Activity', inplace=True, axis=1)
Extended_Df.drop('Frequent_Light_Physical_Activity', inplace=True, axis=1)
Extended_Df.drop('Planned_Retirement_Year', inplace=True, axis=1) Extended_Df.drop('Retirement_Year', inplace=True, axis=1) Extended_Df.drop('Household_Size.1', inplace=True, axis=1)
In[35]:
Extended_Df1 = Extended_Df.copy()
Extended_Df.iloc[:,:].isna().sum()
Extended_Df['Current_Age'].value_counts()
Extended_Df.shape
In[65]:
Extended_Df['Death_Age'].fillna(value = 200, inplace = True) Extended_Df['Race'].fillna(value = '1.white/caucasian', inplace = True)
Extended_Df.loc[Extended_Df['Highest_Degree']=='1.ged','Highest_Degree'] = '3.hs/ged' Extended_Df.loc[Extended_Df['Highest_Degree']=='2.hs','Highest_Degree'] = '3.hs/ged'
Extended_Df.loc[Extended_Df['Years_Education']=='17.17+ yrs','Years_Education'] = '17' Extended_Df.loc[Extended_Df['Years_Education']=='0.none','Years_Education'] = '0' Extended_Df['Years_Education'].fillna(value = '-1', inplace = True)
Extended_Df['Education_Category'].fillna(value = '1.lt high-school', inplace = True)
Extended_Df['Religion'].fillna(value = '5.other', inplace = True)
Extended_Df['Veteran_Status'].fillna(value = '0.no', inplace = True)
Extended_Df['Division_Birth'].fillna(value = '11.not us/inc us terr', inplace = True)
Extended_Df['Cohort_Of_Respondent'].fillna(value = '0.not in any cohort', inplace = True)
Extended_Df['BMI'].fillna(value = Extended_Df['BMI'].mean(), inplace = True) Extended_Df['Height'].fillna(value = Extended_Df['Height'].mean(), inplace = True) Extended_Df['Weight'].fillna(value = Extended_Df['Weight'].mean(), inplace = True) Extended_Df['Length_Current_Marriage'].fillna(value = Extended_Df['Length_Current_Marriage'].mean(), inplace = True) Extended_Df['Length_Longest_Marriage'].fillna(value = Extended_Df['Length_Longest_Marriage'].mean(), inplace = True) Extended_Df['Times_Married'].fillna(value = int(Extended_Df['Times_Married'].mean()), inplace = True) Extended_Df['Household_Size'].fillna(value = int(Extended_Df['Household_Size'].mean()), inplace = True)
Extended_Df['Whether_Couple_HH'].fillna(value = 'No value provided', inplace = True)
Extended_Df['Mother_Years_Of_Education'].fillna(value = '-1', inplace = True) Extended_Df['Father_Years_Of_Education'].fillna(value = '-1', inplace = True)
Extended_Df.loc[Extended_Df['Division_At_Birth']=='10.us/na division','Division_At_Birth'] = '11.not us/inc us terr' Extended_Df['Division_At_Birth'].fillna(value = '11.not us/inc us terr', inplace = True)
Extended_Df.loc[Extended_Df['Marital_Status']=='4.separated','Marital_Status'] = '6.separated/divorced' Extended_Df.loc[Extended_Df['Marital_Status']=='5.divorced','Marital_Status'] = '6.separated/divorced' Extended_Df['Marital_Status'].fillna(value = 'No value given', inplace = True)
Extended_Df['Partnership_Status'].fillna(value = '0. no', inplace = True)
Extended_Df['Times_Divorced'].fillna(value = 0.0, inplace = True)
Extended_Df['Smoke'].fillna(value = '1.yes', inplace = True) Extended_Df['Smoke_Now'].fillna(value = '0.no', inplace = True)
Extended_Df['Drink'].fillna(value = '0.no', inplace = True) Extended_Df['Drink_Per_Day'].fillna(value = '0.0 or doesnt drink', inplace = True) Extended_Df['Drink_Days_Per_Week'].fillna(value = '0.0 or doesnt drink', inplace = True)
Extended_Df['Reports_Retired'].fillna(value = 'No value given', inplace = True)
Extended_Df['Reports_Retired'].fillna(value = 'No value given', inplace = True)
Extended_Df['Father_Alive'].fillna(value = '0.no', inplace = True) Extended_Df['Mother_Alive'].fillna(value = '0.no', inplace = True)
Extended_Df['Father_Age'].fillna(value = int(Extended_Df['Father_Age'].mean()), inplace = True) Extended_Df['Mother_Age'].fillna(value = int(Extended_Df['Mother_Age'].mean()), inplace = True)
Extended_Df['Number_Living_Children'].fillna(value = int(Extended_Df['Number_Living_Children'].mean()), inplace = True)
Extended_Df.loc[Extended_Df['Drink_Per_Day']=='0.0 or doesnt drink','Drink_Per_Day'] = '0' Extended_Df.loc[Extended_Df['Drink_Per_Day']=="0.doesn't drink",'Drink_Per_Day'] = '0' Extended_Df.loc[Extended_Df['Drink_Per_Day']=='1.lt 1/day','Drink_Per_Day'] = '1' Extended_Df.loc[Extended_Df['Drink_Per_Day']=='2.1-2/day','Drink_Per_Day'] = '2' Extended_Df.loc[Extended_Df['Drink_Per_Day']=='3.3-4/day','Drink_Per_Day'] = '3' Extended_Df.loc[Extended_Df['Drink_Per_Day']=='4.5+/day','Drink_Per_Day'] = '4' Extended_Df.loc[Extended_Df['Drink_Per_Day']=='99.all day','Drink_Per_Day'] = '45' Extended_Df['Drink_Per_Day'] = pd.to_numeric(Extended_Df['Drink_Per_Day'])
Extended_Df.loc[Extended_Df['Drink_Days_Per_Week']=='0.0 or doesnt drink','Drink_Days_Per_Week'] = '0' Extended_Df['Drink_Days_Per_Week'] = pd.to_numeric(Extended_Df['Drink_Days_Per_Week'])
Extended_Df.loc[Extended_Df['Father_Years_Of_Education']=='8.5.8+ yrs','Father_Years_Of_Education'] = '8' Extended_Df.loc[Extended_Df['Father_Years_Of_Education']=='8.5','Father_Years_Of_Education'] = '8' Extended_Df.loc[Extended_Df['Father_Years_Of_Education']=='8.5.8+ yrs','Father_Years_Of_Education'] = '8' Extended_Df.loc[Extended_Df['Father_Years_Of_Education']=='17.17+ yrs','Father_Years_Of_Education'] = '17' Extended_Df.loc[Extended_Df['Father_Years_Of_Education']=='0.none','Father_Years_Of_Education'] = '0' Extended_Df['Father_Years_Of_Education'] = pd.to_numeric(Extended_Df['Father_Years_Of_Education'])
Extended_Df.loc[Extended_Df['Mother_Years_Of_Education']=='8.5.8+ yrs','Mother_Years_Of_Education'] = '8' Extended_Df.loc[Extended_Df['Mother_Years_Of_Education']=='8.5','Mother_Years_Of_Education'] = '8' Extended_Df.loc[Extended_Df['Mother_Years_Of_Education']=='8.5.8+ yrs','Mother_Years_Of_Education'] = '8' Extended_Df.loc[Extended_Df['Mother_Years_Of_Education']=='17.17+ yrs','Mother_Years_Of_Education'] = '17' Extended_Df.loc[Extended_Df['Mother_Years_Of_Education']=='0.none','Mother_Years_Of_Education'] = '0' Extended_Df['Mother_Years_Of_Education'] = pd.to_numeric(Extended_Df['Mother_Years_Of_Education'])
Extended_Df['Years_Education'] = pd.to_numeric(Extended_Df['Years_Education'])
In[84]:
Extended_Df = Extended_Df[Extended_Df['Blood_Pressure'].notna()]
Extended_Df.iloc[:,:].isna().sum()
Extended_Df['Death_Age'].value_counts()
Extended_Df.shape
In[ ]:
In[85]:
Death_Df = Extended_Df.loc[Extended_Df['Is_Dead']==1] Non_Death_Df = Extended_Df.loc[Extended_Df['Is_Dead']==0]
In[86]:
print(Extended_Df.shape) print(Death_Df.shape) print(Non_Death_Df.shape)
In[97]:
from sklearn.model_selection import train_test_split from sklearn.ensemble import RandomForestRegressor from sklearn.metrics import r2_score
In[105]:
labels = np.array(Death_Df['Death_Age']) model_df = Death_Df.drop('Death_Age', axis = 1) train, test, train_labels, test_labels = train_test_split(model_df,labels,test_size = 0.2)
In[106]:
train.iloc[:,:].isna().sum()
In[107]:
Other_Data_Train = train[['Cohort_Of_Respondent','Death_Year','Death_Wave', 'Length_Current_Marriage','Is_Dead', 'Father_Alive', 'Mother_Alive', 'Father_Age', 'Mother_Age','Wave_No','Household_Id','Current_Age']]
train = train.drop(['Cohort_Of_Respondent','Death_Year','Death_Wave', 'Length_Current_Marriage','Is_Dead', 'Father_Alive', 'Mother_Alive', 'Father_Age', 'Mother_Age','Wave_No','Household_Id','Current_Age'],axis=1)
train = pd.get_dummies(train) train = train.astype('float64') len(train.columns.to_list())
In[108]:
Other_Data_Test = test[['Cohort_Of_Respondent','Death_Year','Death_Wave', 'Length_Current_Marriage','Is_Dead', 'Father_Alive', 'Mother_Alive', 'Father_Age', 'Mother_Age','Wave_No','Household_Id','Current_Age']]
test = test.drop(['Cohort_Of_Respondent','Death_Year','Death_Wave', 'Length_Current_Marriage','Is_Dead', 'Father_Alive', 'Mother_Alive', 'Father_Age', 'Mother_Age','Wave_No','Household_Id','Current_Age'],axis=1)
test = pd.get_dummies(test) test = test.astype('float64') len(test.columns.to_list())
In[109]:
Other_Data_Non_Death_Df = Non_Death_Df[['Cohort_Of_Respondent','Death_Year','Death_Wave', 'Length_Current_Marriage','Is_Dead', 'Father_Alive', 'Mother_Alive', 'Father_Age', 'Mother_Age','Wave_No','Death_Age', 'Household_Id','Current_Age']]
Non_Death_Df = Non_Death_Df.drop(['Cohort_Of_Respondent','Death_Year','Death_Wave', 'Length_Current_Marriage','Is_Dead', 'Father_Alive', 'Mother_Alive', 'Father_Age', 'Mother_Age','Wave_No','Death_Age', 'Household_Id','Current_Age'],axis=1)
Non_Death_Df = pd.get_dummies(Non_Death_Df) Non_Death_Df = Non_Death_Df.astype('float64') print(len(Non_Death_Df.columns.to_list()))
In[ ]:
In[110]:
rf = RandomForestRegressor(n_estimators = 1000, random_state = 42,verbose=True) rf.fit(train, train_labels)
In[111]:
predictions = rf.predict(train) errors = abs(predictions - train_labels) mape = 100 * (errors / train_labels) accuracy = 100 - np.mean(mape) accuracy
In[112]:
R2 = r2_score(train_labels, predictions) Adj_R2 = 1-(1-R2)*(train.shape[0]-1)/(train.shape[0]-train.shape[1]-1) Adj_R2
In[113]:
predictions_test = rf.predict(test) test_errors = abs(predictions_test - test_labels) mape = 100 * (test_errors / test_labels) accuracy = 100 - np.mean(mape) accuracy
In[114]:
importances = list(rf.featureimportances) feature_importances = [(feature, round(importance, 4)) for feature, importance in zip(train.columns, importances)] feature_importances = sorted(feature_importances, key = lambda x: x[1], reverse = True) feature_importances
In[116]:
test = pd.concat([test, Other_Data_Test], axis=1) test['Actual_Death_Age'] = test_labels test['Predicted_Death_Age'] = predictions_test test.head() test.to_excel("Death Model Tests Predicted v2.xlsx")
In[118]:
predictions_non_death = rf.predict(Non_Death_Df) Non_Death_Df = pd.concat([Non_Death_Df, Other_Data_Non_Death_Df], axis=1) Non_Death_Df['Predicted_Death_Age'] = predictions_non_death Non_Death_Df.to_excel("Death Model Non Death Predicted v2.xlsx")
!/usr/bin/env python
coding: utf-8
In[3]:
import pandas as pd import numpy as np import os os.chdir("Z:\Healthcare Research\Healthcare Spending\Research Papers\HRS\HRS_2018v2 - 2023\Dipan")
In[4]:
HRS_data = pd.io.stata.read_stata('randhrs1992_2018v2.dta')
In[5]:
columns = pd.DataFrame(HRS_data.columns.to_list(),columns = ['Variables'])
columns.to_csv('HRS Variables.csv',index = False)
In[6]:
HRS_keep = HRS_data[['hhidpn','r1mstat','r1mpart','r2mstat','r2mpart','r3mstat','r3mpart','r4mstat','r4mpart', 'r5mstat','r5mpart','r6mstat','r6mpart','r7mstat','r7mpart','r8mstat','r8mpart','r9mstat', 'r9mpart','r10mstat','r10mpart','r11mstat','r11mpart','r12mstat','r12mpart','r13mstat', 'r13mpart','r14mstat','r14mpart','r1mrct','r2mrct','r3mrct','r4mrct','r5mrct','r6mrct', 'r7mrct','r8mrct','r9mrct','r10mrct','r11mrct','r12mrct','r13mrct','r14mrct','r1mcurln' ,'r2mcurln','r3mcurln','r4mcurln','r5mcurln','r6mcurln','r7mcurln','r8mcurln','r9mcurln', 'r10mcurln','r11mcurln','r12mcurln','r13mcurln','r14mcurln','r1mlenm','r2mlenm','r3mlenm', 'r4mlenm','r5mlenm','r6mlenm','r7mlenm','r8mlenm','r9mlenm','r10mlenm','r11mlenm','r12mlenm', 'r13mlenm','r14mlenm','r1mdiv','r2mdiv','r3mdiv','r4mdiv','r5mdiv','r6mdiv','r7mdiv','r8mdiv', 'r9mdiv','r10mdiv','r11mdiv','r12mdiv','r13mdiv','r14mdiv','r1cenreg','r2cenreg','r3cenreg', 'r4cenreg','r5cenreg','r6cenreg','r7cenreg','r8cenreg','r9cenreg','r10cenreg','r11cenreg', 'r12cenreg','r13cenreg','r14cenreg','r1cendiv','r2cendiv','r3cendiv','r4cendiv','r5cendiv', 'r6cendiv','r7cendiv','r8cendiv','r9cendiv','r10cendiv','r11cendiv','r12cendiv','r13cendiv', 'r14cendiv','h1hhresp','h4hhresp','h5hhresp','h6hhresp','h7hhresp','h8hhresp','h9hhresp', 'h10hhresp','h11hhresp','h12hhresp','h13hhresp','h14hhresp','h1cpl','h4cpl','h5cpl','h6cpl', 'h7cpl','h8cpl','h9cpl','h10cpl','h11cpl','h12cpl','h13cpl','h14cpl','ragender','raracem', 'rabmonth','rabyear','rabdate','r1iwbeg','r2iwbeg','r3iwbeg','r4iwbeg','r5iwbeg','r6iwbeg', 'r7iwbeg','r8iwbeg','r9iwbeg','r10iwbeg','r11iwbeg','r12iwbeg','r13iwbeg','r14iwbeg','r1iwend', 'r2iwend','r3iwend','r4iwend','r5iwend','r6iwend','r7iwend','r8iwend','r9iwend','r10iwend', 'r11iwend','r12iwend','r13iwend','r14iwend','r1iwbegf','r2iwbegf','r1iwendf','r2iwendf', 'r3iwendf','r1iwmid','r2iwmid','r3iwmid','r4iwmid','r5iwmid','r6iwmid','r7iwmid', 'r8iwmid','r9iwmid','r10iwmid','r11iwmid','r12iwmid','r13iwmid','r14iwmid','r1iwmidf', 'r2iwmidf','r3iwmidf','r4iwmidf','r5iwmidf','r6iwmidf','r7iwmidf','r8iwmidf','r9iwmidf', 'r10iwmidf','r11iwmidf','r12iwmidf','r13iwmidf','r14iwmidf','r1iwstat','r2iwstat','r3iwstat', 'r4iwstat','r5iwstat','r6iwstat','r7iwstat','r8iwstat','r9iwstat','r10iwstat','r11iwstat', 'r12iwstat','r13iwstat','r14iwstat','radmonth','radyear','raddate','radage_m','radage_y', 'raedyrs','raedegrm','raeduc','rarelig','ravetrn','rameduc','rafeduc','rabplace','r2pexit', 'r3pexit','r4pexit','r5pexit','r6pexit','r7pexit','r8pexit','r9pexit','r10pexit','r11pexit', 'r12pexit','r13pexit','r14pexit','r1agem_b','r2agem_b','r3agem_b','r4agem_b','r5agem_b', 'r6agem_b','r7agem_b','r8agem_b','r9agem_b','r10agem_b','r11agem_b','r12agem_b','r13agem_b', 'r14agem_b','r1agem_e','r2agem_e','r3agem_e','r4agem_e','r5agem_e','r6agem_e','r7agem_e', 'r8agem_e','r9agem_e','r10agem_e','r11agem_e','r12agem_e','r13agem_e','r14agem_e','r1agem_m', 'r2agem_m','r3agem_m','r4agem_m','r5agem_m','r6agem_m','r7agem_m','r8agem_m','r9agem_m', 'r10agem_m','r11agem_m','r12agem_m','r13agem_m','r14agem_m','r1agey_b','r2agey_b','r3agey_b', 'r4agey_b','r5agey_b','r6agey_b','r7agey_b','r8agey_b','r9agey_b','r10agey_b','r11agey_b', 'r12agey_b','r13agey_b','r14agey_b','r1agey_e','r2agey_e','r3agey_e','r4agey_e','r5agey_e', 'r6agey_e','r7agey_e','r8agey_e','r9agey_e','r10agey_e','r11agey_e','r12agey_e','r13agey_e', 'r14agey_e','r1agey_m','r2agey_m','r3agey_m','r4agey_m','r5agey_m','r6agey_m','r7agey_m', 'r8agey_m','r9agey_m','r10agey_m','r11agey_m','r12agey_m','r13agey_m','r14agey_m','rawtsamp', 'r1wthh','r2wthh','r3wthh','r4wthh','r5wthh','r6wthh','r7wthh','r8wthh','r9wthh','r10wthh', 'r11wthh','r12wthh','r13wthh','r14wthh','r1wtresp','r2wtresp','r3wtresp','r4wtresp', 'r5wtresp','r6wtresp','r7wtresp','r8wtresp','r9wtresp','r10wtresp','r11wtresp','r12wtresp', 'r13wtresp','r14wtresp','h2cpl','h2hhresp','h3cpl','h3hhresp','racohbyr','r1iwendm', 'r2iwendm','r3iwendm','r4iwendm','r5iwendm','r6iwendm','r7iwendm','r8iwendm','r9iwendm', 'r10iwendm','r11iwendm','r12iwendm','r13iwendm','r14iwendm','r1iwendy','r2iwendy','r3iwendy', 'r4iwendy','r5iwendy','r6iwendy','r7iwendy','r8iwendy','r9iwendy','r10iwendy','r11iwendy', 'r12iwendy','r13iwendy','r14iwendy','recendiv','recenreg','remstat','rempart','reiwbeg', 'reiwmid','reiwend','reiwmidf','respagem_b','respagem_m','respagem_e','respagey_b', 'respagey_m','respagey_e','r1shlt','r2shlt','r3shlt','r4shlt','r5shlt','r6shlt', 'r7shlt','r8shlt','r9shlt','r10shlt','r11shlt','r12shlt','r13shlt','r14shlt','r1hltc5', 'r2hltc5','r3hltc5','r4hltc5','r5hltc5','r6hltc5','r3hltc5f','r4hltc5f','r5hltc5f', 'r6hltc5f','r1hltc3','r2hltc3','r3hltc3','r4hltc3','r5hltc3','r6hltc3','r7hltc3', 'r8hltc3','r9hltc3','r10hltc3','r11hltc3','r12hltc3','r13hltc3','r14hltc3','r7vgactx', 'r8vgactx','r9vgactx','r10vgactx','r11vgactx','r12vgactx','r13vgactx','r14vgactx', 'r7mdactx','r8mdactx','r9mdactx','r10mdactx','r11mdactx','r12mdactx','r13mdactx', 'r14mdactx','r7ltactx','r8ltactx','r9ltactx','r10ltactx','r11ltactx','r12ltactx', 'r13ltactx','r14ltactx','r1bmi','r2bmi','r3bmi','r4bmi','r5bmi','r6bmi','r7bmi', 'r8bmi','r9bmi','r10bmi','r11bmi','r12bmi','r13bmi','r14bmi','r1height','r2height', 'r3height','r4height','r5height','r6height','r7height','r8height','r9height','r10height', 'r11height','r12height','r13height','r14height','r1weight','r2weight','r3weight', 'r4weight','r5weight','r6weight','r7weight','r8weight','r9weight','r10weight','r11weight', 'r12weight','r13weight','r14weight','r1back','r2back','r3back','r4back','r5back','r6back', 'r7back','r8back','r9back','r10back','r11back','r12back','r13back','r14back','r1smokev', 'r2smokev','r3smokev','r4smokev','r5smokev','r6smokev','r7smokev','r8smokev','r9smokev', 'r10smokev','r11smokev','r12smokev','r13smokev','r14smokev','r1smoken','r2smoken','r3smoken', 'r4smoken','r5smoken','r6smoken','r7smoken','r8smoken','r9smoken','r10smoken','r11smoken', 'r12smoken','r13smoken','r14smoken','r1drink','r2drink','r3drink','r4drink','r5drink', 'r6drink','r7drink','r8drink','r9drink','r10drink','r11drink','r12drink','r13drink', 'r14drink','r1drinkr','r2drinkr','r3drinkd','r4drinkd','r5drinkd','r6drinkd','r7drinkd', 'r8drinkd','r9drinkd','r10drinkd','r11drinkd','r12drinkd','r13drinkd','r14drinkd', 'r3drinkn','r4drinkn','r5drinkn','r6drinkn','r7drinkn','r8drinkn','r9drinkn','r10drinkn', 'r11drinkn','r12drinkn','r13drinkn','r14drinkn','r1hibp','r2hibp','r3hibp','r4hibp','r5hibp', 'r6hibp','r7hibp','r8hibp','r9hibp','r10hibp','r11hibp','r12hibp','r13hibp','r14hibp', 'r1hibpe','r2hibpe','r3hibpe','r4hibpe','r5hibpe','r6hibpe','r7hibpe','r8hibpe','r9hibpe', 'r10hibpe','r11hibpe','r12hibpe','r13hibpe','r14hibpe','r1diab','r2diab','r3diab','r4diab', 'r5diab','r6diab','r7diab','r8diab','r9diab','r10diab','r11diab','r12diab','r13diab', 'r14diab','r1diabe','r2diabe','r3diabe','r4diabe','r5diabe','r6diabe','r7diabe','r8diabe', 'r9diabe','r10diabe','r11diabe','r12diabe','r13diabe','r14diabe','r1cancr','r2cancr', 'r3cancr','r4cancr','r5cancr','r6cancr','r7cancr','r8cancr','r9cancr','r10cancr','r11cancr', 'r12cancr','r13cancr','r14cancr','r1cancre','r2cancre','r3cancre','r4cancre','r5cancre', 'r6cancre','r7cancre','r8cancre','r9cancre','r10cancre','r11cancre','r12cancre','r13cancre', 'r14cancre','r1lung','r2lung','r3lung','r4lung','r5lung','r6lung','r7lung','r8lung', 'r9lung','r10lung','r11lung','r12lung','r13lung','r14lung','r1lunge','r2lunge','r3lunge', 'r4lunge','r5lunge','r6lunge','r7lunge','r8lunge','r9lunge','r10lunge','r11lunge','r12lunge', 'r13lunge','r14lunge','r1heart','r2heart','r3heart','r4heart','r5heart','r6heart','r7heart', 'r8heart','r9heart','r10heart','r11heart','r12heart','r13heart','r14heart','r1hearte', 'r2hearte','r3hearte','r4hearte','r5hearte','r6hearte','r7hearte','r8hearte','r9hearte', 'r10hearte','r11hearte','r12hearte','r13hearte','r14hearte','r1strok','r2strok','r3strok', 'r4strok','r5strok','r6strok','r7strok','r8strok','r9strok','r10strok','r11strok','r12strok', 'r13strok','r14strok','r1stroke','r2stroke','r3stroke','r4stroke','r5stroke','r6stroke', 'r7stroke','r8stroke','r9stroke','r10stroke','r11stroke','r12stroke','r13stroke','r14stroke', 'r1psych','r2psych','r3psych','r4psych','r5psych','r6psych','r7psych','r8psych','r9psych', 'r10psych','r11psych','r12psych','r13psych','r14psych','r1psyche','r2psyche','r3psyche', 'r4psyche','r5psyche','r6psyche','r7psyche','r8psyche','r9psyche','r10psyche','r11psyche', 'r12psyche','r13psyche','r14psyche','r1arthr','r2arthr','r3arthr','r4arthr','r5arthr', 'r6arthr','r7arthr','r8arthr','r9arthr','r10arthr','r11arthr','r12arthr','r13arthr', 'r14arthr','r1arthre','r2arthre','r3arthre','r4arthre','r5arthre','r6arthre','r7arthre', 'r8arthre','r9arthre','r10arthre','r11arthre','r12arthre','r13arthre','r14arthre', 'r4memry','r5memry','r6memry','r7memry','r8memry','r9memry','r4memrye','r5memrye', 'r6memrye','r7memrye','r8memrye','r9memrye','r10alzhe','r11alzhe','r12alzhe','r13alzhe', 'r14alzhe','r10alzhee','r11alzhee','r12alzhee','r13alzhee','r14alzhee','r10demen','r11demen', 'r12demen','r13demen','r14demen','r10demene','r11demene','r12demene','r13demene','r14demene', 'r1conde','r2conde','r3conde','r4conde','r5conde','r6conde','r7conde','r8conde','r9conde', 'r10conde','r11conde','r12conde','r13conde','r14conde','r8bpsys','r9bpsys','r10bpsys', 'r11bpsys','r12bpsys','r13bpsys','r14bpsys','r8bpdia','r9bpdia','r10bpdia','r11bpdia', 'r12bpdia','r13bpdia','r14bpdia','r8bppuls','r9bppuls','r10bppuls','r11bppuls','r12bppuls', 'r13bppuls','r14bppuls','h1atotn','h1atotb','h2atotn','h2atotb','h3atotn','h3atotb', 'h4atotn','h4atotb','h5atotn','h5atotb','h6atotn','h6atotb','h7atotn','h7atotb','h8atotn', 'h8atotb','h9atotn','h9atotb','h10atotn','h10atotb','h11atotn','h11atotb','h12atotn', 'h12atotb','h13atotn','h13atotb','h14atotn','h14atotb','r1iearn','h1icap','r2iearn','h2icap', 'r3iearn','h3icap','r4iearn','h4icap','r5iearn','h5icap','r6iearn','h6icap','r7iearn', 'h7icap','r8iearn','h8icap','r9iearn','h9icap','r10iearn','h10icap','r11iearn','h11icap', 'r12iearn','h12icap','r13iearn','h13icap','r14iearn','h14icap','r1sayret','r2sayret', 'r3sayret','r4sayret','r5sayret','r6sayret','r7sayret','r8sayret','r9sayret','r10sayret', 'r11sayret','r12sayret','r13sayret','r14sayret','r1retmon','r2retmon','r3retmon','r4retmon', 'r5retmon','r6retmon','r7retmon','r8retmon','r9retmon','r10retmon','r11retmon','r12retmon', 'r13retmon','r14retmon','r1retyr','r2retyr','r3retyr','r4retyr','r5retyr','r6retyr', 'r7retyr','r8retyr','r9retyr','r10retyr','r11retyr','r12retyr','r13retyr','r14retyr', 'r1rplnyr','r2rplnyr','r3rplnyr','r4rplnyr','r5rplnyr','r6rplnyr','r7rplnyr','r8rplnyr', 'r9rplnyr','r10rplnyr','r11rplnyr','r12rplnyr','r13rplnyr','r14rplnyr','r1jjobs', 'r2jjobs','r3jjobs','r4jjobs','r5jjobs','r6jjobs','r7jjobs','r8jjobs','r9jjobs', 'r10jjobs','r11jjobs','r12jjobs','r13jjobs','r14jjobs','r1jyears','r2jyears', 'r3jyears','r4jyears','r5jyears','r6jyears','r7jyears','r8jyears','r9jyears', 'r10jyears','r11jyears','r12jyears','r13jyears','r14jyears','h1hhres','h1child', 'r1dadliv','r1momliv','r1dadage','r1momage','h2hhres','h2child','r2dadliv','r2momliv', 'r2dadage','r2momage','h3hhres','h3child','r3dadliv','r3momliv','r3dadage','r3momage', 'h4hhres','h4child','r4dadliv','r4momliv','r4dadage','r4momage','h5hhres','h5child', 'r5dadliv','r5momliv','r5dadage','r5momage','h6hhres','h6child','r6dadliv','r6momliv', 'r6dadage','r6momage','h7hhres','h7child','r7dadliv','r7momliv','r7dadage','r7momage', 'h8hhres','h8child','r8dadliv','r8momliv','r8dadage','r8momage','h9hhres','h9child', 'r9dadliv','r9momliv','r9dadage','r9momage','h10hhres','h10child','r10dadliv','r10momliv', 'r10dadage','r10momage','h11hhres','h11child','r11dadliv','r11momliv','r11dadage', 'r11momage','h12hhres','h12child','r12dadliv','r12momliv','r12dadage','r12momage', 'h13hhres','h13child','r13dadliv','r13momliv','r13dadage','r13momage','h14hhres', 'h14child','r14dadliv','r14momliv','r14dadage','r14momage']].copy()
HRS_keep.to_csv('HRS Columns Filtered.csv')
In[7]:
########Death dataframe###################### temp_df = HRS_keep.copy()
HRS_Death = pd.DataFrame()
HRS_Death['Death_Year'] = HRS_keep['radyear']
temp_df = temp_df.loc[temp_df['radyear']>0]
In[8]:
temp_df.to_csv('HRS Death.csv')
1.Death Age and Death year
3. Find wave number exit interview
4. All demographic variables for previous wave and ever haves - death wave values not reported - fill death wave values for ever haves by last wave data
5. Sample weights
- Run regression analysis
- Calculate sum(sample wts) and count - for each year, each condition
Death data
Death_df = pd.DataFrame() Death_df[['Household_Id','Death_Year','Death_Age','Gender','Race','Years_Education', 'Highest_Degree','Education_Category','Religion', 'Veteran_Status','Mother_Years_Of_Education', 'Father_Years_Of_Education','Division_Birth','Sample_Wt', 'Cohort_Of_Respondent','Division','Region', 'Marital_Status','Partnership_Status',]] = temp_df[['hhidpn','radyear','radage_y','ragender','raracem','raedyrs', 'raedegrm','raeduc','rarelig','ravetrn', 'rameduc','rafeduc','rabplace','rawtsamp', 'racohbyr','recendiv','recenreg','remstat','rempart',]]
In[9]:
def find_death_wave(df): temp = df.copy() temp['Death_Wave'] = 0 for i in range(df.shape[0]):
print(f'row:{i+1}')
print(f'wave:{j}')
Death_df['Death_Wave'] = find_death_wave(temp_df)
In[10]:
temp_df['r1drinkn'] = temp_df['r1drinkr'] temp_df['r2drinkn'] = temp_df['r2drinkr']
In[11]:
Death_df['Times_Married'] = None Death_df['Length_Current_Marriage'] = None Death_df['Length_Longest_Marriage'] = None Death_df['Times_Divorced'] = None Death_df['Household_Size'] = None Death_df['Whether_Couple_HH'] = None Death_df['HH_Wt'] = None Death_df['Person_Level_Wt'] = None Death_df['Self_Report_Health'] = None Death_df['Self_Report_Health_Change_3_Category'] = None Death_df['Frequent_Vigorous_Physical_Activity'] = None Death_df['Frequent_Moderate_Physical_Activity'] = None Death_df['Frequent_Light_Physical_Activity'] = None Death_df['BMI'] = None Death_df['Height'] = None Death_df['Weight'] = None Death_df['Back_Problem'] = None Death_df['Smoke'] = None Death_df['Smoke_Now'] = None Death_df['Drink'] = None Death_df['Drink_Days_Per_Week'] = None Death_df['Drink_Per_Day'] = None Death_df['Blood_Pressure'] = None Death_df['Diabetes'] = None Death_df['Cancer'] = None Death_df['Lung_Disease'] = None Death_df['Heart_Problem'] = None Death_df['Stroke'] = None Death_df['Pyschological_Problem'] = None Death_df['Arthritis'] = None Death_df['Alzheimers'] = None Death_df['Dementia'] = None Death_df['Sum_Conditions'] = None Death_df['Systolic_BP'] = None Death_df['Diastolic_BP'] = None Death_df['Pulse'] = None Death_df['Non_Housing_Assets'] = None Death_df['Total_Assets'] = None Death_df['Income'] = None Death_df['Capital_Income_HH'] = None Death_df['Reports_Retired'] = None Death_df['Retirement_Year'] = None Death_df['Planned_Retirement_Year'] = None Death_df['Job_History'] = None Death_df['Job_Years'] = None Death_df['Household_Size'] = None Death_df['Number_Living_Children'] = None Death_df['Father_Alive'] = None Death_df['Mother_Alive'] = None Death_df['Father_Age'] = None Death_df['Mother_Age'] = None
for i in range(Death_df.shape[0]): d_wave = Death_df['Death_Wave'].iloc[i] if d_wave==1: continue d_wave = Death_df['Death_Wave'].iloc[i] Death_df['Times_Married'].iloc[i] = temp_df[f'r{d_wave-1}mrct'].iloc[i] Death_df['Length_Current_Marriage'].iloc[i] = temp_df[f'r{d_wave-1}mcurln'].iloc[i] Death_df['Length_Longest_Marriage'].iloc[i] = temp_df[f'r{d_wave-1}mlenm'].iloc[i] Death_df['Times_Divorced'].iloc[i] = temp_df[f'r{d_wave-1}mdiv'].iloc[i] Death_df['Household_Size'].iloc[i] = temp_df[f'h{d_wave-1}hhresp'].iloc[i] Death_df['Whether_Couple_HH'].iloc[i] = temp_df[f'h{d_wave-1}cpl'].iloc[i] Death_df['HH_Wt'].iloc[i] = temp_df[f'r{d_wave-1}wthh'].iloc[i] Death_df['Person_Level_Wt'].iloc[i] = temp_df[f'r{d_wave-1}wtresp'].iloc[i] Death_df['Self_Report_Health'].iloc[i] = temp_df[f'r{d_wave-1}shlt'].iloc[i] Death_df['Self_Report_Health_Change_3_Category'].iloc[i] = temp_df[f'r{d_wave-1}hltc3'].iloc[i] Death_df['BMI'].iloc[i] = temp_df[f'r{d_wave-1}bmi'].iloc[i] Death_df['Height'].iloc[i] = temp_df[f'r{d_wave-1}height'].iloc[i] Death_df['Weight'].iloc[i] = temp_df[f'r{d_wave-1}weight'].iloc[i] Death_df['Back_Problem'].iloc[i] = temp_df[f'r{d_wave-1}back'].iloc[i] Death_df['Smoke'].iloc[i] = temp_df[f'r{d_wave-1}smokev'].iloc[i] Death_df['Smoke_Now'].iloc[i] = temp_df[f'r{d_wave-1}smoken'].iloc[i] Death_df['Drink'].iloc[i] = temp_df[f'r{d_wave-1}drink'].iloc[i] Death_df['Drink_Per_Day'].iloc[i] = temp_df[f'r{d_wave-1}drinkn'].iloc[i] Death_df['Blood_Pressure'].iloc[i] = temp_df[f'r{d_wave-1}hibpe'].iloc[i] Death_df['Diabetes'].iloc[i] = temp_df[f'r{d_wave-1}diabe'].iloc[i] Death_df['Cancer'].iloc[i] = temp_df[f'r{d_wave-1}cancre'].iloc[i] Death_df['Lung_Disease'].iloc[i] = temp_df[f'r{d_wave-1}lunge'].iloc[i] Death_df['Heart_Problem'].iloc[i] = temp_df[f'r{d_wave-1}hearte'].iloc[i] Death_df['Stroke'].iloc[i] = temp_df[f'r{d_wave-1}stroke'].iloc[i] Death_df['Pyschological_Problem'].iloc[i] = temp_df[f'r{d_wave-1}psyche'].iloc[i] Death_df['Arthritis'].iloc[i] = temp_df[f'r{d_wave-1}arthre'].iloc[i] Death_df['Sum_Conditions'].iloc[i] = temp_df[f'r{d_wave-1}conde'].iloc[i] Death_df['Non_Housing_Assets'].iloc[i] = temp_df[f'h{d_wave-1}atotn'].iloc[i] Death_df['Total_Assets'].iloc[i] = temp_df[f'h{d_wave-1}atotb'].iloc[i] Death_df['Income'].iloc[i] = temp_df[f'r{d_wave-1}iearn'].iloc[i] Death_df['Capital_Income_HH'].iloc[i] = temp_df[f'h{d_wave-1}icap'].iloc[i] Death_df['Reports_Retired'].iloc[i] = temp_df[f'r{d_wave-1}sayret'].iloc[i] Death_df['Retirement_Year'].iloc[i] = temp_df[f'r{d_wave-1}retyr'].iloc[i] Death_df['Planned_Retirement_Year'].iloc[i] = temp_df[f'r{d_wave-1}rplnyr'].iloc[i] Death_df['Job_History'].iloc[i] = temp_df[f'r{d_wave-1}jjobs'].iloc[i] Death_df['Job_Years'].iloc[i] = temp_df[f'r{d_wave-1}jyears'].iloc[i] Death_df['Household_Size'].iloc[i] = temp_df[f'h{d_wave-1}hhres'].iloc[i] Death_df['Number_Living_Children'].iloc[i] = temp_df[f'h{d_wave-1}child'].iloc[i] Death_df['Father_Alive'].iloc[i] = temp_df[f'r{d_wave-1}dadliv'].iloc[i] Death_df['Mother_Alive'].iloc[i] = temp_df[f'r{d_wave-1}momliv'].iloc[i] Death_df['Father_Age'].iloc[i] = temp_df[f'r{d_wave-1}dadage'].iloc[i] Death_df['Mother_Age'].iloc[i] = temp_df[f'r{d_wave-1}momage'].iloc[i]
In[ ]:
Death_df.to_csv('HRS Death Cleaned.csv',index = False)
In[10]:
temp_df = HRS_keep.copy()
HRS_keep['radyear'].unique()
In[11]:
Incidence Data
0. For each wave - remove people who died before this wave - exit interview before this wave
1.Interview end Year
2.Ever haves - Each condition
- calculate sum(sample wts) and cout - for each year and each condtions
def find_if_death_wave(df):
temp = df.copy()
temp['Death_Wave'] = None
for i in range(df.shape[0]):
if not temp['radyear'].iloc[i]>0:
continue
for j in range(1,15):
if df[f'r{j}iwstat'].loc[i]=='5.nr,died this wv':
temp['Death_Wave'].iloc[i] = j
return temp['Death_Wave']
temp_df['Death_Wave'] = find_if_death_wave(temp_df)
In[12]:
def count_chronic(name_c,df):
count_c = []
for i in range(1,15):
if f'r{i}{name_c}' not in temp_df.columns:
count_c.append(0)
else:
count_c.append(temp_df[f'r{i}{name_c}'].value_counts()['1.yes'])
return count_c
In[13]:
Chronic_Incidence = pd.DataFrame(list(range(1,15)),columns=[''])
In[14]:
name_c = 'hibpe'
count_c = {}
sumwt_c = {}
Also include Age in this loop
for i in range(1,15):
years_list = temp_df.loc[temp_df[f'r{i}iwendy']>0][f'r{i}iwendy'].unique()
for year in years_list:
temp = temp_df.loc[temp_df[f'r{i}iwendy']==year]
for age in range(55,101):
temp1 = temp.loc[temp_df[f'r{i}agey_e']==age]
print(f'{i} {int(year)} {age} {temp1.shape[0]}')
Count
if f'{int(year)}_{age}' not in count_c.keys():
if f'r{i}{name_c}' in temp1.columns:
countc[f'{int(year)}{age}'] = temp1[f'r{i}{name_c}'].value_counts()['1.yes']
else:
countc[f'{int(year)}{age}'] = 0
else:
if f'r{i}{name_c}' in temp1.columns:
countc[f'{int(year)}{age}'] += temp1[f'r{i}{name_c}'].value_counts()['1.yes']
else:
countc[f'{int(year)}{age}'] += 0
Weight Sum
if f'{int(year)}_{age}' not in sumwt_c.keys():
if f'r{i}{name_c}' in temp1.columns:
sumwtc[f'{int(year)}{age}'] = temp1[f'rawtsamp'].loc[temp1[f'r{i}{name_c}']=='1.yes'].sum()
else:
sumwtc[f'{int(year)}{age}'] = 0
else:
if f'r{i}{name_c}' in temp1.columns:
sumwtc[f'{int(year)}{age}'] += temp1[f'rawtsamp'].loc[temp1[f'r{i}{name_c}']=='1.yes'].sum()
else:
sumwtc[f'{int(year)}{age}'] += 0
count_c_list = []
sumwt_c_list = []
for i in range(1992,2020):
for j in range(55,101):
if f'{i}_{j}' in count_c.keys():
count_c_list.append([i,j,countc[f'{i}{j}']])
if f'{i}_{j}' in sumwt_c.keys():
sumwt_c_list.append([i,j,sumwtc[f'{i}{j}']])
count_df = pd.DataFrame(count_c_list,columns = ['Year','Age','Count'])
sumwt_df = pd.DataFrame(sumwt_c_list,columns = ['Year','Age','Sum_Wt'])
sumwt_df = pd.merge(count_df, sumwt_df, how='left', left_on=['Year','Age'], right_on = ['Year','Age'])
In[22]:
Incidence Data
def incidence_chronic(name_c,df): temp_df = df.copy() count_c = {} sumwt_c = {}
print(f'{i} {int(year)} {age} {temp1.shape[0]}')
In[23]:
Male
temp_df = HRS_keep.loc[HRS_keep['ragender']=='1.male'].copy() chronic_list = ['hibpe','diabe','cancre','lunge','hearte','stroke','psyche','arthre','alzhee','demene'] for i in chronic_list: a = incidence_chronic(i,temp_df) if i == chronic_list[0]: Incidence = a.copy() continue Incidence = pd.merge(Incidence, a, how='left', left_on=['Year','Age'], right_on = ['Year','Age']) Incidence_Male = Incidence.copy()
Female
temp_df = HRS_keep.loc[HRS_keep['ragender']=='2.female'].copy() chronic_list = ['hibpe','diabe','cancre','lunge','hearte','stroke','psyche','arthre','alzhee','demene'] for i in chronic_list: a = incidence_chronic(i,temp_df) if i == chronic_list[0]: Incidence = a.copy() continue Incidence = pd.merge(Incidence, a, how='left', left_on=['Year','Age'], right_on = ['Year','Age']) Incidence_Female = Incidence.copy()
In[25]:
Incidence.to_csv("Incidence Counts and Sum of Weights.csv",index = False)
In[26]:
Death Counts and Sum Weigts
Male
temp_df = HRS_keep.loc[HRS_keep['radyear']>0] temp_df = temp_df.loc[temp_df['ragender']=='1.male'] chronic_list = ['hibpe','diabe','cancre','lunge','hearte','stroke','psyche','arthre','alzhee','demene'] for i in chronic_list: a = incidence_chronic(i,temp_df) if i == chronic_list[0]: Death_count_df = a.copy() continue Death_count_df = pd.merge(Death_count_df, a, how='left', left_on=['Year','Age'], right_on = ['Year','Age']) Death_count_Male = Death_count_df.copy()
Male
temp_df = HRS_keep.loc[HRS_keep['radyear']>0] temp_df = temp_df.loc[temp_df['ragender']=='2.female'] chronic_list = ['hibpe','diabe','cancre','lunge','hearte','stroke','psyche','arthre','alzhee','demene'] for i in chronic_list: a = incidence_chronic(i,temp_df) if i == chronic_list[0]: Death_count_df = a.copy() continue Death_count_df = pd.merge(Death_count_df, a, how='left', left_on=['Year','Age'], right_on = ['Year','Age']) Death_count_Female = Death_count_df.copy()
Death_count_df.to_csv("Death Counts and Sum of Weights.csv",index = False)
In[28]:
with pd.ExcelWriter('Counts and Sum Weights Chronic - 8 May 2023.xlsx') as writer: Incidence_Male.to_excel(writer, sheet_name='Incidence_Male') Death_count_Male.to_excel(writer, sheet_name='Death_count_Male') Incidence_Female.to_excel(writer, sheet_name='Incidence_Female') Death_count_Female.to_excel(writer, sheet_name='Death_count_Female')
In[16]:
Total Count for Persons
Incidence Data
def total_person_count(df): temp_df = df.copy() count_c = {} sumwt_c = {}
count_c_death = {}
sumwt_c_death = {}
print(f'{i} {int(year)} {age} {temp1.shape[0]}')
count_cdeath[f'{int(year)}{age}'] = temp1[f'r{i}pexit'].value_counts()['1.exit interview this wave']
count_cdeath[f'{int(year)}{age}'] += temp1[f'r{i}pexit'].value_counts()['1.exit interview this wave']
sumwt_cdeath[f'{int(year)}{age}'] = temp1[f'rawtsamp'].loc[temp1[f'r{i}pexit']=='1.exit interview this wave'].sum()
sumwt_cdeath[f'{int(year)}{age}'] = temp1[f'rawtsamp'].loc[temp1[f'r{i}pexit']=='1.exit interview this wave'].sum()
count_c_death_list = []
sumwt_c_death_list = []
count_c_death_list.append([i,j,count_cdeath[f'{i}{j}']])
sumwt_c_death_list.append([i,j,sumwt_cdeath[f'{i}{j}']])
count_death_df = pd.DataFrame(count_c_death_list,columns = ['Year','Age',f'Death_Count'])
sumwt_death_df = pd.DataFrame(sumwt_c_death_list,columns = ['Year','Age',f'Death_Sum_Wt'])
final_df = pd.merge(final_df, count_death_df, how='left', left_on=['Year','Age'], right_on = ['Year','Age'])
final_df = pd.merge(final_df, sumwt_death_df, how='left', left_on=['Year','Age'], right_on = ['Year','Age'])
person_counts_male = total_person_count(HRS_keep.loc[HRS_keep['ragender']=='1.male']) person_counts_female = total_person_count(HRS_keep.loc[HRS_keep['ragender']=='2.female'])
In[17]:
Total Counts of Death
Male
temp_df = HRS_keep.loc[HRS_keep[f'radyear']>0] temp_df = temp_df.loc[temp_df['ragender']=='1.male'] a = pd.pivot_table(temp_df, index=['radyear','radage_y'],values=['rawtsamp'],aggfunc=len) a.columns = ['Death_Count'] b = pd.pivot_table(temp_df, index=['radyear','radage_y'],values=['rawtsamp'],aggfunc=np.sum) b.columns = ['Death_Sum'] temp_df = pd.concat([a,b]) temp_df = temp_df.reset_index() temp_df.columns = ['Year',"Age",'Death_Count','Death_Sum'] death_count_male = temp_df.copy()
Female
temp_df = HRS_keep.loc[HRS_keep[f'radyear']>0] temp_df = temp_df.loc[temp_df['ragender']=='2.female'] a = pd.pivot_table(temp_df, index=['radyear','radage_y'],values=['rawtsamp'],aggfunc=len) a.columns = ['Death_Count'] b = pd.pivot_table(temp_df, index=['radyear','radage_y'],values=['rawtsamp'],aggfunc=np.sum) b.columns = ['Death_Sum'] temp_df = pd.concat([a,b]) temp_df = temp_df.reset_index() temp_df.columns = ['Year',"Age",'Death_Count','Death_Sum'] death_count_female = temp_df.copy()
In[20]:
mortality_df = pd.merge(person_counts, death_counts, how='left', left_on=['Year','Age'], right_on = ['Year','Age'])
mortality_df.columns = ['Year','Age','Total_Count','Total_Sum','Death_Count','Death_Sum']
person_counts.to_csv('Total and Death Person Counts and Sum Wts.csv')
In[21]:
temp_df.to_csv('Total Death Counts.csv')
In[19]:
with pd.ExcelWriter('Total Person and Total Death Counts and Sum 8 may 2023.xlsx') as writer: person_counts_male.to_excel(writer, sheet_name='person_counts_male') death_count_male.to_excel(writer, sheet_name='death_count_male') person_counts_female.to_excel(writer, sheet_name='person_counts_female') death_count_female.to_excel(writer, sheet_name='death_count_female')
In[ ]:
In[ ]:
In[ ]:
In[ ]: