<jupyter_start><jupyter_text><jupyter_code># Import whatever packages you need here
import pandas as pd
import numpy as np
from google.colab import drive
drive.mount('/content/drive')<jupyter_output>Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount("/content/drive", force_remount=True).<jupyter_text>Data cleaning points to ask Chris at OH1.   income_clean() --> why is there a SettingWithCopyWarning2.   clean_depr() --> why does the clean_depr() function change the value of the global variable depr?<jupyter_code># Data structures that are likely to be reused

borough_names = ['City of London',
     'Barking and Dagenham',
     'Barnet',
     'Bexley',
     'Brent',
     'Bromley',
     'Camden',
     'Croydon',
     'Ealing',
     'Enfield',
     'Greenwich',
     'Hackney',
     'Hammersmith and Fulham',
     'Haringey',
     'Harrow',
     'Havering',
     'Hillingdon',
     'Hounslow',
     'Islington',
     'Kensington and Chelsea',
     'Kingston-upon-Thames',
     'Lambeth',
     'Lewisham',
     'Merton',
     'Newham',
     'Redbridge',
     'Richmond-upon-Thames',
     'Southwark',
     'Sutton',
     'Tower Hamlets',
     'Waltham Forest',
     'Wandsworth',
     'Westminster']
# MATT'S CODE

# Reading in the income dataset
income_path = "/content/drive/MyDrive/The One Percent/Datasets/income-of-tax-payers (1).xlsx"
income = pd.read_excel(income_path, 1, header=1)

# Function for cleaning the income dataset
def income_clean(df, individuals=True, mean=True, median=True):

    # Filtering rows by whether the row corresponds to a London borough
    df = df[df['Area'].isin(borough_names)]

    # Keeping only relevant columns
    df = df[['Area', 'Number of Individuals.17', 'Mean £.17', 'Median £.17']]
            
    # Renaming row and column names
    df.columns = ['Borough', 'Number of Individuals', 'Mean £', 'Median £']
    df = df.set_index('Borough')
    
    # Adding some modularity to choose which columns to keep
    if mean == False:
        df.drop(columns='Mean £', inplace=True)    
    if median == False:
        df.drop(columns='Median £', inplace=True)
    if individuals == False:
        df.drop(columns='Number of Individuals', inplace=True)
    
    return df

# Reading in the vaccine dataset
vaccine_path = "/content/drive/MyDrive/The One Percent/Datasets/nhse_weekly_vaccines_london_stp.csv"
vaccine = pd.read_csv(vaccine_path)

# Function for cleaning the vaccine dataset
def vaccine_clean(df, first=True, second=True):

    # Filtering data by relevant variable values
    most_recent = df['end_date'] == '2021-02-14'
    eighty_plus = df['age_band'] == '80+'
    df = df.loc[most_recent & eighty_plus]
    
    # Setting the index of the dataframe to boroughs and manipulating strings
    df = df.set_index('boroughs')
    df.index = df.index.str.replace('Hackney and City of London', 'Hackney, City of London')

    # Creating a new dataframe to be imputed with values
    df1 = pd.DataFrame(np.nan, index=borough_names, columns=['1st dose', '2nd dose'])
    df1.index = df1.index.str.replace('-', ' ')
    
    # Imputing values from df to new dataframe, df1
    first_dose = df['dose'] == '1st dose'
    second_dose = df['dose'] == '2nd dose'
    
    for borough in df1.index:
        for boroughs in df.index:
            if borough in boroughs.split(', '):
                df1.loc[borough, '1st dose'] = df[first_dose].loc[boroughs, 'percent_vaccine']
                df1.loc[borough, '2nd dose'] = df[second_dose].loc[boroughs, 'percent_vaccine']
    
    # Adding some modularity to choose which columns to keep
    if first == False:
        df1.drop(columns='1st dose', inplace=True)
    if second == False:
        df1.drop(columns='2nd dose', inplace=True)
    
    return df1

