# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3 (ipykernel)
#     language: python
#     name: python3
# ---

import pandas as pd

# read in the job data from Glassdoor
job_df = pd.read_csv('glassdoor_jobs_with_index.csv')
job_df.head()

# read in the lat & lng data and keep only the needed info
lnglat = pd.read_csv('uscities.csv') #csv downloaded from Simple Maps
lnglat = lnglat[['city','state_id','lat','lng']]
lnglat.head()

# split the location to city and state columns
job_df[['city','state_id']] = job_df.location.str.split(',',expand=True)
job_df['state_id'] = job_df['state_id'].str.strip()
job_df['state_id'][0]


# +
# merge job information with lat & lng
merged_df = pd.merge(job_df, lnglat, on=['city', 'state_id'],how='left')

# put NYC's lat&lng as default locations for all remote jobs
merged_df['lat'] = merged_df['lat'].fillna('40.6943')
merged_df['lng'] = merged_df['lng'].fillna('-73.9249')
merged_df
# -

merged_df.to_csv('job_data.csv', index=False)







StarcoderdataJupyterScriptsDedupFiltered