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

pip install sodapy

import pandas as pd
from sqlalchemy import create_engine
import requests
import json
from sodapy import Socrata

# # California Data

# +
#data collected from https://www.cde.ca.gov/ds/sd/sd/filesabd.asp
#https://www.oregon.gov/ode/reports-and-data/students/Pages/Student-Enrollment-Reports.aspx

url= "http://dq.cde.ca.gov/dataquest/dlfile/dlfile.aspx?cLevel=School&cYear=2019-20&cCat=Enrollment&cPage=filesenr.asp"
ca_enrollment_data = pd.read_table(url)
ca_enrollment_data

# -



# +

#modify demo to include ethnicity as columns
ca_enrollment_race = ca_enrollment_data.pivot_table(values='ENR_TOTAL', index='COUNTY', columns='ETHNIC', aggfunc='sum')
ca_enrollment_race.head()
# -

#rename the ethnicity columns
ca_enrollment_race_2 = ca_enrollment_race.rename(columns = {0:'OTHER',
                                    1:'AMERICAN_INDIAN',
                                    2:'ASIAN',
                                    3:'PACIFIC_ISLANDER',
                                    4:'FILIPINO',
                                    5:'LATINO',
                                    6:'AFRICAN_AMERICAN',
                                    7:'WHITE',
                                    9:'MULTI_RACE'})
ca_enrollment_race_2.reset_index(inplace=True)
ca_enrollment_race_2.head()

# +

ca_enrollment_race_2['OTHER_TOTAL'] = ca_enrollment_race_2['OTHER'] + ca_enrollment_race_2['MULTI_RACE']
ca_enrollment_race_2['ASIAN_TOTAL'] = ca_enrollment_race_2['ASIAN'] + ca_enrollment_race_2['FILIPINO']
ca_enrollment_race_2.head(1)
ca_clean = ca_enrollment_race_2[['COUNTY', 'AMERICAN_INDIAN', 'PACIFIC_ISLANDER', 'LATINO','AFRICAN_AMERICAN','WHITE','OTHER_TOTAL','ASIAN_TOTAL']].copy()

#replace NAN with zero
ca_clean.fillna(0,inplace = True)
ca_clean.head()

# Add column of CA
ca_clean['STATE'] = "CA"
ca_clean

ca_clean.rename(
  columns={
    'ASIANS_TOTAL' : 'ASIAN',
    'OTHER_TOTAL' : 'OTHER/MULTI'
  },
  inplace=True
)
ca_clean

ca_clean.reset_index(inplace=False)
#ca_clean.drop(["ETHNIC"], axis=1)
#ca_clean.set_index('index')
#ca_clean.rename_axis(None)
# -

list(ca_clean.columns)

ca_clean_df  =  pd.DataFrame(ca_clean.to_records())
ca_clean_df

ca_clean_df.drop(columns='index',inplace =True)
ca_clean_df = ca_clean_df.rename( columns= {'ASIAN_TOTAL':'ASIAN',
                                           'OTHER/MULTI':'OTHER_MULTI'})
ca_clean_df

# # Oregon Data

# +
url= "https://www.oregon.gov/ode/reports-and-data/students/Documents/fallmembershipreport_20192020.xlsx"

or_enrollment_data = pd.read_excel(url, sheet_name = "District (19-20)")
or_enrollment_data
list(or_enrollment_data.columns)
or_clean = or_enrollment_data[['County', 
                   '2019-20 American Indian/Alaska Native ', 
                   '2019-20 Asian ', 
                   '2019-20 Native Hawaiian/ Pacific Islander', 
                   '2019-20 Black/African American ', 
                   '2019-20 Hispanic/ Latino', 
                   '2019-20 White ', 
                   '2019-20 Multiracial ']].copy()
or_clean = or_clean.groupby(or_clean['County']).sum()
# -

or_clean['STATE'] = "OR"
or_clean

or_clean_df  =  pd.DataFrame(or_clean.to_records())
or_clean_df

or_clean_df.rename(
  columns={'County':'COUNTY',
 '2019-20 American Indian/Alaska Native ':'AMERICAN_INDIAN',
 '2019-20 Native Hawaiian/ Pacific Islander':'PACIFIC_ISLANDER',
 '2019-20 Hispanic/ Latino':'LATINO',
 '2019-20 Black/African American ':'AFRICAN_AMERICAN',
 '2019-20 White ':'WHITE',
 '2019-20 Multiracial ':'OTHER_MULTI',
 '2019-20 Asian ':'ASIAN',
  },
  inplace=True
)
or_clean_df

