<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
import math 
client = boto3.client('s3')
resource = boto3.resource('s3')
my_bucket = resource.Bucket('daanmatchdatafiles')<jupyter_output><empty_output><jupyter_text>CSR 2016_2017.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 1](5.1)        * [S.No.](5.1.1)        * [Company Name(s)](5.1.2)        * [Amount (Actuals)](5.1.3)    * [Sheet 2](5.2)        * [Development Sectors](5.2.1)        * [Amount Spent FY 2014-15 (INR Cr.)](5.2.2)        * [Amount Spent FY 2015-16 (INR Cr.)](5.2.3)        * [Amount Spent FY 2016-17 (INR Cr.)](5.2.4)* [Problems with the data](6) **About this dataset**    Data provided by: National CSR Portal.         Source: https://csr.gov.in/         Type: xlsx  Last Modified: May 29, 2021, 19:52:24 (UTC-07:00)  Size: 487.4 KB<jupyter_code>path = "s3://daanmatchdatafiles/DaanMatch_DataFiles/CSR 2016_2017.xlsx"
CSR_2016_2017 = pd.ExcelFile(path)
print(CSR_2016_2017.sheet_names)
# Show dataframe 1
CSR_2016_2017_1 = CSR_2016_2017.parse('Sheet1')
CSR_2016_2017_1.head()
# Show dataframe 2
CSR_2016_2017_2 = CSR_2016_2017.parse('Sheet2')
CSR_2016_2017_2.head()
# Show dataframe 3
CSR_2016_2017_3 = CSR_2016_2017.parse('Sheet3')
CSR_2016_2017_3.head()<jupyter_output><empty_output><jupyter_text>Sheet 3 is empty. **Cleaned datasets**   Sheet 1 Cleaning up the data in sheet 1:<jupyter_code>CSR_2016_2017_1.head()
as_prescribed = CSR_2016_2017_1.copy()
as_prescribed.rename(columns = {'Unnamed: 1' : 'S.No.', 'Unnamed: 2' : 'Company Name(s)', 'Unnamed: 3' : 'Amount (Actuals)'}, inplace = True)
as_prescribed = as_prescribed.iloc[4:401, 1:4]
as_prescribed = as_prescribed.reset_index(drop = True)
as_prescribed
less_prescribed = CSR_2016_2017_1.copy()
less_prescribed.rename(columns = {'Unnamed: 5' : 'S.No.', 'Unnamed: 6' : 'Company Name(s)', 'Unnamed: 7' : 'Amount (Actuals)'}, inplace = True)
less_prescribed = less_prescribed.iloc[4:, 5:8]
less_prescribed = less_prescribed.reset_index(drop = True)
less_prescribed
more_prescribed = CSR_2016_2017_1.copy()
more_prescribed.rename(columns = {'Unnamed: 9' : 'S.No.', 'Unnamed: 10' : 'Company Name(s)', 'Unnamed: 11' : 'Amount (Actuals)'}, inplace = True)
more_prescribed = more_prescribed.iloc[4:3732, 9:12]
more_prescribed = more_prescribed.reset_index(drop = True)
more_prescribed<jupyter_output><empty_output><jupyter_text>Sheet 2 Cleaning up the data in sheet 2:<jupyter_code>CSR_2016_2017_2.head()
sheet_2 = CSR_2016_2017_2.copy()
sheet_2 = sheet_2.rename(columns = {'Unnamed: 2' : 'Development Sectors', 'Unnamed: 3' : 'Amount Spent FY 2014-15 (INR Cr.)', 'Unnamed: 4' : 'Amount Spent FY 2015-16 (INR Cr.)', 'Unnamed: 5' : 'Amount Spent FY 2016-17 (INR Cr.)'})
sheet_2 = sheet_2.iloc[3:, 2:]
sheet_2 = sheet_2.reset_index(drop = True)
sheet_2['Development Sectors'] = sheet_2['Development Sectors'].fillna('Grand Total (in Cr.)')
sheet_2<jupyter_output><empty_output><jupyter_text>**What's in this dataset?**<jupyter_code>print("Sheet 1:")
print('\n')
print("As Prescribed:")
print("Shape:", as_prescribed.shape)
print("Rows:", as_prescribed.shape[0])
print("Columns:", as_prescribed.shape[1])
print("Each row is a company who spent exactly 2% of their earnings on CSR (Corporate Social Responsibility).")
print('\n')
print("Less Than Prescribed:")
print("Shape:", less_prescribed.shape)
print("Rows:", less_prescribed.shape[0])
print("Columns:", less_prescribed.shape[1])
print("Each row is a company who spent less than 2% of their earnings on CSR (Corporate Social Responsibility).")
print('\n')
print("More Than Prescribed:")
print("Shape:", more_prescribed.shape)
print("Rows:", more_prescribed.shape[0])
print("Columns:", more_prescribed.shape[1])
print("Each row is a company who spent more than 2% of their earnings on CSR (Corporate Social Responsibility).")