#income_clean(income)
vaccine_clean(vaccine)
vaccine
# SHAOKUN'S CODE 1
#WORKPLACE DATA
# load excel spreadsheet into python
df = pd.read_csv('https://data.london.gov.uk/download/google-mobility-by-borough/26d5821b-fcb6-4aae-af73-ee0596942d16/google_activity_by_London_Borough.csv', parse_dates= ['date'])
#delete all the data columns except workplace % change
df=df.drop(['area_code','retail_and_recreation_percent_change_from_baseline','grocery_and_pharmacy_percent_change_from_baseline','parks_percent_change_from_baseline','transit_stations_percent_change_from_baseline','residential_percent_change_from_baseline'], axis=1)

# selecting dates only from first national lockdown onwards (16th March) to most recent data
df = df.set_index(['date'])
df=df.loc['2020-3-16':'2021-2-16']
#greater than the start date and smaller than the end date
#mask = (workplace['date'] > '16/03/2020'), (workplace['date'] <= '2021.2.16')


#group data by area_name then find mean change for each area
total=df['workplaces_percent_change_from_baseline'].groupby(df['area_name'])
borough_mean=total.mean()
#create a new dataframe to store the mean change for each area
cleaned_workplace=borough_mean.to_frame(name='Mean Workplace % Change').reset_index()

#cleaned2 = pd.merge(df2,cleaned_workplace,on ='area_name',how ='inner') 
#cleaned_workplace= cleaned_workplace.set_index('area_name')

cleaned_workplace.to_csv('Workplace Mobility')
cleaned_workplace.set_index(['area_name'])

#COVID DEATHS DATA
#load data into python
df2_path = "/content/drive/MyDrive/The One Percent/Datasets/deaths by covid 19.xlsx"

df2 = pd.read_excel(df2_path)
# drop the first two rows of data

# rename the columns 
#new_header = df2. iloc[2]
#df2 = df2[3:]
#df2. columns = new_header

# Choose rows that have the geography type= local authority
df2=df2[df2['Unnamed: 1'] == 'Local Authority']

# Choose rows that have cause of death= Covid19
df2=df2[df2['Unnamed: 3'] == 'COVID 19']
df2
# Aggregate total no. of deaths by area
total=df2['Unnamed: 6'].groupby(df2["Unnamed: 2"])
#Find the sum of deaths
deaths=total.sum()

#make a data frame of the deaths by area
cleaned_deaths=deaths.to_frame(name='Number of deaths').reset_index()
#Rename the column to area name
cleaned_deaths = cleaned_deaths.rename(columns={'Unnamed: 2': 'area_name'})

#import data on population
population_path = "/content/drive/MyDrive/The One Percent/Datasets/Population by nborough.xlsx"

population_df=pd.read_excel(population_path)
population_df.set_index('area_name')

#Set the index to area name 
cleaned_deaths.set_index('area_name')

#cleaned_deaths.head(n=20)

#Merge the two datasets together to remove the no. of deaths in regions outside London
merged=pd.merge(cleaned_workplace, cleaned_deaths, how='inner')

# #divide no. of deaths by population
#df3=pd.DataFrame(population_df['(estimated 2021) (thousands)'])
merged=pd.merge(merged, population_df, how='inner')
# Scale the number of deaths by population and create a new column 'no. deaths per thousand'
merged['Number of deaths per thousand'] = merged['Number of deaths']/merged['Population (estimated 2021) (thousands)']
#Drop the number of deaths column and population column
merged=merged.drop(['Number of deaths'], axis=1)
merged=merged.drop(['Population (estimated 2021) (thousands)'], axis=1)

#renamed the dataframe and save it as a CSV
cleaned_workplace_deaths=merged
cleaned_workplace_deaths.to_csv=('Cleaned data on workplace mobility and COVID deaths by borough')
cleaned_workplace_deaths = cleaned_workplace_deaths.set_index('area_name')
cleaned_workplace_deaths








