<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
import datetime as dt
pd.plotting.register_matplotlib_converters()
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
%matplotlib inline
import seaborn as sns
from collections import Counter
import statistics
import re
#from langdetect import detect

# Suppress loc warnings
pd.options.mode.chained_assignment = None
client = boto3.client('s3')
resource = boto3.resource('s3')
my_bucket = resource.Bucket('my-bucket')<jupyter_output><empty_output><jupyter_text>42621 Final_Data_ngodarpan.gov.in TOC:* [About this dataset](1)* [What's in this dataset](2)* [Codebook](3)    * [Missing values](3.1)    * [Summary statistics](3.2)* [Columns](4)    * [Name](4.1)    * [ngo url](4.2)    * [Mobile](4.3)    * [UniqueID](4.4)    * [Off phone1](4.5)    * [Email](4.6)    * [Major Activities1](4.7)    * [operational states db](4.8)    * [issues working db](4.9)    * [operational district db](4.10)    * [reg name](4.11)    * [fcrano](4.12)    * [nr regNo](4.13)    * [nr add](4.14)    * [nr orgName](4.15)    * [ngo reg date](4.16)    * [nr actName](4.17)    * [nr city](4.18)    * [TypeDescription](4.19)    * [StateName](4.20)    * [status](4.21)    * [president name](4.22)    * [president email](4.22)    * [president mobile](4.22)    * [Chairman name](4.23)    * [Chairman email](4.23)    * [Chairman mobile](4.23)    * [Secretary name](4.24)    * [Secretary email](4.24)    * [Secretary mobile](4.24)    * [Asisstant Secretary name](4.25)    * [Asisstant Secretary email](4.25)    * [Asisstant Secretary mobile](4.25)    * [Board Member name](4.26)    * [Board Member email](4.26)    * [Board Member mobile](4.26)    * [Vice Chairman name](4.27)    * [Vice Chairman email](4.27)    * [Vice Chairman mobile](4.27)    * [Member name](4.28)    * [Member email](4.28)    * [Member mobile](4.28)<jupyter_code># Lists out the column names in TOC format
def toc_maker(dataset):
    counter = 1
    for column in dataset.columns:
        print("* ["+column+"](4."+str(counter)+")")
        counter +=1
#toc_maker(Final_Data_ngodarpan)<jupyter_output><empty_output><jupyter_text>**About this dataset**    Data provided by: NGO Darpan  Source: ngodarpan.gov.in   Type: xlsx  Last Modified: June 1, 2021, 17:06:30 (UTC-07:00)  Size: 49.7 MB<jupyter_code>path = "s3://daanmatchdatafiles/Darpan21FCRA/42621 Final_Data_ngodarpan.gov.in.xlsx"
xl = pd.ExcelFile(path)
print(xl.sheet_names)
Final_Data_ngodarpan = xl.parse('ngodarpan.gov.in')
Final_Data_ngodarpan.head()<jupyter_output>['ngodarpan.gov.in']<jupyter_text>**What's in this dataset?**<jupyter_code>dataset = Final_Data_ngodarpan
print("Shape:", dataset.shape)
print("Rows:", dataset.shape[0])
print("Columns:", dataset.shape[1])
print("Each row is a NGO.")<jupyter_output><empty_output><jupyter_text>**Codebook**<jupyter_code>dataset_columns = [column for column in dataset.columns]
dataset_desc = ["Name of NGO",
                "URL for NGO",
                "Mobile phone",
                "Unique ID of VO/NGO",
                "Telephone/Alternate number",
                "Email address",
                "Description of major activities",
                "List of states or union territories they operate in.",
                "List of issues they are working on",
                "List of districts they operate in",
                "Name of registrar",
                "FCRA number",
                "Registration number",
                "Address",
                "Name of NGO",
                "Registration date",
                "Name of Act",
                "City of NGO",
                "Type of NGO. Either registerd societies, trust, etc.",
                "Name of state",
                "Status",
                "President name",
                "President email",
                "President mobile",
                "Chairman name",
                "Chairman email",
                "Chairman mobile",
                "Secretary name",
                "Secretary email",
                "Secretary mobile",
                "Asisstant Secretary name",
                "Asisstant Secretary email",
                "Asisstant Secretary mobile",
                "Board Member name",
                "Board Member email",
                "Board Member mobile",
                "Vice Chairman name",
                "Vice Chairman email",
                "Vice Chairman mobile",
                "Member name",
                "Member email",
                "Member mobile"
               ]
dataset_desc = dataset_desc + ["N/A"] * (len(dataset_columns) - len(dataset_desc))
dataset_dtypes = [dtype for dtype in dataset.dtypes]

data = {"Column Name": dataset_columns, "Description": dataset_desc, "Type": dataset_dtypes}
codebook = pd.DataFrame(data)
codebook<jupyter_output><empty_output><jupyter_text>**Missing values**<jupyter_code>Final_Data_ngodarpan.isnull().sum()<jupyter_output><empty_output><jupyter_text>**Summary statistics**  None. All qualitative features.  Columns  NameName of NGO.  No. of unique values: 109682  No. of duplicates: 1548<jupyter_code>column = dataset["Name"]
column
# Number of empty strings/missing values
print("Invalid:", sum(column == " ") + sum(column.isnull()))
print("No. of unique values:", len(column.unique()))
# Check for duplicates
counter = dict(Counter(column))
duplicates = { key:[value] for key, value in counter.items() if value > 1}
print("No. of duplicates:", len(duplicates))
table = pd.DataFrame.from_dict(duplicates)
table = table.melt(var_name="Duplicate Names", value_name="Count").sort_values(by=["Count"], ascending=False).reset_index(drop=True)
table
# Example
dataset[column == table.iloc[0,0]].head()<jupyter_output><empty_output><jupyter_text>Same ```name``` does not mean duplicate rows.  ngo urlURL for NGO.  No. of unique values: 24253  No. of duplicates values: 202   A lot of NGOs were confused in the information filling process, and pasted the NGO darpan URL instead of the URL to their NGO's website if there is one: the first 13. So there are a large number of invalid URLs.  Additionally, a large number of urls cannot be reached.<jupyter_code>column = dataset["ngo url"]
column
# Number of empty strings/missing values
print("Invalid:", sum(column == " ") + sum(column.isnull()))

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

# Check for duplicates
counter = dict(Counter(column))
duplicates = { key:[value] for key, value in counter.items() if value > 1}
print("No. of Duplicates:", len(duplicates))

table = pd.DataFrame.from_dict(duplicates)
table = table.melt(var_name="Duplicate URLs", value_name="Count").sort_values(by=["Count"], ascending=False).reset_index(drop=True)
table<jupyter_output>Invalid: 86142
No. of unique values: 24253
No. of Duplicates: 202<jupyter_text>A lot of NGOs were confused in the information filling process, and pasted the NGO darpan URL instead of the URL to their NGO's website if there is one: the first 13. So there are a large number of invalid URLs. Additionally, a large number of urls cannot be reached.<jupyter_code>table.iloc[13:]
# Example
dataset[column == table.iloc[13,0]].head()<jupyter_output><empty_output><jupyter_text>Duplicates for ```ngo url``` do not mean duplicate rows.  MobileMobile number. Incorrect dtype.  Varying lengths for mobile: 9, 10, 11No. of null values: 32  No. of unique values: 111431  No. of duplicate values: 402.<jupyter_code>column = dataset["Mobile"]
column
# Number of missing values
print("Nulls:", sum(column.isnull()))

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

# Drop nulls and remove float and convert to str.
column = column.dropna().astype(int).astype(str)

# Lenght of mobile number
print("Lengths:", column.apply(len).unique())

# Check for duplicates
counter = dict(Counter(column))
duplicates = { key:[value] for key, value in counter.items() if value > 1}
print("No. of Duplicates:", len(duplicates))

table = pd.DataFrame.from_dict(duplicates)
table = table.melt(var_name="Duplicate Mobile numbers", value_name="Count").sort_values(by=["Count"], ascending=False).reset_index(drop=True)
table
# Example
dataset[dataset["Mobile"] == int(table.iloc[0,0])].head()<jupyter_output><empty_output><jupyter_text>Duplicate ```Mobile``` does not mean duplicate rows.  UniqueIDUnique ID of VO/NGO.  No. of unique values: 111929<jupyter_code>column = dataset["UniqueID"]
column
# Number of missing values
print("Nulls:", sum(column.isnull()))

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

# Check for duplicates
counter = dict(Counter(column))
duplicates = { key:[value] for key, value in counter.items() if value > 1}
print("No. of Duplicates:", len(duplicates))<jupyter_output>Nulls: 0
No. of unique values: 111929
No. of Duplicates: 0<jupyter_text>Off phone1Telephone/Alternate number.  Contains invalid numbers: All zeros. Invalid formats: mysql_prep(Mobile) Numbers of varying length.Nulls: 95402  No. of unique values: 15364  No. of duplicates: 641.<jupyter_code>column = dataset["Off phone1"]
column
# Number of missing values
print("Nulls:", sum(column.isnull()))

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

# Drop nulls and remove hyphen and mysql_prep()
column = column.dropna()
cleaned_column = [re.sub('[^0-9]','', row) for row in column]
column = pd.Series(cleaned_column)



# Check for duplicates
counter = dict(Counter(column))
duplicates = { key:[value] for key, value in counter.items() if value > 1}
del duplicates["0000000000000"]
del duplicates["00000000000"]
del duplicates["0000000000"]
del duplicates["000000000"]
del duplicates["00000000"]
print("No. of Duplicates:", len(duplicates))

table = pd.DataFrame.from_dict(duplicates)
table = table.melt(var_name="Duplicate Off phone1 numbers", value_name="Count").sort_values(by=["Count"], ascending=False).reset_index(drop=True)

# Lenght of numbers
print("Lengths:", table["Duplicate Off phone1 numbers"].apply(len).unique())

table<jupyter_output>Nulls: 95402
No. of unique values: 15364
No. of Duplicates: 639
Lengths: [11 10  9]<jupyter_text>Column contains numbers of varying length.<jupyter_code># Example
dataset[dataset["Off phone1"].str.replace("-", "") == table.iloc[3,0]].head()<jupyter_output><empty_output><jupyter_text>Duplicate ```Off phone1``` does not mean duplicate rows.  EmailEmail address.    No. of unique values: 111557  No. of Duplicates: 340<jupyter_code>column = dataset["Email"]
column
# Number of missing values
print("Nulls:", sum(column.isnull()))

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

# Lower case all emails
column = column.str.lower()

# Check for duplicates
counter = dict(Counter(column))
duplicates = { key:[value] for key, value in counter.items() if value > 1}
print("No. of Duplicates:", len(duplicates))

table = pd.DataFrame.from_dict(duplicates)
table = table.melt(var_name="Duplicate Email IDs", value_name="Count").sort_values(by=["Count"], ascending=False).reset_index(drop=True)
table
# Example
dataset[dataset["Email"] == table.iloc[0,0]].head()
domain = [split[1] for split in column.str.split("@",1)]
domain = pd.Series(domain).value_counts()
domain_no = len(domain)
print("No. of domains:", domain_no)
domain.head()<jupyter_output>No. of domains: 6685<jupyter_text>Duplicate ```Email``` does not mean duplicate rows.  Major Activities1Description of major activities.  Not all in english.  Nulls: 27311  No. of descriptions with special characters: 1475   No. of unique values: 75696  No. of Duplicates: 1680<jupyter_code>column = dataset["Major Activities1"]
column
# Number of missing values
print("Nulls:", sum(column.isnull()))
column = column.dropna()

# Lowercase all strings
column = column.str.lower()

def isEnglish(s):
    try:
        s.encode(encoding='utf-8').decode('ascii')
    except UnicodeDecodeError:
        return False
    else:
        return True
    
language = [isEnglish(row) for row in column]
print("No. of descriptions with special characters:", len(language) - sum(language))
column = column[language]

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

# Check for duplicates
counter = dict(Counter(column))
duplicates = { key:[value] for key, value in counter.items() if value > 1}
print("No. of Duplicates:", len(duplicates))

table = pd.DataFrame.from_dict(duplicates)
table = table.melt(var_name="Duplicate Major Activities", value_name="Count").sort_values(by=["Count"], ascending=False).reset_index(drop=True)
table<jupyter_output>Nulls: 27311
No. of descriptions with special characters: 1475
No. of unique values: 75696
No. of Duplicates: 1680<jupyter_text>There still remains descriptions that have special characters in other languages besides English.    operational states dbList of states or union territories they operate in.  Rows contain multiple states and some rows contain duplicate states. Invalid value: testingswss  No. of unique values: 37Orissa is the former name of the state Odisha.  Former union territories Dadra and Nagar Haveli and Daman and Diu have merged together. [Source](https://en.wikipedia.org/wiki/Dadra_and_Nagar_Haveli_and_Daman_and_Diu).<jupyter_code>column = dataset["operational states db"]
column
# Nulls
print("Nulls:", sum(column.isnull()))
column = column.dropna()

# Empty strings
print("Empty strings:", sum(column == ""))

# Lowercase all strings
column = column.str.lower()<jupyter_output>Nulls: 23039
Empty strings: 0<jupyter_text>Rows contain multiple states, and some rows contain duplicate states.<jupyter_code>column.value_counts().tail(3)
# Strip leading and tailing whitespace and create set to remove duplicates in list
split_column = [list(set([i.strip() for i in row])) for row in column.str.split(',')]    

# Drop([0]) to remove empty string and  testingswss
cleaned_column_operational_states = pd.Series(split_column).explode().value_counts().rename_axis('Value').reset_index(name='Counts').drop([0, 26])
print("Unique Value:", len(cleaned_column_operational_states))
cleaned_column_operational_states
cleaned_column_operational_states.plot.bar(x="Value", y="Counts", figsize=(16,4))
ax = plt.title("Count of Operational States")
plt.xlabel("Operational States")
plt.show()<jupyter_output><empty_output><jupyter_text>issues working dbList of issues they are working on.  Some rows contain multiple issues.  Nulls: 22637  Unique values: 44<jupyter_code>column = dataset["issues working db"]
column
# Nulls
print("Nulls:", sum(column.isnull()))
column = column.dropna()

# Lowercase all strings
column = column.str.lower()

# Strip leading and tailing whitespace and create set to remove duplicates in list
split_column = [list(set([i.strip() for i in row])) for row in column.str.split(',')]    

# Drop([0]) to remove empty string
cleaned_column = pd.Series(split_column).explode().value_counts().rename_axis('Value').reset_index(name='Counts').drop([0])
cleaned_column
cleaned_column.plot.bar(x="Value", y="Counts", figsize=(16,4))
ax = plt.title("Count of Issues working")
plt.xlabel("Issues working")
plt.show()<jupyter_output><empty_output><jupyter_text>operational district dbList of districts they operate in.  Some rows contain multiple districts and arrows. Invalid value: testingswss  Arrows point from state/union territory to district  Nulls: 23039  No. of unique values: 728<jupyter_code>column = dataset["operational district db"]
column
# Nulls
print("Nulls:", sum(column.isnull()))
column = column.dropna()

# Lowercase all strings
column = column.str.lower()

# Remove arrows
column = column.str.replace('->', ',')

# Strip leading and tailing whitespace and create set to remove duplicates in list
split_column = [list(set([i.strip() for i in row])) for row in column.str.split(',')]    

# Drop([0]) to remove empty string
cleaned_column = pd.Series(split_column).explode().value_counts().rename_axis('Value').reset_index(name='Counts').drop([0])
print(cleaned_column[cleaned_column["Value"] == "testingswss"])
# Drop([330]) to remove testingswss
cleaned_column = cleaned_column.drop([330])
print("No. of unique values:", len(cleaned_column["Value"].unique()))
cleaned_column<jupyter_output>Nulls: 23039
           Value  Counts
331  testingswss     574
No. of unique values: 728<jupyter_text>There are 718 districts in India. However there are 728 unique rows.  reg nameName of registrar.    No. of unique values: 7<jupyter_code>column = dataset["reg name"]
column
# Nulls
print("Nulls:", sum(column.isnull()))

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

counts = column.value_counts().rename_axis('Value').reset_index(name='Counts')
counts
counts.plot.bar(x="Value", y="Counts")
ax = plt.title("Count reg name")
plt.xlabel("Registrar name")
plt.show()<jupyter_output><empty_output><jupyter_text>fcranoFCRA number.  Some numbers are invalid and have differing lengths..  Nulls: 89869  No. of unique values: 21145<jupyter_code>column = dataset["fcrano"]
column
# Nulls
print("Nulls:", sum(column.isnull()))
column = column.dropna()

# Value counts
column.value_counts().rename_axis('Value').reset_index(name='Counts')<jupyter_output>Nulls: 89869<jupyter_text>Some numbers are invalid.<jupyter_code># Only contain rows that have digits
cleaned_column = column[column.str.isdigit()].value_counts().rename_axis('Value').reset_index(name='Counts')
# Length of fcranos
print("Lengths:", cleaned_column["Value"].apply(len).unique())
print("No. of unique values:", len(cleaned_column["Value"].unique()))
cleaned_column<jupyter_output>Lengths: [ 5  2  1  9  4  3  7  8 11  6 14 10 13 15 12 16]
No. of unique values: 21145<jupyter_text>nr regNoRegistration number.  Nulls: 3  No. of unique values: 96123  No. of Duplicates: 4986<jupyter_code>column = dataset["nr regNo"]
column
# Nulls
print("Nulls:", sum(column.isnull()))
column = column.dropna()

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

# Check for duplicates
counter = dict(Counter(column))
duplicates = { key:[value] for key, value in counter.items() if value > 1}
print("No. of Duplicates:", len(duplicates))

table = pd.DataFrame.from_dict(duplicates)
table = table.melt(var_name="Duplicate regNo", value_name="Count").sort_values(by=["Count"], ascending=False).reset_index(drop=True)
table<jupyter_output>Nulls: 3
No. of unique values: 96123
No. of Duplicates: 4986<jupyter_text>nr addAddress.  Some invalid addresses, mixed letter case.  No. of unique values: 110903  No. of Duplicates: 600<jupyter_code>column = dataset["nr add"]
column
# Nulls
print("Nulls:", sum(column.isnull()))
column = column.dropna()

# Emptry strings
print("Empty strings:", sum(column == " "))

# Lowercase all strings
column = column.str.lower()

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

# Check for duplicates
counter = dict(Counter(column))
duplicates = { key:[value] for key, value in counter.items() if value > 1}
print("No. of Duplicates:", len(duplicates))

table = pd.DataFrame.from_dict(duplicates)
table = table.melt(var_name="Duplicate regNo", value_name="Count").sort_values(by=["Count"], ascending=False).reset_index(drop=True)
table
# Drop invalid addresses
table.drop([0,1,2,3,4])<jupyter_output><empty_output><jupyter_text>nr orgNameName of NGO.  Mixed letter case.  No. of unique values: 108296  No. of Duplicates: 2290<jupyter_code>column = dataset["nr orgName"]
column
# Nulls
print("Nulls:", sum(column.isnull()))
column = column.dropna()

# Emptry strings
print("Empty strings:", sum(column == " "))

# Lowercase all strings
column = column.str.lower()

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

# Check for duplicates
counter = dict(Counter(column))
duplicates = { key:[value] for key, value in counter.items() if value > 1}
print("No. of Duplicates:", len(duplicates))

table = pd.DataFrame.from_dict(duplicates)
table = table.melt(var_name="Duplicate Value", value_name="Count").sort_values(by=["Count"], ascending=False).reset_index(drop=True)
table<jupyter_output>Nulls: 0
Empty strings: 0
No. of unique values: 108296
No. of Duplicates: 2290<jupyter_text>ngo reg dateRegistration date.   Format: DD-MM-YYYY  No. of unique values: 17307<jupyter_code>column = dataset["ngo reg date"]
column
print("Min:", min(column))
print("Max:", max(column))
column.apply(len).unique()
# Nulls
print("Nulls:", sum(column.isnull()))
column = column.dropna()

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

# Value counts
counts = column.value_counts().rename_axis('Value').reset_index(name='Counts')
counts
dates = pd.Series([dt.datetime.strptime(d,'%d-%m-%Y').date().year for d in column])
dates_count = dates.value_counts().rename_axis('Year').reset_index(name='Counts')
dates_count = dates_count[dates_count["Year"] >= 1917]
# Plot of number of each year
plt.bar(dates_count["Year"], np.log(dates_count["Counts"]))
plt.title("Count of Log year of  ngo reg date")
plt.ylabel("Log Count")
plt.xlabel("Year")
plt.show()<jupyter_output><empty_output><jupyter_text>nr actNameName of Act.   Nulls: 1316  No. of unique values: 30198<jupyter_code>column = dataset["nr actName"]
column
# Nulls
print("Nulls:", sum(column.isnull()))
column = column.dropna()

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

# Value counts
counts = column.value_counts().rename_axis('Value').reset_index(name='Counts')
counts<jupyter_output>Nulls: 1316
No. of unique values: 30198<jupyter_text>nr cityCity of NGO.  Mixed letter case.  Nulls: 214  No. of unique values: 12953<jupyter_code>column = dataset["nr city"]
column
# Nulls
print("Nulls:", sum(column.isnull()))
column = column.dropna()

# Lowercase all strings
column = column.str.lower()

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

# Value counts
counts = column.value_counts().rename_axis('Value').reset_index(name='Counts')
counts<jupyter_output>Nulls: 214
No. of unique values: 12953<jupyter_text>TypeDescriptionType of NGO. Either registered societies, trust, etc.    No. of unique values: 6<jupyter_code>column = dataset["TypeDescription"]
column
# Nulls
print("Nulls:", sum(column.isnull()))
column = column.dropna()

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

# Value counts
counts = column.value_counts().rename_axis('Value').reset_index(name='Counts')
counts<jupyter_output>Nulls: 0
No. of unique values: 6<jupyter_text>StateNameName of state.    Names in uppercase.  No. of unique values: 37<jupyter_code>column = dataset["StateName"]
column
# Nulls
print("Nulls:", sum(column.isnull()))
#column = column.dropna()

# Lowercase all strings
column = column.str.lower()

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

# Value counts
counts = column.value_counts().rename_axis('Value').reset_index(name='Counts')
counts.head()
#Compare with operational states db column
cleaned_column_operational_states.head()<jupyter_output><empty_output><jupyter_text>```StateName``` count is not the same as ```operational district db```, but value is the same.  statusStatus.    Entire column is null.Nulls: 111929<jupyter_code>column = dataset["status"]
column
# Nulls
print("Nulls:", sum(column.isnull()))
#column = column.dropna()

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

# Value counts
counts = column.value_counts().rename_axis('Value').reset_index(name='Counts')
counts.head()<jupyter_output>Nulls: 111929
No. of unique values: 1<jupyter_text>president name + president email + president mobilePresident contact info.  Same amount of nulls for each row. Name has mixed letter case. Mobile in incorrect dtype.    Nulls:  president name      52520  president email     52520  president mobile    52520  No. of unique values:  president name: 52425  president email: 58518  president mobile: 58019<jupyter_code>name = "<NAME>"
email = "president email"
mobile = "president mobile"
df = dataset.loc[:,[name, email, mobile]]
df
# Nulls
print("Nulls:")
print(df.isnull().sum())
df = df.dropna()
# Convert mobile from float64 to int to remove decimals and then to str object
df.loc[:,mobile] = df[mobile].astype(int).astype(str)

# Lowercase names
df.loc[:, name] = df[name].str.lower()
# Unique values
print("No. of unique values:")
for col in df:
    print(col + ":", len(df.loc[:,col].unique()))
df[name].value_counts().rename_axis('Value').reset_index(name='Counts')
df[df[name] == "<NAME>"].head()<jupyter_output><empty_output><jupyter_text>Same ```president name``` does not mean same ```email``` or ```mobile```.<jupyter_code>df[email].value_counts().rename_axis('Value').reset_index(name='Counts')
df[df[email] == "<EMAIL>"].head()<jupyter_output><empty_output><jupyter_text>Same ```email``` does not mean same mobile. Some president names are inputted differently even though they refer to the same person.<jupyter_code>df[mobile].value_counts().rename_axis('Value').reset_index(name='Counts')
df[df[mobile] == "8870868340"].head()<jupyter_output><empty_output><jupyter_text>Same ```mobile``` does not mean same email. Some president names are inputted differently even though they refer to the same person.  Chairman name + Chairman email + Chairman mobileChairman contact info.  Different amount of nulls for each row. Name has mixed letter case. Mobile in incorrect dtype.    Nulls:  Chairman name      82126  Chairman email     82132  Chairman mobile    82137    No. of unique values:  Chairman name: 24523  Chairman email: 29349  Chairman mobile: 29080<jupyter_code>name = "<NAME>"
email = "Chairman email"
mobile = "Chairman mobile"
df = dataset.loc[:,[name, email, mobile]]
df
# Nulls
print("Nulls:")
print(df.isnull().sum())
df = df.dropna()
# Convert mobile from float64 to int to remove decimals and then to str object
df.loc[:,mobile] = df[mobile].astype(int).astype(str)

# Lowercase names
df.loc[:, name] = df[name].str.lower()
# Unique values
print("No. of unique values:")
for col in df:
    print(col + ":", len(df.loc[:,col].unique()))
df[name].value_counts().rename_axis('Value').reset_index(name='Counts')<jupyter_output><empty_output><jupyter_text>Contains invalid names.<jupyter_code>df[email].value_counts().rename_axis('Value').reset_index(name='Counts')
df[df[email] == "<EMAIL>"].head()<jupyter_output><empty_output><jupyter_text>Same ```email``` most refers to same ```name``` (different inputs) and ```mobile```.<jupyter_code>df[mobile].value_counts().rename_axis('Value').reset_index(name='Counts')
df[df[mobile] == "9422346713"].head()<jupyter_output><empty_output><jupyter_text>Same ```mobile``` mostly refers to the same ```name``` and ```email.  Secretary name + Secretary email + Secretary mobileSecretary contact info.  Different amount of nulls for each row. Name has mixed letter case. Mobile in incorrect dtype.    Nulls:  Secretary name      37421  Secretary email     37443  Secretary mobile    37447   No. of unique values:  Secretary name: 63280  Secretary email: 73872  Secretary mobile: 73448<jupyter_code>name = "<NAME>"
email = "Secretary email"
mobile = "Secretary mobile"
df = dataset.loc[:,[name, email, mobile]]
df
# Nulls
print("Nulls:")
print(df.isnull().sum())
df = df.dropna()
# Convert mobile from float64 to int to remove decimals and then to str object
df.loc[:,mobile] = df[mobile].astype(int).astype(str)

# Lowercase names
df.loc[:, name] = df[name].str.lower()
# Unique values
print("No. of unique values:")
for col in df:
    print(col + ":", len(df.loc[:,col].unique()))
df[name].value_counts().rename_axis('Value').reset_index(name='Counts')<jupyter_output><empty_output><jupyter_text>Contains invalid names.<jupyter_code>df[email].value_counts().rename_axis('Value').reset_index(name='Counts')
df[df[email] == "<EMAIL>"].head()<jupyter_output><empty_output><jupyter_text>Same ```email``` most refers to same ```name``` and ```mobile```.<jupyter_code>df[mobile].value_counts().rename_axis('Value').reset_index(name='Counts')
df[df[mobile] == "9938104063"].head()<jupyter_output><empty_output><jupyter_text>Same ```mobile``` most refers to same ```name``` and ```email```.  Asisstant Secretary name + Asisstant Secretary email + Asisstant Secretary mobileAsisstant Secretary contact info.  same amount of nulls for each row. Name has mixed letter case and invalid values. Mobile in incorrect dtype.    Nulls:  Asisstant Secretary name      110892  Asisstant Secretary email     110892  Asisstant Secretary mobile    110892  No. of unique values:  Asisstant Secretary name: 1030  Asisstant Secretary email: 1036  Asisstant Secretary mobile: 1036<jupyter_code>name = "<NAME>"
email = "Asisstant Secretary email"
mobile = "Asisstant Secretary mobile"
df = dataset.loc[:,[name, email, mobile]]
df
# Nulls
print("Nulls:")
print(df.isnull().sum())
df = df.dropna()

# Convert mobile from float64 to int to remove decimals and then to str object
df.loc[:,mobile] = df[mobile].astype(int).astype(str)

# Lowercase names
df.loc[:, name] = df[name].str.lower()

# Unique values
print("No. of unique values:")
for col in df:
    print(col + ":", len(df.loc[:,col].unique()))
df[name].value_counts().rename_axis('Value').reset_index(name='Counts')
df[mobile].value_counts().rename_axis('Value').reset_index(name='Counts')
df[email].value_counts().rename_axis('Value').reset_index(name='Counts')<jupyter_output><empty_output><jupyter_text>Board Member name + Board Member email + Board Member mobileBoard Member contact info.  Same amount of nulls for each row. Name has mixed letter case and invalid values. Mobile in incorrect dtype.    Nulls:  Board Member name      106928  Board Member email     106928  Board Member mobile    106928  No. of unique values:  Board Member name: 4854  Board Member email: 4961  Board Member mobile: 4961<jupyter_code>prefix = "Board Member "
name = prefix + "name"
email = prefix + "email"
mobile = prefix + "mobile"
df = dataset.loc[:,[name, email, mobile]]
df
# Nulls
print("Nulls:")
print(df.isnull().sum())
df = df.dropna()

# Convert mobile from float64 to int to remove decimals and then to str object
df.loc[:,mobile] = df[mobile].astype(int).astype(str)

# Lowercase names
df.loc[:, name] = df[name].str.lower()

# Unique values
print("No. of unique values:")
for col in df:
    print(col + ":", len(df.loc[:,col].unique()))
df[name].value_counts().rename_axis('Value').reset_index(name='Counts')
df[email].value_counts().rename_axis('Value').reset_index(name='Counts')
df[mobile].value_counts().rename_axis('Value').reset_index(name='Counts')<jupyter_output><empty_output><jupyter_text>Vice Chairman name + Vice Chairman email + Vice Chairman mobileVice Chairman contact info.  Different amount of nulls for each row. Name has mixed letter case and invalid values. Mobile in incorrect dtype.    Nulls:  Vice Chairman name      106478  Vice Chairman email     106481  Vice Chairman mobile    106480  No. of unique values:Vice Chairman name: 5238  Vice Chairman email: 5423  Vice Chairman mobile: 5404<jupyter_code>prefix = "Vice Chairman "
name = prefix + "name"
email = prefix + "email"
mobile = prefix + "mobile"
df = dataset.loc[:,[name, email, mobile]]
df
# Nulls
print("Nulls:")
print(df.isnull().sum())
df = df.dropna()

# Convert mobile from float64 to int to remove decimals and then to str object
df.loc[:,mobile] = df[mobile].astype(int).astype(str)

# Lowercase names
df.loc[:, name] = df[name].str.lower()

# Unique values
print("No. of unique values:")
for col in df:
    print(col + ":", len(df.loc[:,col].unique()))
df[name].value_counts().rename_axis('Value').reset_index(name='Counts')
df[email].value_counts().rename_axis('Value').reset_index(name='Counts')
df[mobile].value_counts().rename_axis('Value').reset_index(name='Counts')<jupyter_output><empty_output><jupyter_text>Member name + Member email + Member mobileMember contact info.  Different amount of nulls for each row. Name has mixed letter case and invalid values. Mobile in incorrect dtype.    Nulls:  Member name      82357  Member email     82371  Member mobile    82369  No. of unique values:  Member name: 26538  Member email: 29397  Member mobile: 29330<jupyter_code>prefix = "Member "
name = prefix + "name"
email = prefix + "email"
mobile = prefix + "mobile"
df = dataset.loc[:,[name, email, mobile]]
df
# Nulls
print("Nulls:")
print(df.isnull().sum())
df = df.dropna()

# Convert mobile from float64 to int to remove decimals and then to str object
df.loc[:,mobile] = df[mobile].astype(int).astype(str)

# Lowercase names
df.loc[:, name] = df[name].str.lower()

# Unique values
print("No. of unique values:")
for col in df:
    print(col + ":", len(df.loc[:,col].unique()))
df[name].value_counts().rename_axis('Value').reset_index(name='Counts')
df[email].value_counts().rename_axis('Value').reset_index(name='Counts')
df[mobile].value_counts().rename_axis('Value').reset_index(name='Counts')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14122729623657548, 'fraction_numerical': 0.026742523350269252, 'mean_word_length': 5.191373139951026, 'pattern_counts': {'":': 10, '<': 170, '<?xml version=': 0, '>': 182, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 5, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '44', 'dir': 'jupyter-structured-clean-dedup', 'id': '242967', 'license': 'MIT', 'n_tokens_mistral': 11232, 'n_tokens_neox': 10532, 'n_words': 2885, 'path': '[DIR] Darpan21FCRA/42621 Final_Data_ngodarpan.gov.in/42621 Final_Data_ngodarpan.gov.in.ipynb', 'repo_name': 'GeLi2001/Codebook', 'size': '32949'}