<jupyter_start><jupyter_text>import modules<jupyter_code>import pandas as pd
import numpy as np
c19=pd.read_excel('datasets/C-19.xlsx',skiprows=6,header=None,engine='openpyxl')
c19.fillna(0,inplace=True)<jupyter_output><empty_output><jupyter_text>particular literacy groups are- Illiterate- literate- Below Primary- primary- Middle- `Matric and Secondary+HS+Non tech+Tech as (Matric but below graduate)`- Grad and above<jupyter_code>c08=pd.read_excel('datasets/C-08.xlsx',engine='openpyxl',skiprows=7,header=None)<jupyter_output><empty_output><jupyter_text>obtain useful data from C-08 and C-19 for literacy-groups- first get particular state names for identifying specific states- get particular literacy-groups from C-19 file- make list of particular literacy group row/col for a particular states- now just simply iterate through each state to get relevent data and store it into a csv file    - to get total pop of particular literacy-range I have used C-08 file    - to get total pop that speaks more than 3 langs from a state in a particular literacy-range I have used C-19 file<jupyter_code># STATE_NAMES=[list(np.unique(c18.iloc[:,2].values))]
STATE_NAMES=[]
for state in c19.iloc[:,2].values:
    if not (state in STATE_NAMES) and state!=0:
        STATE_NAMES.append(state)

# although it is a bit of manual work but it is worth the efforts
LITRATE_GROUPS=['Illiterate', 'Literate but below primary', 'Primary but below middle', 'Middle but below matric/secondary', 'Matric/Secondary but below graduate', 'Graduate and above']
LITRATE_GROUP_COLS=[[[10,11]],[[19,20]],[[22,23]],[[25,26]],[[28,29],[31,32],[34,35],[37,38]],[[40,41]]]
useful_data=[]
for i,state in enumerate(STATE_NAMES):
    foo_df=c08[(c08.iloc[:,1]==i) & (c08.iloc[:,4]=='Total') & (c08.iloc[:,5]=='All ages')]
    for j,litracy_grp in enumerate(LITRATE_GROUPS):
        male_pop=0
        female_pop=0
        for litracy_list in LITRATE_GROUP_COLS[j]:
            male_pop+=foo_df.iloc[0,litracy_list[0]]
            female_pop+=foo_df.iloc[0,litracy_list[1]]
    
        # tri
        tri_male=c19[(c19.iloc[:,0]==str(i).zfill(2)) & (c19.iloc[:,4]==litracy_grp) & (c19.iloc[:,3]=='Total')].iloc[0,9]
        tri_female=c19[(c19.iloc[:,0]==str(i).zfill(2)) & (c19.iloc[:,4]==litracy_grp) & (c19.iloc[:,3]=='Total')].iloc[0,10]

        #bi
        bi_male=c19[(c19.iloc[:,0]==str(i).zfill(2)) & (c19.iloc[:,4]==litracy_grp) & (c19.iloc[:,3]=='Total')].iloc[0,6] - tri_male
        bi_female=c19[(c19.iloc[:,0]==str(i).zfill(2)) & (c19.iloc[:,4]==litracy_grp) & (c19.iloc[:,3]=='Total')].iloc[0,7] - tri_female

        #uni
        uni_male=male_pop-bi_male-tri_male
        uni_female=female_pop-bi_female-tri_female

        item={
            'state-code':i,
            'state-name':state,
            'litracy-group':litracy_grp,
            'litracy-group-male-pop':male_pop,
            'litracy-group-female-pop':female_pop,
            'tri-male-ratio':tri_male/male_pop,
            'tri-female-ratio':tri_female/female_pop,
            'bi-male-ratio':bi_male/male_pop,
            'bi-female-ratio':bi_female/female_pop,
            'uni-male-ratio':uni_male/male_pop,
            'uni-female-ratio':uni_female/female_pop
        }

        useful_data.append(item)
df=pd.DataFrame(useful_data)<jupyter_output><empty_output><jupyter_text>literacy-analysis - get highest ratio literacy-group for a state and store it into csv file- above process can be repeated for all parts of the question<jupyter_code>tri_list=[]
bi_list=[]
uni_list=[]
for i in range(36):
    male_values=df[df['state-code']==i].sort_values(by='tri-male-ratio',ascending=False).iloc[0,[2,5]].values
    female_values=df[df['state-code']==i].sort_values(by='tri-male-ratio',ascending=False).iloc[0,[2,6]].values
    tri_item={
        'state/ut':i,
        'litracy-group-males':male_values[0],
        'ratio-males':male_values[1],
        'litracy-group-females':female_values[0],
        'ratio-females':female_values[1]
    }

    tri_list.append(tri_item)

    male_values=df[df['state-code']==i].sort_values(by='bi-male-ratio',ascending=False).iloc[0,[2,7]].values
    female_values=df[df['state-code']==i].sort_values(by='bi-male-ratio',ascending=False).iloc[0,[2,8]].values
    bi_item={
        'state/ut':i,
        'litracy-group-males':male_values[0],
        'ratio-males':male_values[1],
        'litracy-group-females':female_values[0],
        'ratio-females':female_values[1]
    }

    bi_list.append(bi_item)

    male_values=df[df['state-code']==i].sort_values(by='uni-male-ratio',ascending=False).iloc[0,[2,9]].values
    female_values=df[df['state-code']==i].sort_values(by='uni-male-ratio',ascending=False).iloc[0,[2,10]].values
    uni_item={
        'state/ut':i,
        'litracy-group-males':male_values[0],
        'ratio-males':male_values[1],
        'litracy-group-females':female_values[0],
        'ratio-females':female_values[1]
    }

    uni_list.append(uni_item)<jupyter_output><empty_output><jupyter_text>- convert into pandas dataframes and store into CSVs<jupyter_code>tri_df=pd.DataFrame(tri_list)
bi_df=pd.DataFrame(bi_list)
uni_df=pd.DataFrame(uni_list)
tri_df.to_csv('outputs/litracy-gender-a.csv',index=False)
bi_df.to_csv('outputs/litracy-gender-b.csv',index=False)
uni_df.to_csv('outputs/litracy-gender-c.csv',index=False)<jupyter_output><empty_output><jupyter_text>observations- in almost all cases both highest ratio female and male age-groups are same.- interestingly in more than three language case for all states 'graduate and above' literacy group dominates, and it is also quite intutive; since they are more aducated and most of them speak more than 3+ langs. Also in exactly 2 languages also graduate and above literacy group dominants with same resoning it is also intutive- and this finding is in line with Q6 analysis of literacy groups- One expected thing is for only one language part. Here we get Illiterate literacy group to have highest ratio around 90% in almost every state. It is intutive also. We don't expect Illiterate people to have<jupyter_code>tri_df
uni_df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15074309978768577, 'fraction_numerical': 0.029234035603462355, 'mean_word_length': 4.4727435210008935, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '17514', 'license': 'MIT', 'n_tokens_mistral': 2370, 'n_tokens_neox': 2224, 'n_words': 408, 'path': 'Q9_asgn2.ipynb', 'repo_name': 'sunil-dhaka/census-language-analysis', 'size': '6123'}