<jupyter_start><jupyter_text>Job Information<jupyter_code>xl = pd.ExcelFile("../static/data/xlsx/Job Information_2021v2.xlsx")
print(xl.sheet_names)  # see all sheet names<jupyter_output>[u'2008', u'2020', u'2008-2020']<jupyter_text>jobs<jupyter_code>job_information_df_2008_2020 = pd.read_excel("../static/data/xlsx/Job Information_2021v2.xlsx", sheetname='2008-2020')
job_information_df_2008_2020[['SOC Code','Job Title', 'Job Family', 'Description']].head()
family_code = [soc_code.split('-')[0] for soc_code in job_information_df_2008_2020['SOC Code']]
job_information_df_2008_2020['Family Code'] = family_code
job_information_df_2008_2020 = job_information_df_2008_2020[['Family Code', 'SOC Code','Job Title', 'Job Family', 'Description']]
job_information_df_2008_2020.columns = ['Family Code', 'Job Code','Job Title', 'Job Family', 'Description']
job_information_df_2008_2020.head()
job_information_df_2008_2020.to_csv("../static/data/csv/jobs.csv", index=False, encoding='utf-8')<jupyter_output><empty_output><jupyter_text>Panel 1<jupyter_code>job_information_df_2008 = pd.read_excel("../static/data/xlsx/Job Information_2021v2.xlsx", sheetname='2008')
job_information_df_2020 = pd.read_excel("../static/data/xlsx/Job Information_2021v2.xlsx", sheetname='2020')
job_information_df_2008_2020 = pd.read_excel("../static/data/xlsx/Job Information_2021v2.xlsx", sheetname='2008-2020')

