In [1]:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import sqlite3
from pathlib import Path
In [2]:
# Create sqlite connection to part 1 db
conn = sqlite3.connect('Data/sqlite_dbs/salary_db_part1.db')
# Make the part1db the base raw data
raw_data = pd.read_sql('SELECT * FROM salaries', conn)
conn.close()
# from 2 to the number of parts
for i in range(2,19):
# Create connections for this db part
conn = sqlite3.connect(f'Data/sqlite_dbs/salary_db_part{i}.db')
# Read the sql into a df
this_data = pd.read_sql('SELECT * FROM salaries', conn)
# concatenate this new df to the raw data
raw_data = pd.concat([raw_data, this_data], ignore_index=True)
conn.close()
raw_data.shape
Out[2]:
(12029088, 13)
In [3]:
# Create Path object of our directory
path = Path('./Data/salary_data/')
# Loop through the directories (we won't put files here) in Data/salary_data. Each one will be a county
for ctydir in path.iterdir():
# The name of the directory is the name of the county
county = ctydir.name
# Now we make a new path object using this directory
county_path = Path(ctydir)
# We iterate through the directory
for f in county_path.iterdir():
# Read the csv
print(f'Reading {f}')
this_data = pd.read_csv(f)
# Add our county into the dataframe
this_data['county'] = county
# We could maybe recreate the url but I plan to cut it later anyway. so for now we'll just note that it came from CSV
this_data['url'] = 'CSV'
# Drop the fields that aren't in the other data
# Not all the data has all the fields, so we have to do it in a way that won't crash us out
if 'Pension Debt' in this_data.columns:
this_data = this_data.drop('Pension Debt', axis=1)
this_data = this_data.drop(['Notes', 'Status'], axis=1)
# format the column names to be the same as the other data (replace & with and, replace space with _, and make lowercase)
this_data.columns = this_data.columns.str.replace('&', 'and').str.replace(' ', '_').str.lower()
# Rename agency to entity to match the other data.
this_data = this_data.rename(columns={'agency': 'entity',
'base_pay': 'regular_pay'})
raw_data = pd.concat([raw_data, this_data], ignore_index=True)
raw_data.shape
Reading Data/salary_data/Los Angeles/torrance-unified-2019.csv Reading Data/salary_data/Los Angeles/william-s-hart-union-high-2014.csv Reading Data/salary_data/Orange/capistrano-unified-2024.csv Reading Data/salary_data/Orange/irvine-unified-2020.csv Reading Data/salary_data/Orange/anaheim-union-high-2019.csv Reading Data/salary_data/Orange/anaheim-union-high-2021.csv Reading Data/salary_data/Orange/anaheim-union-high-2022.csv Reading Data/salary_data/Orange/capistrano-unified-2013.csv Reading Data/salary_data/Orange/capistrano-unified-2014.csv Reading Data/salary_data/Orange/capistrano-unified-2015.csv Reading Data/salary_data/Orange/capistrano-unified-2016.csv Reading Data/salary_data/Orange/capistrano-unified-2017.csv Reading Data/salary_data/Orange/capistrano-unified-2018.csv Reading Data/salary_data/Orange/capistrano-unified-2019.csv Reading Data/salary_data/Orange/capistrano-unified-2020.csv Reading Data/salary_data/Orange/capistrano-unified-2021.csv Reading Data/salary_data/Orange/capistrano-unified-2022.csv Reading Data/salary_data/Orange/capistrano-unified-2023.csv Reading Data/salary_data/Orange/garden-grove-unified-2013.csv Reading Data/salary_data/Orange/garden-grove-unified-2014.csv Reading Data/salary_data/Orange/garden-grove-unified-2015.csv Reading Data/salary_data/Orange/garden-grove-unified-2016.csv Reading Data/salary_data/Orange/garden-grove-unified-2017.csv Reading Data/salary_data/Orange/garden-grove-unified-2018.csv Reading Data/salary_data/Orange/garden-grove-unified-2019.csv Reading Data/salary_data/Orange/garden-grove-unified-2020.csv Reading Data/salary_data/Orange/garden-grove-unified-2021.csv Reading Data/salary_data/Orange/garden-grove-unified-2022.csv Reading Data/salary_data/Orange/garden-grove-unified-2023.csv Reading Data/salary_data/Orange/garden-grove-unified-2024.csv Reading Data/salary_data/Orange/irvine-unified-2018.csv Reading Data/salary_data/Orange/irvine-unified-2019.csv Reading Data/salary_data/Orange/irvine-unified-2021.csv Reading Data/salary_data/Orange/irvine-unified-2022.csv Reading Data/salary_data/Orange/irvine-unified-2023.csv Reading Data/salary_data/Orange/irvine-unified-2024.csv Reading Data/salary_data/Orange/saddleback-valley-unified-2018.csv Reading Data/salary_data/Orange/santa-ana-unified-2013.csv Reading Data/salary_data/Orange/santa-ana-unified-2014.csv Reading Data/salary_data/Orange/santa-ana-unified-2015.csv Reading Data/salary_data/Orange/santa-ana-unified-2016.csv Reading Data/salary_data/Orange/santa-ana-unified-2017.csv Reading Data/salary_data/Orange/santa-ana-unified-2018.csv Reading Data/salary_data/Orange/santa-ana-unified-2019.csv Reading Data/salary_data/Orange/santa-ana-unified-2020.csv Reading Data/salary_data/Orange/santa-ana-unified-2021.csv Reading Data/salary_data/Orange/santa-ana-unified-2022.csv Reading Data/salary_data/Orange/santa-ana-unified-2023.csv Reading Data/salary_data/Orange/santa-ana-unified-2024.csv Reading Data/salary_data/Riverside/hemet-unified-2024.csv Reading Data/salary_data/Riverside/corona-norco-unified-2012.csv Reading Data/salary_data/Riverside/corona-norco-unified-2013.csv Reading Data/salary_data/Riverside/corona-norco-unified-2014.csv Reading Data/salary_data/Riverside/corona-norco-unified-2015.csv Reading Data/salary_data/Riverside/corona-norco-unified-2016.csv Reading Data/salary_data/Riverside/corona-norco-unified-2017.csv Reading Data/salary_data/Riverside/corona-norco-unified-2018.csv Reading Data/salary_data/Riverside/corona-norco-unified-2019.csv Reading Data/salary_data/Riverside/corona-norco-unified-2020.csv Reading Data/salary_data/Riverside/corona-norco-unified-2021.csv Reading Data/salary_data/Riverside/corona-norco-unified-2022.csv Reading Data/salary_data/Riverside/corona-norco-unified-2023.csv Reading Data/salary_data/Riverside/corona-norco-unified-2024.csv Reading Data/salary_data/Riverside/moreno-valley-unified-2013.csv Reading Data/salary_data/Riverside/moreno-valley-unified-2015.csv Reading Data/salary_data/Riverside/moreno-valley-unified-2016.csv Reading Data/salary_data/Riverside/moreno-valley-unified-2017.csv Reading Data/salary_data/Riverside/moreno-valley-unified-2018.csv Reading Data/salary_data/Riverside/moreno-valley-unified-2019.csv Reading Data/salary_data/Riverside/moreno-valley-unified-2020.csv Reading Data/salary_data/Riverside/moreno-valley-unified-2021.csv Reading Data/salary_data/Riverside/moreno-valley-unified-2022.csv Reading Data/salary_data/Riverside/moreno-valley-unified-2023.csv Reading Data/salary_data/Riverside/moreno-valley-unified-2024.csv Reading Data/salary_data/Riverside/riverside-unified-2012.csv Reading Data/salary_data/Riverside/riverside-unified-2013.csv Reading Data/salary_data/Riverside/riverside-unified-2014.csv Reading Data/salary_data/Riverside/riverside-unified-2015.csv Reading Data/salary_data/Riverside/riverside-unified-2016.csv Reading Data/salary_data/Riverside/riverside-unified-2017.csv Reading Data/salary_data/Riverside/riverside-unified-2018.csv Reading Data/salary_data/Riverside/riverside-unified-2019.csv Reading Data/salary_data/Riverside/riverside-unified-2020.csv Reading Data/salary_data/Riverside/riverside-unified-2021.csv Reading Data/salary_data/Riverside/riverside-unified-2022.csv Reading Data/salary_data/Riverside/riverside-unified-2023.csv Reading Data/salary_data/Riverside/riverside-unified-2024.csv Reading Data/salary_data/Riverside/temecula-valley-unified-2024.csv Reading Data/salary_data/Sacramento/sacramento-city-unified-2016.csv Reading Data/salary_data/Sacramento/elk-grove-unified-2012.csv Reading Data/salary_data/Sacramento/elk-grove-unified-2013.csv Reading Data/salary_data/Sacramento/elk-grove-unified-2014.csv Reading Data/salary_data/Sacramento/elk-grove-unified-2015.csv Reading Data/salary_data/Sacramento/elk-grove-unified-2016.csv Reading Data/salary_data/Sacramento/elk-grove-unified-2017.csv Reading Data/salary_data/Sacramento/elk-grove-unified-2018.csv Reading Data/salary_data/Sacramento/elk-grove-unified-2019.csv Reading Data/salary_data/Sacramento/elk-grove-unified-2020.csv Reading Data/salary_data/Sacramento/elk-grove-unified-2021.csv Reading Data/salary_data/Sacramento/elk-grove-unified-2022.csv Reading Data/salary_data/Sacramento/elk-grove-unified-2023.csv Reading Data/salary_data/Sacramento/elk-grove-unified-2024.csv Reading Data/salary_data/Sacramento/sacramento-city-unified-2013.csv Reading Data/salary_data/Sacramento/sacramento-city-unified-2014.csv Reading Data/salary_data/Sacramento/sacramento-city-unified-2015.csv Reading Data/salary_data/Sacramento/sacramento-city-unified-2017.csv Reading Data/salary_data/Sacramento/sacramento-city-unified-2018.csv Reading Data/salary_data/Sacramento/sacramento-city-unified-2019.csv Reading Data/salary_data/Sacramento/sacramento-city-unified-2020.csv Reading Data/salary_data/Sacramento/sacramento-city-unified-2021.csv Reading Data/salary_data/Sacramento/sacramento-city-unified-2022.csv Reading Data/salary_data/Sacramento/sacramento-city-unified-2023.csv Reading Data/salary_data/Sacramento/sacramento-city-unified-2024.csv Reading Data/salary_data/Sacramento/san-juan-unified-2015.csv Reading Data/salary_data/Sacramento/san-juan-unified-2018.csv Reading Data/salary_data/Sacramento/san-juan-unified-2020.csv Reading Data/salary_data/Sacramento/san-juan-unified-2021.csv Reading Data/salary_data/Sacramento/san-juan-unified-2022.csv Reading Data/salary_data/Sacramento/san-juan-unified-2023.csv Reading Data/salary_data/Sacramento/san-juan-unified-2024.csv Reading Data/salary_data/San Bernardino/fontana-unified-2015.csv Reading Data/salary_data/San Bernardino/fontana-unified-2016.csv Reading Data/salary_data/San Bernardino/fontana-unified-2017.csv Reading Data/salary_data/San Bernardino/fontana-unified-2018.csv Reading Data/salary_data/San Bernardino/fontana-unified-2019.csv Reading Data/salary_data/San Bernardino/fontana-unified-2020.csv Reading Data/salary_data/San Bernardino/fontana-unified-2021.csv Reading Data/salary_data/San Bernardino/fontana-unified-2022.csv Reading Data/salary_data/San Bernardino/fontana-unified-2023.csv Reading Data/salary_data/San Bernardino/fontana-unified-2024.csv Reading Data/salary_data/San Bernardino/san-bernardino-city-unified-2012.csv Reading Data/salary_data/San Bernardino/san-bernardino-city-unified-2013.csv Reading Data/salary_data/San Bernardino/san-bernardino-city-unified-2014.csv Reading Data/salary_data/San Bernardino/san-bernardino-city-unified-2015.csv Reading Data/salary_data/San Bernardino/san-bernardino-city-unified-2016.csv Reading Data/salary_data/San Bernardino/san-bernardino-city-unified-2017.csv Reading Data/salary_data/San Bernardino/san-bernardino-city-unified-2018.csv Reading Data/salary_data/San Bernardino/san-bernardino-city-unified-2019.csv Reading Data/salary_data/San Bernardino/san-bernardino-city-unified-2021.csv Reading Data/salary_data/San Bernardino/san-bernardino-city-unified-2022.csv Reading Data/salary_data/San Bernardino/san-bernardino-city-unified-2023.csv Reading Data/salary_data/San Bernardino/san-bernardino-city-unified-2024.csv Reading Data/salary_data/San Diego/poway-unified-2018.csv Reading Data/salary_data/San Diego/san-diego-unified-2021.csv Reading Data/salary_data/San Diego/cajon-valley-union-2015.csv Reading Data/salary_data/San Diego/cajon-valley-union-2016.csv Reading Data/salary_data/San Diego/chula-vista-elementary-2012.csv Reading Data/salary_data/San Diego/chula-vista-elementary-2013.csv Reading Data/salary_data/San Diego/chula-vista-elementary-2014.csv Reading Data/salary_data/San Diego/chula-vista-elementary-2017.csv Reading Data/salary_data/San Diego/chula-vista-elementary-2018.csv Reading Data/salary_data/San Diego/chula-vista-elementary-2019.csv Reading Data/salary_data/San Diego/chula-vista-elementary-2020.csv Reading Data/salary_data/San Diego/chula-vista-elementary-2022.csv Reading Data/salary_data/San Diego/chula-vista-elementary-2023.csv Reading Data/salary_data/San Diego/chula-vista-elementary-2024.csv Reading Data/salary_data/San Diego/oceanside-unified-2014.csv Reading Data/salary_data/San Diego/poway-unified-2013.csv Reading Data/salary_data/San Diego/poway-unified-2014.csv Reading Data/salary_data/San Diego/poway-unified-2015.csv Reading Data/salary_data/San Diego/poway-unified-2016.csv Reading Data/salary_data/San Diego/poway-unified-2017.csv Reading Data/salary_data/San Diego/poway-unified-2019.csv Reading Data/salary_data/San Diego/poway-unified-2020.csv Reading Data/salary_data/San Diego/poway-unified-2021.csv Reading Data/salary_data/San Diego/poway-unified-2022.csv Reading Data/salary_data/San Diego/poway-unified-2023.csv Reading Data/salary_data/San Diego/poway-unified-2024.csv Reading Data/salary_data/San Diego/san-diego-unified-2013.csv Reading Data/salary_data/San Diego/san-diego-unified-2014.csv Reading Data/salary_data/San Diego/san-diego-unified-2015.csv Reading Data/salary_data/San Diego/san-diego-unified-2016.csv Reading Data/salary_data/San Diego/san-diego-unified-2017.csv Reading Data/salary_data/San Diego/san-diego-unified-2018.csv Reading Data/salary_data/San Diego/san-diego-unified-2019.csv Reading Data/salary_data/San Diego/san-diego-unified-2020.csv Reading Data/salary_data/San Diego/san-diego-unified-2022.csv Reading Data/salary_data/San Diego/san-diego-unified-2023.csv Reading Data/salary_data/San Diego/san-diego-unified-2024.csv Reading Data/salary_data/San Diego/san-marcos-unified-2012.csv Reading Data/salary_data/San Diego/san-marcos-unified-2013.csv Reading Data/salary_data/San Diego/san-marcos-unified-2014.csv Reading Data/salary_data/San Diego/san-marcos-unified-2017.csv Reading Data/salary_data/San Diego/sweetwater-union-high-2012.csv Reading Data/salary_data/San Diego/sweetwater-union-high-2013.csv Reading Data/salary_data/San Diego/sweetwater-union-high-2014.csv Reading Data/salary_data/San Diego/sweetwater-union-high-2015.csv Reading Data/salary_data/San Diego/sweetwater-union-high-2016.csv Reading Data/salary_data/San Diego/sweetwater-union-high-2017.csv Reading Data/salary_data/San Diego/sweetwater-union-high-2018.csv Reading Data/salary_data/San Diego/sweetwater-union-high-2019.csv Reading Data/salary_data/San Diego/sweetwater-union-high-2020.csv Reading Data/salary_data/San Diego/sweetwater-union-high-2022.csv Reading Data/salary_data/San Diego/sweetwater-union-high-2023.csv Reading Data/salary_data/San Diego/sweetwater-union-high-2024.csv Reading Data/salary_data/San Diego/vista-unified-2012.csv Reading Data/salary_data/San Diego/vista-unified-2013.csv Reading Data/salary_data/San Diego/vista-unified-2014.csv Reading Data/salary_data/San Diego/vista-unified-2018.csv Reading Data/salary_data/San Francisco/san-francisco-unified-2013.csv Reading Data/salary_data/San Francisco/san-francisco-unified-2014.csv Reading Data/salary_data/San Francisco/san-francisco-unified-2015.csv Reading Data/salary_data/San Francisco/san-francisco-unified-2016.csv Reading Data/salary_data/San Francisco/san-francisco-unified-2017.csv Reading Data/salary_data/San Francisco/san-francisco-unified-2018.csv Reading Data/salary_data/San Francisco/san-francisco-unified-2019.csv Reading Data/salary_data/San Francisco/san-francisco-unified-2020.csv Reading Data/salary_data/San Francisco/san-francisco-unified-2022.csv Reading Data/salary_data/San Francisco/san-francisco-unified-2023.csv Reading Data/salary_data/San Francisco/san-francisco-unified-2024.csv Reading Data/salary_data/San Joaquin/lodi-unified-2016.csv Reading Data/salary_data/San Joaquin/lodi-unified-2018.csv Reading Data/salary_data/San Joaquin/lodi-unified-2019.csv Reading Data/salary_data/San Joaquin/stockton-unified-2019.csv Reading Data/salary_data/San Joaquin/stockton-unified-2024.csv Reading Data/salary_data/Stanislaus/modesto-city-schools-2017.csv Reading Data/salary_data/Stanislaus/modesto-city-schools-2018.csv Reading Data/salary_data/Stanislaus/modesto-city-schools-2019.csv Reading Data/salary_data/Stanislaus/modesto-city-schools-2020.csv Reading Data/salary_data/Stanislaus/modesto-city-schools-2021.csv Reading Data/salary_data/Stanislaus/modesto-city-schools-2022.csv Reading Data/salary_data/Stanislaus/modesto-city-schools-2023.csv Reading Data/salary_data/Stanislaus/modesto-city-schools-2024.csv Reading Data/salary_data/Tulare/visalia-unified-2019.csv Reading Data/salary_data/Tulare/visalia-unified-2020.csv Reading Data/salary_data/Tulare/visalia-unified-2023.csv Reading Data/salary_data/Tulare/visalia-unified-2024.csv
Out[3]:
(13809125, 13)
In [4]:
raw_data[raw_data['url'] == 'CSV']
Out[4]:
| ID | employee_name | job_title | county | entity | year | regular_pay | overtime_pay | other_pay | total_pay | benefits | total_pay_and_benefits | url | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 12029088 | NaN | George W Mannon | Superintendent | Los Angeles | Torrance Unified | 2019 | 286760.0 | 0.0 | 12803.0 | 299563.0 | 55201.0 | 354764.0 | CSV |
| 12029089 | NaN | Timothy Stowe | Deputy Superint Admin Svc | Los Angeles | Torrance Unified | 2019 | 236150.0 | 0.0 | 1324.0 | 237474.0 | 46788.0 | 284262.0 | CSV |
| 12029090 | NaN | Kati P Krumpe | Chief Academic Officer | Los Angeles | Torrance Unified | 2019 | 212594.0 | 0.0 | 1868.0 | 214462.0 | 42971.0 | 257433.0 | CSV |
| 12029091 | NaN | Judy S Chai | Fiscal Services Officer | Los Angeles | Torrance Unified | 2019 | 182775.0 | 0.0 | 0.0 | 182775.0 | 41622.0 | 224397.0 | CSV |
| 12029092 | NaN | Gilmore Mara | Chief Edu Tech & It Ofcr | Los Angeles | Torrance Unified | 2019 | 182460.0 | 0.0 | 0.0 | 182460.0 | 36969.0 | 219429.0 | CSV |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 13809120 | NaN | Rosa E Elizondo | Teacher, Grade 6 | Tulare | Visalia Unified | 2024 | 0.0 | 0.0 | 9.0 | 9.0 | 0.0 | 9.0 | CSV |
| 13809121 | NaN | Benjamin J Graham | Education Specialist, Mild/Mod | Tulare | Visalia Unified | 2024 | 0.0 | 0.0 | 6.0 | 6.0 | 0.0 | 6.0 | CSV |
| 13809122 | NaN | Michelle L le | Teacher, Grade 3 | Tulare | Visalia Unified | 2024 | 0.0 | 0.0 | 4.0 | 4.0 | 0.0 | 4.0 | CSV |
| 13809123 | NaN | Ashley V Chavez | Teacher, Grade 3 | Tulare | Visalia Unified | 2024 | 0.0 | 0.0 | 2.0 | 2.0 | 0.0 | 2.0 | CSV |
| 13809124 | NaN | Edith M Rodriguez | Education Specialist, Mild/Mod | Tulare | Visalia Unified | 2024 | 0.0 | 0.0 | 0.0 | 0.0 | -2136.0 | -2136.0 | CSV |
1780037 rows × 13 columns
In [5]:
# Now we'll drop our index column (it will get readded later when we write to db)
data_no_index = raw_data.drop(['ID'], axis=1)
data_no_index.head()
Out[5]:
| employee_name | job_title | county | entity | year | regular_pay | overtime_pay | other_pay | total_pay | benefits | total_pay_and_benefits | url | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | Alysse B Castro | Superintendent | alameda | alameda-county-office-of-education | 2024 | 303683.71 | 0 | 0 | 303683.71 | 60546.96 | 364230.67 | https://transparentcalifornia.com/salaries/202... |
| 1 | Chaunise Powell | Sr Chief Of Student Services | alameda | alameda-county-office-of-education | 2024 | 275332.45 | 0 | 1200 | 276532.45 | 46705.72 | 323238.17 | https://transparentcalifornia.com/salaries/202... |
| 2 | Monica R Vaughan | Sr Chief Of Schools | alameda | alameda-county-office-of-education | 2024 | 258532.67 | 0 | 1200 | 259732.67 | 62919.54 | 322652.21 | https://transparentcalifornia.com/salaries/202... |
| 3 | Carmella Franco | Ce-Short Term Salaries | alameda | alameda-county-office-of-education | 2024 | 159238 | 0 | 159238 | 318476.0 | 0 | 318476.00 | https://transparentcalifornia.com/salaries/202... |
| 4 | Juwen Lam | Chief Of Accountability | alameda | alameda-county-office-of-education | 2024 | 201530.69 | 0 | 1200 | 202730.69 | 67623.96 | 270354.65 | https://transparentcalifornia.com/salaries/202... |
In [6]:
# We need to clean up the entity field (it was taken from a URL)
# We will split by - and then rejoin with a space instead. Then capitalize the first letter of each word
# Title will capitalize of, so we'll then replace Of with of
data_no_index['entity'] = data_no_index['entity'].str.split('-').str.join(' ').str.title()
# We'll do the same to county
data_no_index['county'] = data_no_index['county'].str.split('-').str.join(' ').str.title()
In [7]:
# .title doesn't ignore some words it should. I think the only ones to worry about are Of, And, and The
# Doing it here instead of the previous cell for cleanliness' sake
# Dong the same for county and entity
data_no_index['county'] = data_no_index['county'].str.replace(' Of ', ' of ').str.replace(' And ', ' and ').str.replace(' The ', ' the ')
data_no_index['entity'] = data_no_index['entity'].str.replace(' Of ', ' of ').str.replace(' And ', ' and ').str.replace(' The ', ' the ')
data_no_index.head()
Out[7]:
| employee_name | job_title | county | entity | year | regular_pay | overtime_pay | other_pay | total_pay | benefits | total_pay_and_benefits | url | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | Alysse B Castro | Superintendent | Alameda | Alameda County Office of Education | 2024 | 303683.71 | 0 | 0 | 303683.71 | 60546.96 | 364230.67 | https://transparentcalifornia.com/salaries/202... |
| 1 | Chaunise Powell | Sr Chief Of Student Services | Alameda | Alameda County Office of Education | 2024 | 275332.45 | 0 | 1200 | 276532.45 | 46705.72 | 323238.17 | https://transparentcalifornia.com/salaries/202... |
| 2 | Monica R Vaughan | Sr Chief Of Schools | Alameda | Alameda County Office of Education | 2024 | 258532.67 | 0 | 1200 | 259732.67 | 62919.54 | 322652.21 | https://transparentcalifornia.com/salaries/202... |
| 3 | Carmella Franco | Ce-Short Term Salaries | Alameda | Alameda County Office of Education | 2024 | 159238 | 0 | 159238 | 318476.0 | 0 | 318476.00 | https://transparentcalifornia.com/salaries/202... |
| 4 | Juwen Lam | Chief Of Accountability | Alameda | Alameda County Office of Education | 2024 | 201530.69 | 0 | 1200 | 202730.69 | 67623.96 | 270354.65 | https://transparentcalifornia.com/salaries/202... |
In [8]:
# Seemed like it deleted a lot so i want to investigate a little
data_no_index[data_no_index.duplicated(keep=False)]
Out[8]:
| employee_name | job_title | county | entity | year | regular_pay | overtime_pay | other_pay | total_pay | benefits | total_pay_and_benefits | url | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1038014 | Victor Rosa | Superintendent | Kings | Hanford Joint Union High | 2020 | 183699 | 0 | 4800 | 188499.0 | 48667 | 237166.0 | https://transparentcalifornia.com/salaries/202... |
| 1038015 | Renee Creech | Assistant Superintendent | Kings | Hanford Joint Union High | 2020 | 179256 | 0 | 4800 | 184056.0 | 52445 | 236501.0 | https://transparentcalifornia.com/salaries/202... |
| 1038016 | Janice Ede | Director, Special Services | Kings | Hanford Joint Union High | 2020 | 169328 | 0 | 2778 | 172106.0 | 44475 | 216581.0 | https://transparentcalifornia.com/salaries/202... |
| 1038017 | Ward Whaley | Director, Human Resources | Kings | Hanford Joint Union High | 2020 | 169328 | 0 | 2778 | 172106.0 | 44475 | 216581.0 | https://transparentcalifornia.com/salaries/202... |
| 1038018 | Clark Parson | Principal,Incl Head Teacher | Kings | Hanford Joint Union High | 2020 | 164395 | 0 | 2778 | 167173.0 | 43655 | 210828.0 | https://transparentcalifornia.com/salaries/202... |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 13804724 | Katherine L Porter Howarth | Vuta Extra Duty Assign Level 6 | Tulare | Visalia Unified | 2024 | 104789.0 | 0.0 | 5951.0 | 110740.0 | 35601.0 | 146341.0 | CSV |
| 13805222 | Elizabeth N Rhoades | Vuta Extra Duty Assign Level 6 | Tulare | Visalia Unified | 2024 | 82428.0 | 0.0 | 5326.0 | 87754.0 | 31219.0 | 118973.0 | CSV |
| 13805223 | Elizabeth N Rhoades | Vuta Extra Duty Assign Level 6 | Tulare | Visalia Unified | 2024 | 82428.0 | 0.0 | 5326.0 | 87754.0 | 31219.0 | 118973.0 | CSV |
| 13806436 | Angel Mendoza | Utility Team Person | Tulare | Visalia Unified | 2024 | 44172.0 | 0.0 | 1816.0 | 45988.0 | 27511.0 | 73499.0 | CSV |
| 13806437 | Angel Mendoza | Utility Team Person | Tulare | Visalia Unified | 2024 | 44172.0 | 0.0 | 1816.0 | 45988.0 | 27511.0 | 73499.0 | CSV |
3333233 rows × 12 columns
In [9]:
# Definitely a duplicate, so I think there are just that many
data_no_index[(data_no_index['employee_name'] == 'Victor Rosa') & (data_no_index['year'] == 2020)]
Out[9]:
| employee_name | job_title | county | entity | year | regular_pay | overtime_pay | other_pay | total_pay | benefits | total_pay_and_benefits | url | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1038014 | Victor Rosa | Superintendent | Kings | Hanford Joint Union High | 2020 | 183699 | 0 | 4800 | 188499.0 | 48667 | 237166.0 | https://transparentcalifornia.com/salaries/202... |
| 1038664 | Victor Rosa | Superintendent | Kings | Hanford Joint Union High | 2020 | 183699 | 0 | 4800 | 188499.0 | 48667.0 | 237166.0 | https://transparentcalifornia.com/salaries/202... |
In [ ]:
# Now let's get rid of duplicates
print(data_no_index.shape)
data_no_index = data_no_index.drop_duplicates()
print(data_no_index.shape)
In [ ]:
# There are far too many job titles, so we'll add a column called job_class
# It will be Administrator, Teacher, or Support Staff
(13809125, 12) (12072439, 12)
In [11]:
data_no_index = data_no_index.reset_index(drop=True)
data_no_index.index += 1
data_no_index.head()
Out[11]:
| employee_name | job_title | county | entity | year | regular_pay | overtime_pay | other_pay | total_pay | benefits | total_pay_and_benefits | url | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Alysse B Castro | Superintendent | Alameda | Alameda County Office of Education | 2024 | 303683.71 | 0 | 0 | 303683.71 | 60546.96 | 364230.67 | https://transparentcalifornia.com/salaries/202... |
| 2 | Chaunise Powell | Sr Chief Of Student Services | Alameda | Alameda County Office of Education | 2024 | 275332.45 | 0 | 1200 | 276532.45 | 46705.72 | 323238.17 | https://transparentcalifornia.com/salaries/202... |
| 3 | Monica R Vaughan | Sr Chief Of Schools | Alameda | Alameda County Office of Education | 2024 | 258532.67 | 0 | 1200 | 259732.67 | 62919.54 | 322652.21 | https://transparentcalifornia.com/salaries/202... |
| 4 | Carmella Franco | Ce-Short Term Salaries | Alameda | Alameda County Office of Education | 2024 | 159238 | 0 | 159238 | 318476.0 | 0 | 318476.00 | https://transparentcalifornia.com/salaries/202... |
| 5 | Juwen Lam | Chief Of Accountability | Alameda | Alameda County Office of Education | 2024 | 201530.69 | 0 | 1200 | 202730.69 | 67623.96 | 270354.65 | https://transparentcalifornia.com/salaries/202... |
In [12]:
# Create a connection to the sqlite database
conn = sqlite3.connect('Data/sqlite_dbs/TheOneSource.db')
# Write the dataframe to the database
data_no_index.to_sql('salaries', con=conn, if_exists='replace', index=True)
# Close the connection
conn.close()
In [ ]: