<jupyter_start><jupyter_text>Importing modules<jupyter_code>import json 
import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Read cowin csv and census xlsx file<jupyter_code>data=pd.read_csv('cowin_vaccine_data_districtwise.csv')
cols=['State','Level',
       'Name', 'TRU','TOT_P', 'TOT_M', 'TOT_F']
census_data=pd.read_excel('DDW_PCA0000_2011_Indiastatedist.xlsx',usecols=cols)
#only considering total rather than rural and urban seperately for this question
census_data=census_data[(census_data['Level']=='STATE') & (census_data['TRU']=='Total')]
census_data.reset_index(inplace=True,drop=True)<jupyter_output>/home/sunild/anaconda3/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3169: DtypeWarning: Columns (6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,2[...]<jupyter_text>Time ids for dates<jupyter_code># dictionaries for mapping dates to time ids
# eg. for 2020-3-15, time_id_week is 1, time_id_month is 1, time_id_ is 1
import datetime
time_id_week = {}
time_id_month = {}
time_id_overall = {}

date=datetime.date(2021,1,16)
day=0

while True:
    # basically to cover overlapping weeks this part needs to be changed.
    # for now we are proceeding. but change week ids according above definition of 7-DMA
    
    #### specific to this situation only
    k=(date.weekday()+1)%7+1
    if k%7==0:
        time_id_week[str(date)]=int(day/7)+1
    else:
        time_id_week[str(date)]=int(day/7)+2
    # there is some problem in week number please fix it
    if int(str(date)[8:10]) <15:
        time_id_month[str(date)]=int(str(date)[5:7])-1
    else:
        time_id_month[str(date)]=int(str(date)[5:7]) # only because it is 2021 # if you consider 2020 then a bit manipulation
    time_id_overall[str(date)]=1
    if date==datetime.date(2021,8,14):
        break
    day=day+1
    date=date+datetime.timedelta(days=1)<jupyter_output><empty_output><jupyter_text>Prepare data frames for first dose<jupyter_code># let's change those column names
#date==datetime.date(2021,8,14)
from datetime import datetime
data_dose1=data.loc[:,(data.loc[0,]=='First Dose Administered')].iloc[1:,:].fillna(0)
first_date_dose1=data_dose1.iloc[:,0]
data_dose1=data_dose1.astype(int).diff(axis=1)
data_dose1.iloc[:,0]=first_date_dose1
col_names_dose1=data_dose1.columns
new_col_names_weeks_dose1=[]

for i in range(len(col_names_dose1)):
    if (datetime.strptime(col_names_dose1[i].split('.')[0],'%d/%m/%Y')).strftime('%Y-%m-%d') == '2021-08-15':
        break
    new_col_names_weeks_dose1.append(time_id_week[(datetime.strptime(col_names_dose1[i].split('.')[0],'%d/%m/%Y')).strftime('%Y-%m-%d')])

no_weeks_dose1=len(np.unique(new_col_names_weeks_dose1))
new_col_names_weeks_dose1.extend(['No']*(len(col_names_dose1)-len(new_col_names_weeks_dose1)))
data_dose1.columns=new_col_names_weeks_dose1
data_dose1['District']=data['District'].str.lower()
data_dose1=data_dose1.loc[:,data_dose1.columns!='No']
state_names=np.unique(data['State'].dropna())
census_states = np.array(census_data[census_data['Level']=='STATE']['Name'])<jupyter_output><empty_output><jupyter_text>Editdist function for strings<jupyter_code>def editdist(str1, str2): 
    m = len(str1)
    n = len(str2)
    # Create a table to store results of subproblems
    dp = [[0 for x in range(n + 1)] for x in range(m + 1)] 
  
    # Fill d[][] in bottom up manner 
    for i in range(m + 1): 
        for j in range(n + 1): 
  
            # If first string is empty, only option is to 
            # insert all characters of second string 
            if i == 0: 
                dp[i][j] = j    # Min. operations = j 
  
            # If second string is empty, only option is to 
            # remove all characters of second string 
            elif j == 0: 
                dp[i][j] = i    # Min. operations = i 
  
            # If last characters are same, ignore last char 
            # and recur for remaining string 
            elif str1[i-1] == str2[j-1]: 
                dp[i][j] = dp[i-1][j-1] 
  
            # If last character are different, consider all 
            # possibilities and find minimum 
            else: 
                dp[i][j] = 1 + min(dp[i][j-1],        # Insert 
                                   dp[i-1][j],        # Remove 
                                   dp[i-1][j-1])    # Replace 
    #print(dp)
    return dp[m][n]<jupyter_output><empty_output><jupyter_text>finding common states<jupyter_code>common_cowin_indexes = []
common_census_indexes = []
remain_states_cowin=[]
for i in range(len(state_names)):
    k=0
    for j in range(len(census_states)):
        if editdist(state_names[i].lower(),census_states[j].lower())<4 and state_names[i][0]==census_states[j][0]:
            common_cowin_indexes.append(state_names[i])
            common_census_indexes.append(census_states[j])
            k=1
            break
    if k==0:
        remain_states_cowin.append(state_names[i])
print(remain_states_cowin)
common_census_indexes.extend(['NCT OF DELHI','DAMAN & DIU', 'DADRA & NAGAR HAVELI'])
common_cowin_indexes.extend(['Delhi','Dadra and Nagar Haveli and Daman and Diu'])<jupyter_output>['Dadra and Nagar Haveli and Daman and Diu', 'Delhi', 'Ladakh', 'Telangana']<jupyter_text>- [x] Updating state list with new states formed after 2011 census and other things.- [x] DAMAN & DIU + DADRA & NAGAR HAVELI = Dadra and Nagar Haveli and Daman and Diu- [x] Ladakh + Jammu and Kashmir = Jammu and Kashmir- [x] Telangana + Andhra Pradesh = Andhra Pradesh- [x] NCT OF DELHI = Delhi<jupyter_code>data_state_female=data_dose1.iloc[:,:-1]
data_state_female['State']=data['State']<jupyter_output><empty_output><jupyter_text>Counts first dose counts for states- [x] Total counts- [x] Last week(weekid=31) counts to compute vaccination rate<jupyter_code>state_cowin_female_male=[]
state_male_count=[]#this is for last week
state_female_count=[]
for i in range(len(common_cowin_indexes)):
    for j in range(data_state_female.shape[0]):
        if common_cowin_indexes[i]=='Andhra Pradesh':
            state_cowin_female_male.append(common_cowin_indexes[i])
            state_female_count.append((data_state_female.loc[(data_state_female['State']=='Andhra Pradesh')].iloc[:,:-1]).astype(int).sum().sum()+(data_state_female.loc[(data_state_female['State']=='Telangana')].iloc[:,:-1]).astype(int).sum().sum())
            state_male_count.append((data_state_female.loc[(data_state_female['State']=='Andhra Pradesh'), data_state_female.columns==31]).astype(int).sum().sum()+(data_state_female.loc[(data_state_female['State']=='Telangana'),  data_state_female.columns==31]).astype(int).sum().sum())
            break
        elif common_cowin_indexes[i]=='Jammu and Kashmir':
            state_cowin_female_male.append(common_cowin_indexes[i])
            state_female_count.append((data_state_female.loc[(data_state_female['State']=='Jammu and Kashmir')].iloc[:,:-1]).astype(int).sum().sum()+(data_state_female.loc[(data_state_female['State']=='Ladakh')].iloc[:,:-1]).astype(int).sum().sum())
            state_male_count.append((data_state_female.loc[(data_state_female['State']=='Jammu and Kashmir'),  data_state_female.columns==31]).astype(int).sum().sum()+(data_state_female.loc[(data_state_female['State']=='Ladakh'),  data_state_female.columns==31]).astype(int).sum().sum())
            break   
        else:
            state_cowin_female_male.append(common_cowin_indexes[i])
            state_female_count.append(data_state_female.loc[data_state_female['State']==common_cowin_indexes[i]].iloc[:,:-1].astype(int).sum().sum())
            state_male_count.append(data_state_female.loc[data_state_female['State']==common_cowin_indexes[i],  data_state_female.columns==31].astype(int).sum().sum())
            break<jupyter_output><empty_output><jupyter_text>population count for states with modification<jupyter_code>total_state_pop=[]
state_male_female_pop=[]
for i in range(len(common_census_indexes)):
    for j in range(census_data.shape[0]):
        if common_census_indexes[i]==census_data['Name'][j]:
            state_male_female_pop.append(common_census_indexes[i])
            total_state_pop.append(census_data.iloc[j,4].astype(int))
            break<jupyter_output><empty_output><jupyter_text>Rate of vaccination = 100*last-week-count/total-state-population<jupyter_code>import datetime
dadra_daman_total_pop=total_state_pop[-1].astype(int)+total_state_pop[-2].astype(int)
total_state_pop=total_state_pop[:-2]
total_state_pop.append(dadra_daman_total_pop)
pop_left=[i-j for i,j in zip(total_state_pop,state_female_count)]
rateofvaccination=[np.round(i*100/j,2) for i,j in zip(state_male_count,total_state_pop)] # here state_male_count is total count in last week
days_to_complete=[np.ceil(7*j/i) for i,j in zip(state_male_count,pop_left)]
completetion_date=[]
date=datetime.date(2021,8,14)
for i in days_to_complete:
    if i < 0: ## where days are negative reassign it to zero days, it is the case with Dadar and Daman UT
        completetion_date.append(str(date+datetime.timedelta(days=0)))
    else:
        completetion_date.append(str(date+datetime.timedelta(days=i)))<jupyter_output><empty_output><jupyter_text>Write in csv file<jupyter_code>complete_df=pd.DataFrame({'stateid':state_cowin_female_male,'populationleft':pop_left,'rateofvaccination':rateofvaccination,'date':completetion_date})
complete_df.sort_values('stateid', axis = 0, ascending = True, kind='mergesort',inplace=True)
complete_df.reset_index(inplace=True,drop=True)
complete_df.to_csv('complete-vaccination.csv',index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15366157980612344, 'fraction_numerical': 0.0801420481812074, 'mean_word_length': 5.273329319686936, 'pattern_counts': {'":': 0, '<': 46, '<?xml version=': 0, '>': 43, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '186528', 'license': 'MIT', 'n_tokens_mistral': 4515, 'n_tokens_neox': 3889, 'n_words': 564, 'path': 'Q9_Asgn1.ipynb', 'repo_name': 'sunil-dhaka/india-covid19-cases-and-vaccination-analysis', 'size': '10419'}