# ## Washington Data

# +
# Unauthenticated client only works with public data sets. Note 'None'
# in place of application token, and no username or password:
#client = Socrata("data.wa.gov", None)

# Example authenticated client (needed for non-public datasets):
client = Socrata("data.wa.gov",
                  apitoken,
                  username=username1,
                  password=<PASSWORD>)

# First 2000 results, returned as JSON from API / converted to Python list of
# dictionaries by sodapy.
results = client.get("gtd3-scga", limit=20000)

# Convert to pandas DataFrame
wa_data_df = pd.DataFrame.from_records(results)
wa_data_df

# -






# +
wa_temp = wa_data_df.loc[wa_data_df['organizationlevel'] != 'State'].copy()
wa_temp.fillna(0, inplace=True)
wa_temp['STATE'] = 'WA'
wa_clean = wa_temp[['county',
                    'american_indian_alaskan_native',
                    'asian',
                    'black_african_american',
                    'hispanic_latino_of_any_race',
                    'native_hawaiian_other_pacific',
                    'two_or_more_races',
                    'white']].copy()



wa_clean['asian'] = wa_clean['asian'].astype(str).astype(int)
wa_clean['american_indian_alaskan_native'] = wa_clean['american_indian_alaskan_native'].astype(str).astype(int)
wa_clean['black_african_american'] = wa_clean['black_african_american'].astype(str).astype(int)
wa_clean['hispanic_latino_of_any_race'] = wa_clean['hispanic_latino_of_any_race'].astype(str).astype(int)
wa_clean['native_hawaiian_other_pacific'] = wa_clean['native_hawaiian_other_pacific'].astype(str).astype(int)
wa_clean['two_or_more_races'] = wa_clean['two_or_more_races'].astype(str).astype(int)
wa_clean['white'] = wa_clean['white'].astype(str).astype(int)


wa_clean = wa_clean.groupby(wa_clean['county']).sum()


# +
wa_clean['STATE'] = 'WA'

# wa_clean = wa_temp[['county',
#                     'american_indian_alaskan_native',
#                     'asian',
#                     'black_african_american',
#                     'hispanic_latino_of_any_race',
#                     'native_hawaiian_other_pacific',
#                     'two_or_more_races',
#                     'white']].copy()

# #wa_clean = wa_clean.groupby(wa_clean['county']).sum()
wa_clean
# -



# +

wa_clean.rename(
  columns={'county':'COUNTY',
 'american_indian_alaskan_native':'AMERICAN_INDIAN',
 'native_hawaiian_other_pacific':'PACIFIC_ISLANDER',
 'hispanic_latino_of_any_race':'LATINO',
 'black_african_american':'AFRICAN_AMERICAN',
 '2019-20 White ':'WHITE',
 'two_or_more_races':'OTHER_MULTI',
 'asian':'ASIAN',
  },
  inplace=True
)
wa_clean
# -

wa_clean['STATE'] = 'WA'
wa_clean

wa_clean['AMERICAN_INDIAN'] = round(wa_clean['AMERICAN_INDIAN'],1)
wa_clean

# +
wa_clean_df  =  pd.DataFrame(wa_clean.to_records())

wa_clean_df = wa_clean_df.rename(columns={'county':'COUNTY',
                                         'white':'WHITE'})
wa_clean_df
# -

temp = pd.concat([ca_clean_df, wa_clean_df])

clean_df = pd.concat([temp, or_clean_df])

clean_df



rds_connection_string = "postgres:postgres@localhost:5432/ELT_Project"
engine = create_engine(f'postgresql://{rds_connection_string}')

#Check for tables
engine.table_names()

clean_df.to_sql(name='ELT', con=engine, if_exists='append', index=False) #check other options for if_exists

pd.read_sql_query('SELECT * FROM "ELT"', con=engine).head()



# +
#https://pandas.pydata.org/pandas-docs/stable/user_guide/missing_data.html

#df.insert(0, 'New_ID', range(0, 0 + len(df)))
#df

# -



StarcoderdataJupyterScriptsDedupFiltered