print('\n')
print('\n')
print("Sheet 2:")
print("Shape:", sheet_2.shape)
print("Rows:", sheet_2.shape[0])
print("Columns:", sheet_2.shape[1])
print("Each row is a development sector of CSR and how much was spent on that sector each year.")<jupyter_output>Sheet 1:


As Prescribed:
Shape: (397, 3)
Rows: 397
Columns: 3
Each row is a company who spent exactly 2% of their earnings on CSR (Corporate Social Responsibility).


Less Than Prescribed:
Shape: (6813, 3)
Rows: 6813
Columns: 3
Each row is a company who spent less than 2% of their earnings on CSR (Corporate Social Responsibility).


More Than Prescribed:
Shape: (3728, 3)
Rows: 3728
Columns: 3
Each row is a company who spent more than 2% of their earnings on CSR (Corporate Social Responsibility).




Sheet 2:
Shape: (15, 4)
Rows: 15
Columns: 4
Each row is a development sector of CSR and how much was spent on that sector each year.<jupyter_text>So what does this mean? In India, every company is supposed to spend 2% of their earnings on Corporate Social Responsibility (CSR). The first sheet is showing every company that spent exactly 2%, less than 2%, and more than 2% on CSR in the year 2016-2017. The total amounts shown are in INR (Indian Rupees). The second sheet is showing how these CSR donations are divided up each year. There are several different sectors of where donations go, and this sheet shows how much money is donated to each sector over the span of three years. The total amounts are shown in Cr (Crores).  **Codebook**<jupyter_code>print("Codebook for As Prescribed, Less than Prescribed, and More than Prescribed:")
as_prescribed_columns = [column for column in as_prescribed.columns]
as_prescribed_description = ["Counting how many companies in the DataFrame. The ordering is based on how much each company spent on CSR in 2016-17 in relation to the other companies in the DataFrame.",
                            "The name of the company.",
                            "Amount of money (in Rupees) spent on CSR in 2016-17. Ordered by decreasing amount."]
as_prescribed_dtypes = [dtype for dtype in as_prescribed.dtypes]

data_1 = {"Column Name": as_prescribed_columns, "Description": as_prescribed_description, "Type": as_prescribed_dtypes}
as_prescribed_codebook = pd.DataFrame(data_1)
as_prescribed_codebook.style.set_properties(subset=['Description'], **{'width': '600px'})
print("Codebook for Sheet 2:")
sheet_2_columns = [column for column in sheet_2.columns]
sheet_2_description = ["The different sectors that CSR donations are divided into.", 
                       "Amount of money (in Cr.) spent in total in each sector in 2014-15.", 
                       "Amount of money (in Cr.) spent in total in each sector in 2015-16.", 
                       "Amount of money (in Cr.) spent in total in each sector in 2016-17."]
sheet_2_dtypes = [dtype for dtype in sheet_2.dtypes]

data_2 = {"Column Name": sheet_2_columns, "Description": sheet_2_description, "Type": sheet_2_dtypes}
sheet_2_codebook = pd.DataFrame(data_2)
sheet_2_codebook.style.set_properties(subset=['Description'], **{'width': '600px'})<jupyter_output>Codebook for Sheet 2:<jupyter_text>**Missing values**<jupyter_code>as_prescribed.isnull().sum()
less_prescribed.isnull().sum()
more_prescribed.isnull().sum()
sheet_2.isnull().sum()
less_prescribed.loc[less_prescribed['Company Name(s)'].isnull()]<jupyter_output><empty_output><jupyter_text>The 1 missing value in the "Company Name(s)" column in the As Prescribed and More than Prescribed DataFrames are in the last row, which is summing the totals. In the Less than Prescribed DataFrame, however, there are 64 NaN values in the "Company Name(s)" column. As we can see above, 1 of those is the total row and the other 63 are rows where the "Amount (Actuals)" value is 0. We can remove these rows, except for the total row, as they do not provide any information and are most likely mistakes.<jupyter_code>#Dropping null rows from less_prescribed
less_prescribed = less_prescribed.dropna()
total_row = {'S.No.' : 'Total', 'Amount (Actuals)' : 39931700000}
less_prescribed = less_prescribed.append(total_row, ignore_index = True)
less_prescribed = less_prescribed.reset_index(drop = True)
less_prescribed
#Checking to make sure we only have 1 NaN value now
less_prescribed.isnull().sum()<jupyter_output><empty_output><jupyter_text>**Summary statistics**<jupyter_code>#Changing the spending columns into type int
as_prescribed["Amount (Actuals)"] = as_prescribed["Amount (Actuals)"].astype(str).astype(int)
less_prescribed["Amount (Actuals)"] = less_prescribed["Amount (Actuals)"].astype(str).astype(int)
more_prescribed["Amount (Actuals)"] = more_prescribed["Amount (Actuals)"].astype(str).astype(int)
sheet_2['Amount Spent FY 2014-15 (INR Cr.)'] = sheet_2['Amount Spent FY 2014-15 (INR Cr.)'].astype(str).astype(float)
sheet_2['Amount Spent FY 2015-16 (INR Cr.)'] = sheet_2['Amount Spent FY 2015-16 (INR Cr.)'].astype(str).astype(float)
sheet_2['Amount Spent FY 2016-17 (INR Cr.)'] = sheet_2['Amount Spent FY 2016-17 (INR Cr.)'].astype(str).astype(float)
as_prescribed.describe()
less_prescribed.describe()
more_prescribed.describe()
sheet_2.describe()
#Combining the 3 prescribed DataFrames into 1
total_companies = as_prescribed.append(less_prescribed, ignore_index = True).append(more_prescribed, ignore_index = True)
total_companies = total_companies.dropna()
total_companies = total_companies.reset_index(drop = True)
total_companies<jupyter_output><empty_output><jupyter_text>Columns  Sheet 1Sheet 1 contains 3 DataFrames: As Prescribed, Less than Prescribed, and More than Prescribed. They all contain the same 3 columns.   S.No.Counting how many companies in the DataFrame. The ordering is based on how much each company spent on CSR in 2016-17 in relation to the other companies in the DataFrame.  As Prescribed DataFrame:<jupyter_code>column = as_prescribed["S.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)<jupyter_output>No. of unique values: 397
Duplicates: {}<jupyter_text>Less Than Prescribed DataFrame:<jupyter_code>column = less_prescribed["S.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)<jupyter_output>No. of unique values: 6749
Duplicates: {}<jupyter_text>More Than Prescribed DataFrame:<jupyter_code>column = more_prescribed["S.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)<jupyter_output>No. of unique values: 3728
Duplicates: {}<jupyter_text>Company Name(s)The name of the company.  As Prescribed DataFrame:<jupyter_code>column = as_prescribed['Company Name(s)']
as_companies = np.array(column)
column
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("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))<jupyter_output>No. of unique values: 397
Duplicates: {}<jupyter_text>Less Than Prescribed DataFrame:<jupyter_code>column = less_prescribed['Company Name(s)']
less_companies = np.array(column)
column
print("No. of unique values:", len(column.unique()))

# Check for duplicates
counter = dict(Counter(column))
less_duplicates = { key:value for key, value in counter.items() if value > 1}
print("Duplicates:", less_duplicates)
if len(less_duplicates) > 0:
    print("No. of duplicates:", len(less_duplicates))
less_duplicates
less_prescribed[less_prescribed['Company Name(s)'].isin(less_duplicates)].sort_values('Company Name(s)')<jupyter_output><empty_output><jupyter_text>We can see above that of the 15 companies repeated twice in the less_prescribed DataFrame, 7 of them have different values in the "Amount (Actuals)" column. Therefore, 8 companies were added twice with the same value in the "Amount (Actuals)" column, and 7 were added twice with completely different values. This is an error in the dataset.  More Than Prescribed DataFrame:<jupyter_code>column = more_prescribed['Company Name(s)']
more_companies = np.array(column)
column
print("No. of unique values:", len(column.unique()))

# Check for duplicates
counter = dict(Counter(column))
more_duplicates = { key:value for key, value in counter.items() if value > 1}
print("Duplicates:", more_duplicates)
if len(more_duplicates) > 0:
    print("No. of duplicates:", len(more_duplicates))
more_duplicates
more_prescribed[more_prescribed['Company Name(s)'].isin(more_duplicates)].sort_values('Company Name(s)')<jupyter_output><empty_output><jupyter_text>We can see above that of the 10 companies repeated twice in the more_prescribed DataFrame, 9 of them have different values in the "Amount (Actuals)" column. Therefore, 1 company was added twice with the same value in the "Amount (Actuals)" column, and 9 were added twice with completely different values. This is an error in the dataset.  Total Companies DataFrame:<jupyter_code>column = total_companies['Company Name(s)']
all_companies = np.array(column)
column
print("No. of unique values:", len(column.unique()))

# Check for duplicates
counter = dict(Counter(column))
all_duplicates = { key:value for key, value in counter.items() if value > 1}
print("Duplicates:", all_duplicates)
if len(all_duplicates) > 0:
    print("No. of duplicates:", len(all_duplicates))
all_duplicates
total_companies[total_companies['Company Name(s)'].isin(all_duplicates)].sort_values('Company Name(s)')
#Making a DataFrame for companies that are repeated in 2 different DataFrames
dups_3 = all_duplicates.keys()
dups_4 = []

for dup in dups_3:
    if (not dup in(less_duplicates)):
        if (not dup in (more_duplicates)):
            dups_4.append(dup)

as_repeats = as_prescribed[as_prescribed['Company Name(s)'].isin(dups_4)].sort_values('Company Name(s)')
as_repeats['DataFrame'] = 'as_prescribed'

less_repeats = less_prescribed[less_prescribed['Company Name(s)'].isin(dups_4)].sort_values('Company Name(s)')
less_repeats['DataFrame'] = 'less_prescribed'

more_repeats = more_prescribed[more_prescribed['Company Name(s)'].isin(dups_4)].sort_values('Company Name(s)')
more_repeats['DataFrame'] = 'more_prescribed'

mixed_repeats = as_repeats.append(less_repeats, ignore_index = True).append(more_repeats, ignore_index = True)
mixed_repeats.sort_values('Company Name(s)')
plt.figure(figsize = (8, 6))
df_chart = mixed_repeats.groupby('DataFrame').agg(len)
plt.bar(df_chart.index, df_chart["S.No."])
plt.title("Companies in each DataFrame that are Repeated Elsewhere")
plt.xlabel("Original DataFrame")
plt.ylabel('Number of Companies that are Repeated in Other DataFrames')
plt.xticks(rotation = 20);<jupyter_output><empty_output><jupyter_text>As you can see above, there are 15 companies whose names are repeated in 2 different datasets. Therefore, those 15 companies each appear twice in total throughout the as_prescribed, less_prescribed, and more_prescribed DataFrames. 13 companies in the more_prescribed DataFrame, 10 companies in the less_prescribed DataFrame, and 7 companies in the as_prescribed DataFrame appear in another DataFrame. This means that the data for those 15 companies is innacurate and is an error in the data.  Amount (Actuals)Amount of money (in Rupees) spent on CSR in 2016-17. Ordered by decreasing amount.  As Prescribed DataFrame:<jupyter_code>column = as_prescribed['Amount (Actuals)']
column
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("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))
as_prescribed.groupby('Amount (Actuals)').agg(len).sort_values('S.No.', ascending = False)
bins= np.linspace(0, 250000000, 10)
plt.figure(figsize = (6, 5))
plt.hist(as_prescribed['Amount (Actuals)'], bins=bins, edgecolor="k")
plt.xticks(bins);

plt.show()
#Mean:
as_mean = np.mean(np.array(column))
as_mean<jupyter_output><empty_output><jupyter_text>Less Than Prescribed DataFrame:<jupyter_code>column = less_prescribed['Amount (Actuals)']
column
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("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))
less_prescribed.groupby('Amount (Actuals)').agg(len).sort_values('S.No.', ascending = False)
bins= np.linspace(0, 40000000, 7)
plt.figure(figsize = (6, 5))
plt.hist(less_prescribed['Amount (Actuals)'], bins=bins, edgecolor="k")
plt.xticks(bins);

plt.show()
#Mean:
less_mean = np.mean(np.array(column))
less_mean<jupyter_output><empty_output><jupyter_text>More Than Prescribed DataFrame:<jupyter_code>column = more_prescribed['Amount (Actuals)']
column
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("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))
more_prescribed.groupby('Amount (Actuals)').agg(len).sort_values('S.No.', ascending = False)
bins= np.linspace(0, 50000000, 7)
plt.figure(figsize = (6, 5))
plt.hist(more_prescribed['Amount (Actuals)'], bins=bins, edgecolor="k")
plt.xticks(bins);

plt.show()
#Mean:
more_mean = np.mean(np.array(column))
more_mean<jupyter_output><empty_output><jupyter_text>Means Visualization:<jupyter_code>means_df = pd.DataFrame(data = {'DataFrame' : ['As Prescribed', 'Less than Prescribed', 'More than Prescribed'], 
                               'Mean' : [as_mean, less_mean, more_mean]})
plt.figure(figsize = (8, 6))
plt.bar(means_df['DataFrame'], means_df['Mean'])
plt.title("Mean Amount from each DataFrame (in INR)")
plt.xlabel("DataFrame")
plt.ylabel('Mean Amount')
plt.xticks(rotation = 20);<jupyter_output><empty_output><jupyter_text>Sheet 2Sheet 2 contains information on how much money was spent towards each Development Sector of CSR in the years 2014-15, 2015-16, and 2016-17.  Development SectorsThe different sectors that CSR donations are divided into.<jupyter_code>column = sheet_2['Development Sectors']
column
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("Duplicates:", duplicates)
if len(duplicates) > 0:
    print("No. of duplicates:", len(duplicates))<jupyter_output>No. of unique values: 15
Duplicates: {}<jupyter_text>Amount Spent FY 2014-15 (INR Cr.)Amount of money (in Cr.) spent in total in each sector in 2014-15.<jupyter_code>column = sheet_2['Amount Spent FY 2014-15 (INR Cr.)']
column
#Making sure first 14 rows sum to last value
total_14 = np.sum(np.array(column)[0:14])
math.isclose(total_14, column[14], abs_tol = 0.5)<jupyter_output><empty_output><jupyter_text>Amount Spent FY 2015-16 (INR Cr.)Amount of money (in Cr.) spent in total in each sector in 2015-16.<jupyter_code>column = sheet_2['Amount Spent FY 2015-16 (INR Cr.)']
column
#Making sure first 14 rows sum to last value
total_15 = np.sum(np.array(column)[0:14])
math.isclose(total_15, column[14], abs_tol = 0.5)<jupyter_output><empty_output><jupyter_text>Amount Spent FY 2016-17 (INR Cr.)Amount of money (in Cr.) spent in total in each sector in 2016-17.<jupyter_code>column = sheet_2['Amount Spent FY 2016-17 (INR Cr.)']
column
#Making sure first 14 rows sum to last value
total_16 = np.sum(np.array(column)[0:14])
math.isclose(total_16, column[14], abs_tol = 0.5)<jupyter_output><empty_output><jupyter_text>Sheet 2 Visualization:<jupyter_code>yearly_df = pd.DataFrame(data = {'Year' : ['2014-15', '2015-16', '2016-17'], 
                               'Total Spent' : [total_14, total_15, total_16]})
plt.figure(figsize = (8, 6))
plt.bar(yearly_df['Year'], yearly_df['Total Spent'])
plt.title("Total Amount Spent each Year on CSR (in Cr. INR)")
plt.xlabel("Fiscal Year")
plt.ylabel('Total Spent (Cr. INR)')
plt.xticks(rotation = 20);<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1280543640657381, 'fraction_numerical': 0.037351197648079426, 'mean_word_length': 5.9537533512064345, 'pattern_counts': {'":': 6, '<': 95, '<?xml version=': 0, '>': 114, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 3, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '25', 'dir': 'jupyter-structured-clean-dedup', 'id': '397762', 'license': 'MIT', 'n_tokens_mistral': 7706, 'n_tokens_neox': 6828, 'n_words': 2002, 'path': 'CSR 2016_2017/.ipynb_checkpoints/CSR 2016_2017-checkpoint.ipynb', 'repo_name': 'GeLi2001/Codebook', 'size': '20755'}