# LEWIS' CODE
#Comorbidities / underlying health data:
#Source: London Datastore (data.london.gov.uk) 2012-2013

#Reading in each dataframe from the file, number parameter corresponds to sheet index number
file = pd.ExcelFile('https://data.london.gov.uk/download/better-health-for-london--one-year-on/ad22d1dd-838a-4565-adf3-9d8289841b8c/Better-health-london-ambitions-data.xlsx')
inactive = pd.read_excel(file, 3)
excess_weight = pd.read_excel(file, 4)
smoking = pd.read_excel(file, 6)
#All dataframes in same format, so we can use single function:
def clean_com(df):
    #Keeping only the most recent year of available data
    years = df['Time Period'].unique()
    most_recent = max(years)
    bool_recent = df['Time Period'] == most_recent
    df = df[bool_recent]
    
    #Getting rid of London and England averages at the bottom
    df = df.head(33)
    
    #Keeping only the relevant columns
    keep_cols = ['Area Name', 'Value']
    for col in df:
        if col not in keep_cols:
            df.drop(col, axis = 1, inplace = True)
    
    #Changing the row names from numbers to area names
    row_names = list(df['Area Name'])
    df.index = row_names
    
    #Dropping the now obslete area name column
    df.drop('Area Name', axis = 1, inplace = True)
    return df


#Adding data from new file on asthma hospitalisations (can't use function as format different)
#Source: PHE (fingertips.phe.org.uk) 2018-2019

#Loading in the file
path = '/content/drive/MyDrive/The One Percent/Datasets/indicators-CountyUApre419.data.csv'

asthma = pd.read_csv(path)
#Keeping the relevant rows only
rows_keep = list(range(1329,1362))
asthma = asthma.loc[rows_keep,:]

#Keeping the relevant columns
keep_cols = ['Value', 'Area Name']
for col in asthma:
  if col not in keep_cols:
    asthma.drop(col, axis = 1, inplace = True)

#Changing row names from numbers to area names
row_names = list(asthma['Area Name'])
asthma.index = row_names
asthma.drop('Area Name', axis = 1, inplace = True)

#Importing file for population
path2 = '/content/drive/MyDrive/The One Percent/Datasets/Population by nborough.xlsx'
popul = pd.read_excel(path2)

#Indexing by area name
pop_row_names = list(popul['area_name'])
popul.index = pop_row_names

#Removing now duplicate area name column
popul.drop('area_name', axis = 1, inplace = True)

#Joining population onto asthma dataframe
asthma = pd.concat([asthma, popul], axis = 1)


#Defining scaled values
asthma['Asthma hospitalisations per 1000'] = asthma['Value'] / asthma['Population (estimated 2021) (thousands)']

#Dropping ingredients
asthma.drop(['Population (estimated 2021) (thousands)', 'Value'], axis = 1, inplace = True)


#Joining together the data
comorb = pd.concat([clean_com(inactive), clean_com(excess_weight), clean_com(smoking), asthma], axis = 1)

#Renaming the columns to reflect the data
col_names = ['Inactivity %', 'Excess Weight %', 'Smoker %', 'Asthma Hospitalisations per 1000' ]
comorb.columns = col_names


#-----------------------------------------------------------------------------#

# Age data
#Source: London Datastore (data.london.gov.uk) 2015

#Reading in the file, selecting sheet zero
path = 'https://data.london.gov.uk/dataset/london-borough-profiles/resource/445fbb06-8945-499b-b1e6-1933afa3c642/download'
atlas = pd.ExcelFile(path)
age = pd.read_excel(atlas, 0)

#Trimming off the irrelevant first row
age = age[1:]

#Columns are currently named 'Unnamed: 0' etc, real column names are in the first row
#first row is index 1 due to trim above. Changing column names:
age.columns = list(age.loc[1,:])

#Trimming off first row which is now duplicate of column names
age = (age[1:])
#Keeping relevant columns for data and excluding rows unrelated to london boroughs
keep_cols = [1] + list(range(18,22))
age = age.iloc[:33,keep_cols]

