<jupyter_start><jupyter_text>Codebook  **Authors:** <NAME>   Documenting existing data files of DaanMatch with information about location, owner, "version", source etc.<jupyter_code>import boto3
import numpy as np 
import pandas as pd
pd.plotting.register_matplotlib_converters()
import matplotlib.pyplot as plt
%matplotlib inline
from collections import Counter
import statistics
client = boto3.client('s3')
resource = boto3.resource('s3')
my_bucket = resource.Bucket('daanmatchdatafiles')<jupyter_output><empty_output><jupyter_text>Goa proforma_panchayat.xlsx TOC:* [About this dataset](1)* [Cleaned datasets](2)* [What's in this dataset](3)* [Codebook](4)    * [Missing values](4.1)    * [Summary statistics](4.2)* [Columns](5)    * [Sheet A](5.1)        * [Sr.No](5.1.1)        * [Name of the District along with State](5.1.2)        * [Name of Village Panchayat](5.1.3)        * [Name of Sarpanch](5.1.4)        * [Address with Pin Code](5.1.5)        * [Mobile No./Email Address](5.1.6)    * [Sheet B](5.2)        * [Sr.No](5.2.1)        * [Name of the District along with State](5.2.2)        * [Name of Gram Panchayat](5.2.3)        * [Gram Panchayat Bhawan Adress with Pin Code](5.2.4)    * [Sheet C](5.3)        * [Sr.No](5.3.1)        * [Name of the District along with State](5.3.2)        * [Name of Panchayat Officer (V.P. Secretary)](5.3.3)        * [Mobile No.](5.3.4)        * [Email Address](5.3.5)    * [Sheet E](5.4)        * [Sr.No](5.4.1)        * [Name of District along with State](5.4.2)        * [Name of Disctrict Panchayat President](5.4.3)        * [Address with Pin Code](5.4.4)        * [Mobile No. / Email Address](5.4.5)    * [Sheet F](5.5)        * [Sr.No](5.5.1)        * [Name of the State](5.5.2)        * [Name of the Nodal Officer / Designation](5.5.3)        * [Mobile No. / Email Address](5.5.4)        * [Postal Details](5.5.5) **About this dataset**    Data provided by: Unknown.  Source: https://daanmatchdatafiles.s3.us-west-1.amazonaws.com/Goa+proforma_panchayat.xlsx  Type: xlsx  Last Modified: June 14, 2021, 21:47:22 (UTC-07:00)  Size: 50.8 KB<jupyter_code>path = "s3://daanmatchdatafiles/Goa proforma_panchayat.xlsx"
goa_proforma = pd.ExcelFile(path)
print(goa_proforma.sheet_names)
goa_proforma_A = goa_proforma.parse('(A)')
goa_proforma_A.head()
goa_proforma_B = goa_proforma.parse('(B)')
goa_proforma_B.head()
goa_proforma_C = goa_proforma.parse('(C)')
goa_proforma_C.head()
goa_proforma_D = goa_proforma.parse('(D)')
goa_proforma_D.head()
goa_proforma_E = goa_proforma.parse('(E)')
goa_proforma_E.head()
goa_proforma_F = goa_proforma.parse('(F)')
goa_proforma_F.head()<jupyter_output><empty_output><jupyter_text>**Cleaned datasets**     Cleaning up the data in Sheet A:<jupyter_code>goa_proforma_A.rename(columns = {'Unnamed: 0' : 'Sr.No', 
                                 'Unnamed: 1' : 'Name of the District along with State', 
                                 'Unnamed: 2' : 'Name of Village Panchayat', 
                                 'Unnamed: 3' : 'Name of Sarpanch', 
                                 'Unnamed: 4' : 'Address with Pin Code', 
                                 'Unnamed: 5' : 'Mobile No./Email Address'}, inplace = True)
goa_proforma_A = goa_proforma_A.iloc[1:, :]
goa_proforma_A = goa_proforma_A.reset_index(drop = True)
goa_proforma_A<jupyter_output><empty_output><jupyter_text>Cleaning up the data in Sheet B:<jupyter_code>goa_proforma_B.rename(columns = {'PROFORMA' : 'Sr.No', 
                                 'Unnamed: 1' : 'Name of the District along with State', 
                                 'Unnamed: 2' : 'Name of Gram Panchayat', 
                                 'Unnamed: 3' : 'Gram Panchayat Bhawan Adress with Pin Code'}, inplace = True)
goa_proforma_B = goa_proforma_B.iloc[4:, :]
goa_proforma_B = goa_proforma_B.reset_index(drop = True)
goa_proforma_B<jupyter_output><empty_output><jupyter_text>Cleaning up the data in Sheet C:<jupyter_code>goa_proforma_C.rename(columns = {'PROFORMA' : 'Sr.No', 
                                 'Unnamed: 1' : 'Name of the District along with State', 
                                 'Unnamed: 2' : 'Name of Panchayat Officer (V.P. Secretary)', 
                                 'Unnamed: 3' : 'Mobile No.', 
                                 'Unnamed: 4' : 'Email Address'}, inplace = True)
goa_proforma_C = goa_proforma_C.iloc[4:, :]
goa_proforma_C = goa_proforma_C.reset_index(drop = True)
goa_proforma_C<jupyter_output><empty_output><jupyter_text>Cleaning up the data in Sheet D:<jupyter_code>goa_proforma_D<jupyter_output><empty_output><jupyter_text>Sheet D contains no information, so we can ignore it and proceed with cleaning the other sheets. Cleaning up the data in Sheet E:<jupyter_code>goa_proforma_E.rename(columns = {'(E)' : 'Sr.No', 
                                 'Unnamed: 1' : 'Name of District along with State', 
                                 'Unnamed: 2' : 'Name of District Panchayat President', 
                                 'Unnamed: 3' : 'Address with Pin Code', 
                                 'Unnamed: 4' : 'Mobile No. / Email Address'}, inplace = True)
goa_proforma_E = goa_proforma_E.iloc[2:, :]
goa_proforma_E = goa_proforma_E.reset_index(drop = True)
goa_proforma_E<jupyter_output><empty_output><jupyter_text>Cleaning up the data in Sheet F:<jupyter_code>goa_proforma_F
goa_proforma_F.rename(columns = {'PROFORMA' : 'Sr.No', 
                                 'Unnamed: 1' : 'Name of the State', 
                                 'Unnamed: 2' : 'Name of the Nodal Officer / Designation', 
                                 'Unnamed: 3' : 'Mobile No. / Email Address', 
                                 'Unnamed: 4' : 'Postal Details'}, inplace = True)
goa_proforma_F = goa_proforma_F.iloc[4:, :]
goa_proforma_F = goa_proforma_F.reset_index(drop = True)
goa_proforma_F<jupyter_output><empty_output><jupyter_text>**What's in this dataset?**<jupyter_code>print("Sheet A:")
print("Shape:", goa_proforma_A.shape)
print("Rows:", goa_proforma_A.shape[0])
print("Columns:", goa_proforma_A.shape[1])
print("Each row is a Sarpanch in Goa.")

print('\n')
print("Sheet B:")
print("Shape:", goa_proforma_B.shape)
print("Rows:", goa_proforma_B.shape[0])
print("Columns:", goa_proforma_B.shape[1])
print("Each row is a Gram Panchayat in Goa.")

print('\n')
print("Sheet C:")
print("Shape:", goa_proforma_C.shape)
print("Rows:", goa_proforma_C.shape[0])
print("Columns:", goa_proforma_C.shape[1])
print("Each row is a Panchayat Officer in Goa.")

print('\n')
print("Sheet E:")
print("Shape:", goa_proforma_E.shape)
print("Rows:", goa_proforma_E.shape[0])
print("Columns:", goa_proforma_E.shape[1])
print("Each row is a Disctrict Panchayat President in Goa.")

print('\n')
print("Sheet F:")
print("Shape:", goa_proforma_F.shape)
print("Rows:", goa_proforma_F.shape[0])
print("Columns:", goa_proforma_F.shape[1])
print("Each row is a Nodal Officer in Goa.")<jupyter_output>Sheet A:
Shape: (187, 6)
Rows: 187
Columns: 6
Each row is a Sarpanch in Goa.


Sheet B:
Shape: (187, 4)
Rows: 187
Columns: 4
Each row is a Gram Panchayat in Goa.


Sheet C:
Shape: (185, 5)
Rows: 185
Columns: 5
Each row is a Panchayat Officer in Goa.


Sheet E:
Shape: (4, 5)
Rows: 4
Columns: 5
Each row is a Disctrict Panchayat President in Goa.


Sheet F:
Shape: (1, 5)
Rows: 1
Columns: 5
Each row is a Nodal Officer in Goa.<jupyter_text>**Codebook**<jupyter_code>print("Codebook for Sheet A:")
goa_proforma_A_columns = [column for column in goa_proforma_A.columns]
goa_proforma_A_description = ["Meant for counting, is a null column.", 
                              "Name of district in Goa.", 
                              "Name of the village panchayat the Sarpanch is elected in.", 
                              "Name of the Sarpanch (elected offical).", 
                              "The address and pin code of the Sarpanch.", 
                              "The mobile phone number or email address of the Sarpanch."]
goa_proforma_A_dtypes = [dtype for dtype in goa_proforma_A.dtypes]

data_A = {"Column Name": goa_proforma_A_columns, "Description": goa_proforma_A_description, "Type": goa_proforma_A_dtypes}
goa_proforma_A_codebook = pd.DataFrame(data_A)
goa_proforma_A_codebook.style.set_properties(subset=['Description'], **{'width': '600px'})
print("Codebook for Sheet B:")
goa_proforma_B_columns = [column for column in goa_proforma_B.columns]
goa_proforma_B_description = ["Counts the number of Gram Panchayats.", 
                              "Name of district in Goa.", 
                              "Name of the Gram Panchayat (elected offical).", 
                              "The address and pin code of the Gram Panchayat."]
goa_proforma_B_dtypes = [dtype for dtype in goa_proforma_B.dtypes]

data_B = {"Column Name": goa_proforma_B_columns, "Description": goa_proforma_B_description, "Type": goa_proforma_B_dtypes}
goa_proforma_B_codebook = pd.DataFrame(data_B)
goa_proforma_B_codebook.style.set_properties(subset=['Description'], **{'width': '600px'})
print("Codebook for Sheet C:")
goa_proforma_C_columns = [column for column in goa_proforma_C.columns]
goa_proforma_C_description = ["Counts the number of Panchayat Officers.", 
                              "Name of district in Goa.", 
                              "Name of the Panchayat Officer (V.P. Secretary).", 
                              "The mobile phone number of the Panchayat Officer.", 
                              "The email address of the Panchayat Officer."]
goa_proforma_C_dtypes = [dtype for dtype in goa_proforma_C.dtypes]

data_C = {"Column Name": goa_proforma_C_columns, "Description": goa_proforma_C_description, "Type": goa_proforma_C_dtypes}
goa_proforma_C_codebook = pd.DataFrame(data_C)
goa_proforma_C_codebook.style.set_properties(subset=['Description'], **{'width': '600px'})
print("Codebook for Sheet E:")
goa_proforma_E_columns = [column for column in goa_proforma_E.columns]
goa_proforma_E_description = ["Counts the number of Disctrict Panchayat Presidents.", 
                              "Name of district in Goa.", 
                              "Name of the Disctrict Panchayat President.", 
                              "The address and pin code of the Disctrict Panchayat President.", 
                              "The mobile phone number or email address of the Disctrict Panchayat President."]
goa_proforma_E_dtypes = [dtype for dtype in goa_proforma_E.dtypes]

data_E = {"Column Name": goa_proforma_E_columns, "Description": goa_proforma_E_description, "Type": goa_proforma_E_dtypes}
goa_proforma_E_codebook = pd.DataFrame(data_E)
goa_proforma_E_codebook.style.set_properties(subset=['Description'], **{'width': '600px'})
print("Codebook for Sheet F:")
goa_proforma_F_columns = [column for column in goa_proforma_F.columns]
goa_proforma_F_description = ["Counts the number of Nodal Officers.", 
                              "Name of State in India.", 
                              "Name of the Nodal Officer and their designation.", 
                              "The mobile phone number or email address of the Nodal Officer.", 
                              "The address and pin code of the Nodal Officer."]
goa_proforma_F_dtypes = [dtype for dtype in goa_proforma_F.dtypes]

data_F = {"Column Name": goa_proforma_F_columns, "Description": goa_proforma_F_description, "Type": goa_proforma_F_dtypes}
goa_proforma_F_codebook = pd.DataFrame(data_F)
goa_proforma_F_codebook.style.set_properties(subset=['Description'], **{'width': '600px'})<jupyter_output>Codebook for Sheet F:<jupyter_text>**Missing values**<jupyter_code>goa_proforma_A.isnull().sum()
goa_proforma_B.isnull().sum()
goa_proforma_C.isnull().sum()
goa_proforma_E.isnull().sum()
goa_proforma_F.isnull().sum()<jupyter_output><empty_output><jupyter_text>**Summary statistics**<jupyter_code>goa_proforma_A.describe()
goa_proforma_B.describe()
goa_proforma_C.describe()
goa_proforma_E.describe()
goa_proforma_F.describe()<jupyter_output><empty_output><jupyter_text>Columns  Sheet ASheet A contains information about Sarpanchs in Goa.  Sr.No.Meant for counting, is a null column.<jupyter_code>column = goa_proforma_A["Sr.No"]
column
print("No. of unique values:", len(column.unique()))
counter = dict(Counter(column))
duplicates = {key:value for key, value in counter.items() if value > 1}
print("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))

print("No. of null values:", sum(column.isnull()))<jupyter_output>No. of unique values: 1
Duplicates: {nan: 187}
No. of duplicates: 1
No. of null values: 187<jupyter_text>Name of the District along with StateName of district in Goa.<jupyter_code>column = goa_proforma_A["Name of the District along with State"]
column
print("No. of unique values:", len(column.unique()))
counter = dict(Counter(column))
duplicates = {key:value for key, value in counter.items() if value > 1}
print("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))

print("No. of null values:", sum(column.isnull()))<jupyter_output>No. of unique values: 130
Duplicates: {'South Goa': 58}
No. of duplicates: 1
No. of null values: 0<jupyter_text>The first 128 values all consist of either "North Goa" or "South Goa", followed by "V.P." and a name of a Village Panchayat. This VP is repeated in the next column, so we can remove it to see how many counts we have in each district.<jupyter_code>districts = column.apply(lambda x: str(x[:9]))
districts
print("Unique values:", districts.unique())
print("No. of unique values:", len(districts.unique()))

# Table of number of Sarpanchs in each district
table = districts.value_counts().rename_axis('District').reset_index(name='Count')
table
plt.figure(figsize = (8, 5))
plt.bar(table["District"], table["Count"])
plt.title("Number of Sarpanch's in each District")
plt.xlabel("District")
plt.ylabel("Count")
plt.show()<jupyter_output><empty_output><jupyter_text>Name of Village PanchayatName of the village panchayat the Sarpanch is elected in.<jupyter_code>column = goa_proforma_A["Name of Village Panchayat"]
column
print("No. of unique values:", len(column.unique()))
counter = dict(Counter(column))
duplicates = {key:value for key, value in counter.items() if value > 1}
print("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))

print("No. of null values:", sum(column.isnull()))<jupyter_output>No. of unique values: 185
Duplicates: {'Querim': 2, 'Navelim': 2}
No. of duplicates: 2
No. of null values: 0<jupyter_text>Name of SarpanchName of the Sarpanch (elected offical).<jupyter_code>column = goa_proforma_A["Name of Sarpanch"]
column
print("No. of unique values:", len(column.unique()))
counter = dict(Counter(column))
duplicates = {key:value for key, value in counter.items() if value > 1}
print("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))

print("No. of null values:", sum(column.isnull()))
#Titles of officers
titles = column.apply(lambda x: str(x[:3]))
smt = 0; shr = 0; mr = 0; mrs = 0; none = 0
for title in titles:
    if (title == 'Smt'):
        smt += 1
    elif (title == 'Shr'):
        shr += 1
    elif (title == 'Mrs'):
        mrs += 1
    elif (title == 'Mr.'):
        mr += 1
    else:
        none += 1
titles_df = pd.DataFrame(data = {'Titles' : ['Smt.', 'Shri.', 'Mr.', 'Mrs.', 'None'], 
                                 'Counts' : [smt, shr, mr, mrs, none]})
titles_df
# Plot number of titles'
plt.figure(figsize = (10, 7))
plt.bar(titles_df["Titles"], titles_df["Counts"])
plt.title("Count of Titles")
plt.xlabel("Title")
plt.ylabel('Count')
plt.show()<jupyter_output><empty_output><jupyter_text>Address with Pin CodeThe address and pin code of the Sarpanch.<jupyter_code>column = goa_proforma_A["Address with Pin Code"]
column
print("No. of unique values:", len(column.unique()))
counter = dict(Counter(column))
duplicates = {key:value for key, value in counter.items() if value > 1}
print("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))

print("No. of null values:", sum(column.isnull()))<jupyter_output>No. of unique values: 150
Duplicates: {nan: 19, ' ---': 20}
No. of duplicates: 2
No. of null values: 19<jupyter_text>Mobile No./Email AddressThe mobile phone number or email address of the Sarpanch.<jupyter_code>column = goa_proforma_A["Mobile No./Email Address"]
column
print("No. of unique values:", len(column.unique()))
counter = dict(Counter(column))
duplicates = {key:value for key, value in counter.items() if value > 1}
print("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))

print("No. of null values:", sum(column.isnull()))<jupyter_output>No. of unique values: 187
Duplicates: {}
No. of null values: 1<jupyter_text>Sheet BSheet B contains information about Gram Panchayats in Goa.  Sr.No.Counts the number of Gram Panchayats.<jupyter_code>column = goa_proforma_B["Sr.No"]
column
print("No. of unique values:", len(column.unique()))
counter = dict(Counter(column))
duplicates = {key:value for key, value in counter.items() if value > 1}
print("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))

print("No. of null values:", sum(column.isnull()))<jupyter_output>No. of unique values: 187
Duplicates: {}
No. of null values: 0<jupyter_text>Name of the District along with StateName of district in Goa.<jupyter_code>column = goa_proforma_B["Name of the District along with State"]
column
print("No. of unique values:", len(column.unique()))
counter = dict(Counter(column))
duplicates = {key:value for key, value in counter.items() if value > 1}
print("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))

print("No. of null values:", sum(column.isnull()))
districts = column.apply(lambda x: str(x[:9]))

print("Unique values:", districts.unique())
print("No. of unique values:", len(districts.unique()))

# Table of number of Sarpanchs in each district
table = districts.value_counts().rename_axis('District').reset_index(name='Count')
table
plt.figure(figsize = (8, 5))
plt.bar(table["District"], table["Count"])
plt.title("Number of Gram Panchayats in each District")
plt.xlabel("District")
plt.ylabel("Count")
plt.show()<jupyter_output><empty_output><jupyter_text>Name of Gram PanchayatName of the Gram Panchayat (elected offical).<jupyter_code>column = goa_proforma_B["Name of Gram Panchayat"]
column
print("No. of unique values:", len(column.unique()))
counter = dict(Counter(column))
duplicates = {key:value for key, value in counter.items() if value > 1}
print("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))

print("No. of null values:", sum(column.isnull()))
goa_proforma_B[goa_proforma_B["Name of Gram Panchayat"].isin(duplicates)].sort_values('Name of Gram Panchayat')<jupyter_output><empty_output><jupyter_text>Duplicates in ```Name of Gram Panchayat``` are duplicates in rows.  Gram Panchayat Bhawan Adress with Pin CodeThe address and pin code of the Gram Panchayat.<jupyter_code>column = goa_proforma_B["Gram Panchayat Bhawan Adress with Pin Code"]
column
print("No. of unique values:", len(column.unique()))
counter = dict(Counter(column))
duplicates = {key:value for key, value in counter.items() if value > 1}
print("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))

print("No. of null values:", sum(column.isnull()))<jupyter_output>No. of unique values: 187
Duplicates: {}
No. of null values: 0<jupyter_text>Sheet CSheet C contains information about Panchayat Officers in Goa.  Sr.No.Counts the number of Panchayat Officers.<jupyter_code>column = goa_proforma_C["Sr.No"]
column
print("No. of unique values:", len(column.unique()))
counter = dict(Counter(column))
duplicates = {key:value for key, value in counter.items() if value > 1}
print("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))

print("No. of null values:", sum(column.isnull()))<jupyter_output>No. of unique values: 185
Duplicates: {}
No. of null values: 0<jupyter_text>Name of the District along with StateName of district in Goa.<jupyter_code>column = goa_proforma_C["Name of the District along with State"]
column
print("No. of unique values:", len(column.unique()))
counter = dict(Counter(column))
duplicates = {key:value for key, value in counter.items() if value > 1}
print("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))

print("No. of null values:", sum(column.isnull()))
districts = column.apply(lambda x: str(x[:9]))

print("Unique values:", districts.unique())
print("No. of unique values:", len(districts.unique()))

# Table of number of Sarpanchs in each district
table = districts.value_counts().rename_axis('District').reset_index(name='Count')
table
plt.figure(figsize = (8, 5))
plt.bar(table["District"], table["Count"])
plt.title("Number of Panchayat Officers in each District")
plt.xlabel("District")
plt.ylabel("Count")
plt.show()<jupyter_output><empty_output><jupyter_text>Name of Panchayat Officer (V.P. Secretary)Name of the Panchayat Officer (V.P. Secretary).<jupyter_code>column = goa_proforma_C["Name of Panchayat Officer (V.P. Secretary)"]
column
print("No. of unique values:", len(column.unique()))
counter = dict(Counter(column))
duplicates = {key:value for key, value in counter.items() if value > 1}
print("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))

print("No. of null values:", sum(column.isnull()))
goa_proforma_C[goa_proforma_C["Name of Panchayat Officer (V.P. Secretary)"].isin(duplicates)].sort_values("Name of Panchayat Officer (V.P. Secretary)").head()<jupyter_output><empty_output><jupyter_text>Duplicates in ```Name of Panchayat Officer (V.P. Secretary)``` are duplicates in rows.<jupyter_code>#Titles of officers
titles = column.apply(lambda x: str(x[:3]))
smt = 0; shr = 0; mr = 0; mrs = 0; none = 0
for title in titles:
    if (title == 'Smt'):
        smt += 1
    elif (title == 'Shr'):
        shr += 1
    elif (title == 'Mrs'):
        mrs += 1
    elif (title == 'Mr.'):
        mr += 1
    else:
        none += 1
titles_df = pd.DataFrame(data = {'Titles' : ['Smt.', 'Shri.', 'Mr.', 'Mrs.', 'None'], 
                                 'Counts' : [smt, shr, mr, mrs, none]})
titles_df
# Plot number of titles'
plt.figure(figsize = (10, 7))
plt.bar(titles_df["Titles"], titles_df["Counts"])
plt.title("Count of Titles")
plt.xlabel("Title")
plt.ylabel('Count')
plt.show()<jupyter_output><empty_output><jupyter_text>Mobile No.The mobile phone number of the Panchayat Officer.<jupyter_code>column = goa_proforma_C["Mobile No."]
column
print("No. of unique values:", len(column.unique()))
counter = dict(Counter(column))
duplicates = {key:value for key, value in counter.items() if value > 1}
print("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))

print("No. of null values:", sum(column.isnull()))
goa_proforma_C[goa_proforma_C["Mobile No."] == 9421194391]
goa_proforma_C[goa_proforma_C["Mobile No."] == 9890419521]<jupyter_output><empty_output><jupyter_text>Duplicates in ```Mobile No.``` are duplicates in rows.  Email AddressThe email address of the Panchayat Officer.<jupyter_code>column = goa_proforma_C["Email Address"]
column
print("No. of unique values:", len(column.unique()))
counter = dict(Counter(column))
duplicates = {key:value for key, value in counter.items() if value > 1}
print("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))

print("No. of null values:", sum(column.isnull()))
column.dropna()<jupyter_output><empty_output><jupyter_text>Sheet ESheet E contains information about Disctrict Panchayat Presidents in Goa.  Sr.No.Counts the number of Disctrict Panchayat Presidents.<jupyter_code>column = goa_proforma_E["Sr.No"]
column
print("No. of unique values:", len(column.unique()))<jupyter_output>No. of unique values: 4<jupyter_text>Name of District along with StateName of district in Goa.<jupyter_code>column = goa_proforma_E["Name of District along with State"]
column
districts = column.apply(lambda x: str(x[:9]))

print("Unique values:", districts.unique())
print("No. of unique values:", len(districts.unique()))

# Table of number of Sarpanchs in each district
table = districts.value_counts().rename_axis('District').reset_index(name='Count')
table
plt.figure(figsize = (7, 5))
plt.bar(table["District"], table["Count"])
plt.title("Number of Disctrict Panchayat Presidents in each District")
plt.xlabel("District")
plt.ylabel("Count")
plt.show()<jupyter_output><empty_output><jupyter_text>Name of District Panchayat PresidentName of the Disctrict Panchayat President.<jupyter_code>column = goa_proforma_E["Name of District Panchayat President"]
column
print("No. of unique values:", len(column.unique()))
#Titles of officers
titles = column.apply(lambda x: str(x[:3]))
smt = 0; shr = 0
for title in titles:
    if (title == 'Smt'):
        smt += 1
    elif (title == 'Shr'):
        shr += 1
titles_df = pd.DataFrame(data = {'Titles' : ['Smt.', 'Shri.'], 
                                 'Counts' : [smt, shr]})
titles_df
# Plot number of titles'
plt.figure(figsize = (7, 5))
plt.bar(titles_df["Titles"], titles_df["Counts"])
plt.title("Count of Titles")
plt.xlabel("Title")
plt.ylabel('Count')
plt.show()<jupyter_output><empty_output><jupyter_text>Address with Pin CodeThe address and pin code of the Disctrict Panchayat President.<jupyter_code>column = goa_proforma_E["Address with Pin Code"]
column
print("No. of unique values:", len(column.unique()))<jupyter_output>No. of unique values: 4<jupyter_text>Mobile No. / Email AddressThe mobile phone number or email address of the Disctrict Panchayat President.<jupyter_code>column = goa_proforma_E["Mobile No. / Email Address"]
column
print("No. of unique values:", len(column.unique()))<jupyter_output>No. of unique values: 4<jupyter_text>Sheet FSheet F contains information about the Nodal Officer in Goa.      Since there is only one row in the DataFrame, for each column I will just print out the value in the row.  Sr.No.Counts the number of Nodal Officers.<jupyter_code>goa_proforma_F["Sr.No"][0]<jupyter_output><empty_output><jupyter_text>Name of the StateName of State in India.<jupyter_code>goa_proforma_F["Name of the State"][0]<jupyter_output><empty_output><jupyter_text>Name of the Nodal Officer / DesignationName of the Nodal Officer and their designation.<jupyter_code>goa_proforma_F["Name of the Nodal Officer / Designation"][0]<jupyter_output><empty_output><jupyter_text>Mobile No. / Email AddressThe mobile phone number or email address of the Nodal Officer.<jupyter_code>goa_proforma_F["Mobile No. / Email Address"][0]<jupyter_output><empty_output><jupyter_text>Postal DetailsThe address and pin code of the Nodal Officer.<jupyter_code>goa_proforma_F["Postal Details"][0]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13565134028981232, 'fraction_numerical': 0.013273846834556248, 'mean_word_length': 4.278707668353445, 'pattern_counts': {'":': 15, '<': 145, '<?xml version=': 0, '>': 175, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 23, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '39', 'dir': 'jupyter-structured-clean-dedup', 'id': '25821', 'license': 'MIT', 'n_tokens_mistral': 9820, 'n_tokens_neox': 9366, 'n_words': 2435, 'path': 'Goa proforma_panchayat/.ipynb_checkpoints/Goa proforma_panchayat-checkpoint.ipynb', 'repo_name': 'GeLi2001/Codebook', 'size': '27127'}