rank_label = pd.read_excel("../static/data/xlsx/Rank Label.xlsx")
job_information_df_2008['Year'] = '2008'
job_information_df_2020['Year'] = '2020'
job_information_df_2008_2020['Year'] = '2008~2020'
job_information_df_2008['Job Automation Index Rank'] = [None] + job_information_df_2008['Job Automation Index'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2008['Job Automation Index Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2008[['Job Automation Index Rank']], rank_label.rename(columns={"Rank": "Job Automation Index Rank"}), on='Job Automation Index Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2008['Race with the Machine Rank'] = [None] + job_information_df_2008['Race with the Machine'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2008['Race with the Machine Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2008[['Race with the Machine Rank']], rank_label.rename(columns={"Rank": "Race with the Machine Rank"}), on='Race with the Machine Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2008['Race ahead of the Machine Rank'] = [None] + job_information_df_2008['Race ahead of the Machine'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2008['Race ahead of the Machine Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2008[['Race ahead of the Machine Rank']], rank_label.rename(columns={"Rank": "Race ahead of the Machine Rank"}), on='Race ahead of the Machine Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2008['Changing the Course of the Race Rank'] = [None] + job_information_df_2008['Changing the Course of the Race'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2008['Changing the Course of the Race Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2008[['Changing the Course of the Race Rank']], rank_label.rename(columns={"Rank": "Changing the Course of the Race Rank"}), on='Changing the Course of the Race Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2008['Race against the Machine Rank'] = [None] + job_information_df_2008['Race against the Machine'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2008['Race against the Machine Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2008[['Race against the Machine Rank']], rank_label.rename(columns={"Rank": "Race against the Machine Rank"}), on='Race against the Machine Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2008['Running a Different Race Rank'] = [None] + job_information_df_2008['Running a Different Race'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2008['Running a Different Race Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2008[['Running a Different Race Rank']], rank_label.rename(columns={"Rank": "Running a Different Race Rank"}), on='Running a Different Race Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2008['Employment Rank'] = [None] + job_information_df_2008['Employment'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2008['Employment Percentile'] = [None] + pd.merge(job_information_df_2008[['Employment Rank']], rank_label.rename(columns={"Rank": "Employment Rank"}), on='Employment Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2008['Annual Wage Rank'] = [None] + job_information_df_2008['Annual Wage'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2008['Annual Wage Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2008[['Annual Wage Rank']], rank_label.rename(columns={"Rank": "Annual Wage Rank"}), on='Annual Wage Rank', how='left')['Label'].iloc[1:].tolist()
job_information_df_2020['Job Automation Index Rank'] = [None] + job_information_df_2020['Job Automation Index'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2020['Job Automation Index Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2020[['Job Automation Index Rank']], rank_label.rename(columns={"Rank": "Job Automation Index Rank"}), on='Job Automation Index Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2020['Race with the Machine Rank'] = [None] + job_information_df_2020['Race with the Machine'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2020['Race with the Machine Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2020[['Race with the Machine Rank']], rank_label.rename(columns={"Rank": "Race with the Machine Rank"}), on='Race with the Machine Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2020['Changing the Course of the Race Rank'] = [None] + job_information_df_2020['Changing the Course of the Race'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2020['Changing the Course of the Race Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2020[['Changing the Course of the Race Rank']], rank_label.rename(columns={"Rank": "Changing the Course of the Race Rank"}), on='Changing the Course of the Race Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2020['Race ahead of the Machine Rank'] = [None] + job_information_df_2020['Race ahead of the Machine'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2020['Race ahead of the Machine Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2020[['Race ahead of the Machine Rank']], rank_label.rename(columns={"Rank": "Race ahead of the Machine Rank"}), on='Race ahead of the Machine Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2020['Race against the Machine Rank'] = [None] + job_information_df_2020['Race against the Machine'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2020['Race against the Machine Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2020[['Race against the Machine Rank']], rank_label.rename(columns={"Rank": "Race against the Machine Rank"}), on='Race against the Machine Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2020['Running a Different Race Rank'] = [None] + job_information_df_2020['Running a Different Race'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2020['Running a Different Race Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2020[['Running a Different Race Rank']], rank_label.rename(columns={"Rank": "Running a Different Race Rank"}), on='Running a Different Race Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2020['Employment Rank'] = [None] + job_information_df_2020['Employment'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2020['Employment Percentile'] = [None] + pd.merge(job_information_df_2020[['Employment Rank']], rank_label.rename(columns={"Rank": "Employment Rank"}), on='Employment Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2020['Annual Wage Rank'] = [None] + job_information_df_2020['Annual Wage'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2020['Annual Wage Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2020[['Annual Wage Rank']], rank_label.rename(columns={"Rank": "Annual Wage Rank"}), on='Annual Wage Rank', how='left')['Label'].iloc[1:].tolist()
job_information_df_2008_2020['Job Automation Index Change'] = job_information_df_2008_2020['Job Automation Index']
job_information_df_2008_2020['Race with the Machine Change'] = job_information_df_2008_2020['Race with the Machine']
job_information_df_2008_2020['Changing the Course of the Race Change'] = job_information_df_2008_2020['Changing the Course of the Race']
job_information_df_2008_2020['Race ahead of the Machine Change'] = job_information_df_2008_2020['Race ahead of the Machine']
job_information_df_2008_2020['Race against the Machine Change'] = job_information_df_2008_2020['Race against the Machine']
job_information_df_2008_2020['Running a Different Race Change'] = job_information_df_2008_2020['Running a Different Race']

job_information_df_2008_2020['Job Automation Index'] = None

job_information_df_2008_2020['Job Automation Index Change Rank'] = [None] + job_information_df_2008_2020['Job Automation Index Change'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2008_2020['Job Automation Index Change Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2008_2020[['Job Automation Index Change Rank']], rank_label.rename(columns={"Rank": "Job Automation Index Change Rank"}), on='Job Automation Index Change Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2008_2020['Race with the Machine Change Rank'] = [None] + job_information_df_2008_2020['Race with the Machine Change'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2008_2020['Race with the Machine Change Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2008_2020[['Race with the Machine Change Rank']], rank_label.rename(columns={"Rank": "Race with the Machine Change Rank"}), on='Race with the Machine Change Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2008_2020['Changing the Course of the Race Change Rank'] = [None] + job_information_df_2008_2020['Changing the Course of the Race Change'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2008_2020['Changing the Course of the Race Change Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2008_2020[['Changing the Course of the Race Change Rank']], rank_label.rename(columns={"Rank": "Changing the Course of the Race Change Rank"}), on='Changing the Course of the Race Change Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2008_2020['Race ahead of the Machine Change Rank'] = [None] + job_information_df_2008_2020['Race ahead of the Machine Change'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2008_2020['Race ahead of the Machine Change Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2008_2020[['Race ahead of the Machine Change Rank']], rank_label.rename(columns={"Rank": "Race ahead of the Machine Change Rank"}), on='Race ahead of the Machine Change Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2008_2020['Race against the Machine Change Rank'] = [None] + job_information_df_2008_2020['Race against the Machine Change'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2008_2020['Race against the Machine Change Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2008_2020[['Race against the Machine Change Rank']], rank_label.rename(columns={"Rank": "Race against the Machine Change Rank"}), on='Race against the Machine Change Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2008_2020['Running a Different Race Change Rank'] = [None] + job_information_df_2008_2020['Running a Different Race Change'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2008_2020['Running a Different Race Change Percentile'] = ['Top 50%'] + pd.merge(job_information_df_2008_2020[['Running a Different Race Change Rank']], rank_label.rename(columns={"Rank": "Running a Different Race Change Rank"}), on='Running a Different Race Change Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2008_2020['Wage Share Change Rank'] = [None] + job_information_df_2008_2020['Wage Share Change'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2008_2020['Wage Share Change Percentile']= [None] + pd.merge(job_information_df_2008_2020[['Wage Share Change Rank']], rank_label.rename(columns={"Rank": "Wage Share Change Rank"}), on='Wage Share Change Rank', how='left')['Label'].iloc[1:].tolist()

job_information_df_2008_2020['Employment Share Change Rank'] = [None] + job_information_df_2008_2020['Employment Share Change'].iloc[1:].rank(ascending=0, method='first').tolist()
job_information_df_2008_2020['Employment Share Change Percentile']= [None] + pd.merge(job_information_df_2008_2020[['Employment Share Change Rank']], rank_label.rename(columns={"Rank": "Employment Share Change Rank"}), on='Employment Share Change Rank', how='left')['Label'].iloc[1:].tolist()
job_information_df_2008_2020.head()
panel1_df = pd.DataFrame()
panel1_df = panel1_df.append(job_information_df_2008)
panel1_df = panel1_df.append(job_information_df_2020)
panel1_df = panel1_df.append(job_information_df_2008_2020)
panel1_df = panel1_df[['Year','SOC Code','Job Title','Description','Job Automation Index', 'Job Automation Index Rank','Job Automation Index Percentile',
                                              
                       'Race with the Machine', 'Race with the Machine Rank', 'Race with the Machine Percentile',
                       'Race ahead of the Machine', 'Race ahead of the Machine Rank', 'Race ahead of the Machine Percentile',
                       'Changing the Course of the Race', 'Changing the Course of the Race Rank', 'Changing the Course of the Race Percentile',
                       'Race against the Machine', 'Race against the Machine Rank', 'Race against the Machine Percentile',
                       'Running a Different Race', 'Running a Different Race Rank', 'Running a Different Race Percentile',
                       
                       'Employment', 'Employment Rank','Employment Percentile', 
                       'Annual Wage', 'Annual Wage Rank', 'Annual Wage Percentile', 
                       'Job Automation Index Change', 'Job Automation Index Change Rank','Job Automation Index Change Percentile',

                       'Race with the Machine Change', 'Race with the Machine Change Rank', 'Race with the Machine Change Percentile',
                       'Race ahead of the Machine Change', 'Race ahead of the Machine Change Rank', 'Race ahead of the Machine Change Percentile',
                       'Changing the Course of the Race Change', 'Changing the Course of the Race Change Rank', 'Changing the Course of the Race Change Percentile',
                       'Race against the Machine Change', 'Race against the Machine Change Rank', 'Race against the Machine Change Percentile',
                       'Running a Different Race Change', 'Running a Different Race Change Rank', 'Running a Different Race Change Percentile',
                       
                       'Wage Share Change', 'Wage Share Change Rank', 'Wage Share Change Percentile',
                       'Employment Share Change', 'Employment Share Change Rank', 'Employment Share Change Percentile',]]
panel1_df.columns
panel1_df.head()
panel1_df = panel1_df.rename({'SOC Code': 'Job Code'})
panel1_df.to_csv("../static/data/csv/Panel 1.csv", index=False, encoding='utf-8')<jupyter_output><empty_output><jupyter_text>Job Automation Index  Panel 2<jupyter_code>task_automation_df_2008 = pd.read_excel("../static/data/xlsx/Task-Level Job Automation_2021v2.xlsx", sheetname='2008')
task_automation_df_2020 = pd.read_excel("../static/data/xlsx/Task-Level Job Automation_2021v2.xlsx", sheetname='2020')
task_automation_df_2008_2020 = pd.read_excel("../static/data/xlsx/Task-Level Job Automation_2021v2.xlsx", sheetname='2008-2020')
task_automation_df_2008['Year'] = '2008'
task_automation_df_2020['Year'] = '2020'
task_automation_df_2008_2020['Year'] = '2008~2020'
panel2_df = pd.DataFrame()
panel2_df = panel2_df.append(task_automation_df_2008[['Year','SOC Code','Job Title','Race with the Machine', 'Race ahead of the Machine', 'Changing the Course of the Race',
                                                      'Race against the Machine', 'Running a Different Race']])

panel2_df = panel2_df.append(task_automation_df_2020[['Year','SOC Code','Job Title','Race with the Machine', 'Race ahead of the Machine', 'Changing the Course of the Race',
                                                      'Race against the Machine', 'Running a Different Race']])

panel2_df = panel2_df.append(task_automation_df_2008_2020[['Year','SOC Code','Job Title','Race with the Machine', 'Race ahead of the Machine', 'Changing the Course of the Race',
                                                      'Race against the Machine', 'Running a Different Race']])

job_information_df_2008_2020.head()
panel2_df = panel2_df.merge(job_information_df_2008_2020[['SOC Code','Job Family']], on='SOC Code', how='left')
panel2_df.tail()['Year']
job_information_df_2008_2020['Year'] = '2008~2020'
panel2_df = panel2_df.merge(job_information_df_2008_2020[['SOC Code', 'Year', 'Employment Share Change', 'Wage Share Change']], on=['SOC Code', 'Year'], how='left')
panel2_df.tail()
panel2_df = panel2_df[['Year','SOC Code','Job Title','Job Family', 'Race with the Machine', 'Race ahead of the Machine', 'Changing the Course of the Race',
                                                      'Race against the Machine', 'Running a Different Race', 'Employment Share Change', 'Wage Share Change']]
panel2_df = panel2_df.rename(columns={'SOC Code': 'Job Code'})
panel2_df.head()
panel2_df.to_csv("../static/data/csv/Panel 2.csv", index=False, encoding='utf-8')<jupyter_output><empty_output><jupyter_text>Panel 4<jupyter_code>task_automation_df_2008 = pd.read_excel("../static/data/xlsx/Task-Level Job Automation_2021v2.xlsx", sheetname='2008')
task_automation_df_2020 = pd.read_excel("../static/data/xlsx/Task-Level Job Automation_2021v2.xlsx", sheetname='2020')
task_automation_df_2008_2020 = pd.read_excel("../static/data/xlsx/Task-Level Job Automation_2021v2.xlsx", sheetname='2008-2020')
task_automation_df_2008 = task_automation_df_2008.rename(columns={"SOC Code":"Job Code"})
task_automation_df_2020 = task_automation_df_2020.rename(columns={"SOC Code":"Job Code"})
task_automation_df_2008_2020 = task_automation_df_2008_2020.rename(columns={"SOC Code":"Job Code"})
task_automation_df_2008.to_csv("../static/data/csv/Panel 4 2008.csv", encoding='utf-8')
task_automation_df_2020.to_csv("../static/data/csv/Panel 4 2020.csv", encoding='utf-8')
task_automation_df_2008_2020.to_csv("../static/data/csv/Panel 4 2008~2020.csv", encoding='utf-8')<jupyter_output><empty_output><jupyter_text>Panel 5<jupyter_code>task_automation_df_2008 = pd.read_excel("../static/data/xlsx/Task-Level Job Automation_2021v2.xlsx", sheetname='2008')
task_automation_df_2020 = pd.read_excel("../static/data/xlsx/Task-Level Job Automation_2021v2.xlsx", sheetname='2020')
task_automation_df_2008_2020 = pd.read_excel("../static/data/xlsx/Task-Level Job Automation_2021v2.xlsx", sheetname='2008-2020')
task_automation_df_2008 = task_automation_df_2008[['SOC Code','Job Title','Race with the Machine', 'Race ahead of the Machine', 'Changing the Course of the Race',
                                                      'Race against the Machine', 'Running a Different Race']]
task_automation_df_2020 = task_automation_df_2020[['SOC Code','Job Title','Race with the Machine', 'Race ahead of the Machine', 'Changing the Course of the Race',
                                                      'Race against the Machine', 'Running a Different Race']]
task_automation_df_2008_2020 = task_automation_df_2008_2020[['SOC Code','Job Title','Race with the Machine', 'Race ahead of the Machine', 'Changing the Course of the Race',
                                                      'Race against the Machine', 'Running a Different Race']]
task_automation_df_2008 = task_automation_df_2008.rename(columns={"SOC Code":"Job Code"})
task_automation_df_2020 = task_automation_df_2020.rename(columns={"SOC Code":"Job Code"})
task_automation_df_2008_2020 = task_automation_df_2008_2020.rename(columns={"SOC Code":"Job Code"})
task_automation_df_2008.to_csv("../static/data/csv/Panel 5 2008.csv", encoding='utf-8')
task_automation_df_2020.to_csv("../static/data/csv/Panel 5 2020.csv", encoding='utf-8')
task_automation_df_2008_2020.to_csv("../static/data/csv/Panel 5 2008~2020.csv", encoding='utf-8')<jupyter_output><empty_output><jupyter_text>Skill DNA<jupyter_code>skill_dna_df_2008 = pd.read_excel("../static/data/xlsx/Skill DNA.xlsx", sheetname='2008', skiprows=[0])
skill_dna_df_2020 = pd.read_excel("../static/data/xlsx/Skill DNA.xlsx", sheetname='2020', skiprows=[0])
skill_dna_df_automation_level = pd.read_excel("../static/data/xlsx/Skill DNA.xlsx", sheetname='Automation Level')
skill_dna_df_automation_level.head()
skill_dna_df_automation_level.columns = ['Skill Id', 'Skill Name', 'Task', '2020 Automation Level', '2008 Automation Level', 'Change from 2008 to 2020']
skill_dna_df_automation_level.to_csv("../static/data/csv/Panel 3.csv", index=False)
skill_dna_df_2008 = skill_dna_df_2008.rename(columns={"SOC Code":"Job Code"})
skill_dna_df_2020 = skill_dna_df_2020.rename(columns={"SOC Code":"Job Code"})
skill_dna_df_2008.to_csv("../static/data/csv/Panel 3 Skill Importance 2008.csv", index=False, encoding='utf-8')
skill_dna_df_2020.to_csv("../static/data/csv/Panel 3 Skill Importance 2020.csv", index=False, encoding='utf-8')<jupyter_output><empty_output><jupyter_text>City Automation Index  Panel 6<jupyter_code>city_level_df_2008 = pd.read_excel("../static/data/xlsx/Task-Level City Automation_2021v2.xlsx", sheetname='2008')
city_level_df_2020 = pd.read_excel("../static/data/xlsx/Task-Level City Automation_2021v2.xlsx", sheetname='2020')
city_level_df_2008_2020 = pd.read_excel("../static/data/xlsx/Task-Level City Automation_2021v2.xlsx", sheetname='2008-2020')
city_level_df_2008.head()
city_level_df_2008 = city_level_df_2008[['MSA Code', 'AREA_NAME', 'Employment','Automation Index', 'Race with the Machine', 'Race ahead of the Machine', 'Changing the Course of the Race',
                                                      'Race against the Machine', 'Running a Different Race','Lat','Long']]
city_level_df_2020 = city_level_df_2020[['MSA Code', 'AREA_NAME', 'Employment','Automation Index', 'Race with the Machine', 'Race ahead of the Machine', 'Changing the Course of the Race',
                                                      'Race against the Machine', 'Running a Different Race','Lat','Long']]
city_level_df_2008_2020 = city_level_df_2008_2020[['MSA Code', 'AREA_NAME', 'Employment','Automation Index', 'Race with the Machine', 'Race ahead of the Machine', 'Changing the Course of the Race',
                                                      'Race against the Machine', 'Running a Different Race','Lat','Long']]
city_level_df_2008 = city_level_df_2008.rename(columns={'MSA Code':'AREA'})
city_level_df_2020 = city_level_df_2020.rename(columns={'MSA Code':'AREA'})
city_level_df_2008_2020 = city_level_df_2008_2020.rename(columns={'MSA Code':'AREA'})
city_level_df_2008.to_csv("../static/data/csv/Panel 6 2008.csv", index=False)
city_level_df_2020.to_csv("../static/data/csv/Panel 6 2020.csv", index=False)
city_level_df_2008_2020.to_csv("../static/data/csv/Panel 6 2008~2020.csv", index=False)<jupyter_output><empty_output><jupyter_text>Panel 7<jupyter_code>city_level_df_2008 = pd.read_excel("../static/data/xlsx/Task-Level City Automation_2021v2.xlsx", sheetname='2008')
city_level_df_2020 = pd.read_excel("../static/data/xlsx/Task-Level City Automation_2021v2.xlsx", sheetname='2020')
city_level_df_2008_2020 = pd.read_excel("../static/data/xlsx/Task-Level City Automation_2021v2.xlsx", sheetname='2008-2020')
city_level_df_2008.head()
len(city_level_df_2008.columns)
city_level_df_2008['State'] = [area_name.split(', ')[1] for area_name in city_level_df_2008['AREA_NAME'].tolist()]
city_level_df_2020['State'] = [area_name.split(', ')[1] for area_name in city_level_df_2020['AREA_NAME'].tolist()]
city_level_df_2008_2020['State'] = [area_name.split(', ')[1] for area_name in city_level_df_2008_2020['AREA_NAME'].tolist()]
city_level_df_2008['Metropolitan Areas'] = [area_name.split(', ')[0] for area_name in city_level_df_2008['AREA_NAME'].tolist()]
city_level_df_2020['Metropolitan Areas'] = [area_name.split(', ')[0] for area_name in city_level_df_2020['AREA_NAME'].tolist()]
city_level_df_2008_2020['Metropolitan Areas'] = [area_name.split(', ')[0] for area_name in city_level_df_2008_2020['AREA_NAME'].tolist()]
city_level_df_2008.columns
selected_cols = ['State','Metropolitan Areas',
               #'Automation Index', 
                 
                'Race with the Machine', 'Interpersonal Task', 'Group Task', 'Conflict Resolution', 'Management and Supervision',

                'Race ahead of the Machine', 'Communication', 'Systems Analysis', 'Creative and Critical Thinking',

                'Changing the Course of the Race', 'Routine Problem Solving', 'Information Processing',

                'Race against the Machine', 'General Physical Task', 'Dynamic Physical Task',

                'Running a Different Race', 'Equipment Maintenance',  'Vehicle and Equipment Operation', 'Machine Control', 'Hazardous Task'                 
                ]

city_level_df_2008 = city_level_df_2008[selected_cols]
city_level_df_2020 = city_level_df_2020[selected_cols]
city_level_df_2008_2020 = city_level_df_2008_2020[selected_cols]
city_level_df_2008.to_csv("../static/data/csv/Panel 7 2008.csv")
city_level_df_2020.to_csv("../static/data/csv/Panel 7 2020.csv")
city_level_df_2008_2020.to_csv("../static/data/csv/Panel 7 2008~2020.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1439567453910562, 'fraction_numerical': 0.06758532647467427, 'mean_word_length': 5.972251308900524, 'pattern_counts': {'":': 32, '<': 36, '<?xml version=': 0, '>': 36, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 22, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '103053', 'license': 'zlib-acknowledgement', 'n_tokens_mistral': 9816, 'n_tokens_neox': 8491, 'n_words': 1882, 'path': 'notebook/excel_to_panel.ipynb', 'repo_name': 'jonghkim/job-automation-index-web', 'size': '26633'}