#Changing row names from numbers to area names
row_names = list(age['Area name'])
age.index = row_names

#Dropping area name column as no longer needed
age.drop('Area name', axis = 1, inplace = True)


#Adding columns for age group percentages
age['% of 0-15'] = 100 * age['Aged 0-15 (2015)'] / age[2015.0]
age['% of 16-64'] = 100 * age['Aged 16-64 (2015)'] / age[2015.0]
age['% of 65+'] = 100 * age['Aged 65+ (2015)'] / age[2015.0]

#Dropping columns of raw numbers that are no longer necessary
age.drop(['Aged 0-15 (2015)', 'Aged 16-64 (2015)', 'Aged 65+ (2015)', 2015.0], axis = 1, inplace = True)
age
# MELANIA'S CODE



#ETHNICITY DATA
 
#reading file
ethnicity = pd.read_excel('/content/drive/MyDrive/The One Percent/Datasets/Copy of ethnic-groups-by-borough.xlsx')
 
#removing non-relevant rows
ethnicity = ethnicity.head(34)
 
#removing area code
ethnicity.drop('Code', axis = 1, inplace = True )

#only necessary columns
ethnicity = ethnicity.loc[:, ['Area', 'White', 'Asian', 'Black', 'Mixed/ Other','Total']]
#removing empty rows
ethnicity = ethnicity.dropna() 
ethnicity = ethnicity.replace('-', np.nan)

#turning into percentages
ethnicity['% White'] = ethnicity['White'].astype(float) / ethnicity['Total'].astype(float) * 100
ethnicity['% Asian'] = ethnicity['Asian'].astype(float) / ethnicity['Total'].astype(float) * 100
ethnicity['% Black'] =  ethnicity['Black'].astype(float) / ethnicity['Total'].astype(float) * 100
ethnicity['% Mixed/Other'] = ethnicity['Mixed/ Other'].astype(float) / ethnicity['Total'].astype(float) * 100


#dropping columns without percentages

ethnicity.drop(['White', 'Asian', 'Black', 'Mixed/ Other', 'Total'], axis = 1, inplace = True)
#------------
 
 
 
 
#HOUSEHOLD DATA
 
#read file
household = pd.read_csv('/content/drive/MyDrive/The One Percent/Datasets/Persons per dwelling.csv') 

#removing unnecessary columns
household.drop(['Row #', 'Code'], axis = 1, inplace = True )

#Selecting the most recent data
year = household['Year']
household = household[year == 2017]

#Further removing columns
household = household.loc[:, ['Area', 'Persons_per_dwelling']]

#Leaving only boroughs
household = household.head(33)

household = household.set_index('Area')
ethnicity = ethnicity.set_index('Area')
print(ethnicity)
print(household)

# RAJA'S CODE

# Education Attainment (Achievement of 5 or more A*- C grades at GCSE or equivalent including English and Maths, 2013/14)
# Quality of life (% children living in out-of-work households (2015))
# QOL (Happiness score 2011-14 (out of 10))

path = "/content/drive/MyDrive/The One Percent/Datasets/london-borough-profiles.xlsx"
educ = pd.ExcelFile(path)
educ1 = pd.read_excel(educ, 'Data')

#Select columns of interest from df1
keep_cols = ["Area name",
                        "Achievement of 5 or more A*- C grades at GCSE or equivalent including English and Maths, 2013/14", 
                        "% children living in out-of-work households (2015)",
                        "Happiness score 2011-14 (out of 10)"]
for col in educ1:
    if col not in keep_cols:
      educ1.drop(col, axis = 1, inplace = True)

# Setting the index of the dataframe to boroughs
educ1 = educ1.set_index("Area name")

# Renaming column names
educ1.columns = ['% Merit in GCSE', '% Children in unemployed household', 'Happiness']

# Convert data to float, removing rows and removing NaN values
educ1 = educ1.apply (pd.to_numeric, errors='coerce')
educ1 = educ1.dropna()
educ1 = educ1.drop(educ1.index[33:36])

educ1
# MATT'S CODE 

# Reading in the deprivation dataset
depr = pd.read_excel('/content/drive/MyDrive/The One Percent/Datasets/Income deprivation 2019 for London.xlsx', 1)

def clean_depr(df):
    
    """
    # Make borough_names global
    global borough_names
    """
    # Assign borough_names as borough_names_edited
    borough_names_edited = borough_names

    """
    # Remove unecessary columns
    keep_cols = ['Local Authority District name (2019)', 'Index of Multiple Deprivation (IMD) Score']
    for col in df:
        if col not in keep_cols:
            df.drop(col, axis=1, inplace=True)
    """
    
    # Remove unecessary columns
    df = df[['Local Authority District name (2019)', 'Index of Multiple Deprivation (IMD) Score']]
    
    # Renaming columns
    df.columns = ['Borough', 'IMD Score']
    
    # Replacing '-' with ' ' in borough_names list
    borough_names_edited = [borough.replace('-', ' ') for borough in borough_names_edited]
    
    # Create a new dataframe to have values imputed
    df1 = pd.DataFrame(np.nan, index=borough_names_edited, columns=['IMD Score'])
    
    # Take average of wards in the same borough and impute into df1
    for borough in borough_names_edited:
        df1.loc[borough, 'IMD Score'] = df[df['Borough'] == borough]['IMD Score'].mean()
    """
    return df.info(), df1
    """
    return df1

"""
Need to ask Chris: why does the clean_depr() function change the value of the global variable depr?
"""
# Merging all the datasets together

# Saving dataframes to variables
"""
Variables to choose:

1) Income Inequality - Matthew
2) COVID-19 cases/deaths (dependent variables?) - Shaokun
3) Comorbidities -Lewis
4) Ethnic inequality - Melania
5) Vaccination rates - Matthew
6) Occupation - workplace mobility - Shaokun
7) Persons per household - Melania
8) Age- Lewis
9) Education attainment - Raja
10) Quality of life indicators -Raja
11) Deprivation - Matthew
"""
# Assigning dataframe variables corresponding to numbers from google docs
one = income_clean(income, individuals=False) 
two_and_six = cleaned_workplace_deaths
three = comorb
four = ethnicity
five = vaccine_clean(vaccine)
seven = household
eight = age
nine_and_ten = educ1
#eleven = clean_depr(depr)

# Changing income inequality indices such that e.g 'Kingston-upon-Thames' becomes 'Kingston upon Thames'
one.index = one.index.str.replace('-', ' ') 

# Changing age dataset dtype from object to float
eight = eight.astype(float)

# Create lists containing all datasets (partial_datasets is without Melania's datasets, full_datasets is with)
#partial_datasets = [one, two_and_six, three, five, eight, nine_and_ten]
full_datasets = [one, two_and_six, three, four, five, seven, eight, nine_and_ten, eleven]

# Checking that each dataset has the same number of rows
for dataset in full_datasets:
    print(len(dataset)) # all are 33 in length

# Checking how many columns the final dataset should have
col_count = 0
for dataset in full_datasets:
    col_count += len(dataset.columns)
print(col_count) # final dataset should have 22 columns

# Merging the dataframes together
final = pd.concat(full_datasets, axis=1)

# Checking final dataframe information
final.info() 
# Writing the code to a csv file 
final.to_csv('/content/drive/MyDrive/The One Percent/Datasets/Final_dataset2.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12224070992789794, 'fraction_numerical': 0.025568496949528563, 'mean_word_length': 4.250728013977868, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 11, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '77661', 'license': 'MIT', 'n_tokens_mistral': 6371, 'n_tokens_neox': 5789, 'n_words': 1756, 'path': 'data_cleaning.ipynb', 'repo_name': 'shaokun2000/github-slideshow', 'size': '18030'}