# This Python 3 environment comes with many helpful analytics libraries installed
# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python
# For example, here's several helpful packages to load

import numpy as np # linear algebra
import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)

# Input data files are available in the read-only "../input/" directory
# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory

import os
for dirname, _, filenames in os.walk('/kaggle/input'):
    for filename in filenames:
        print(os.path.join(dirname, filename))

# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using "Save & Run All" 
# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session
"""
# Importing Packages
"""
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

import plotly.express as px    
import plotly.graph_objects as go

%matplotlib inline 
"""
# Here is the data
"""
educ_spend = pd.read_csv("/kaggle/input/school-spending-since-1990csv/School_Spending_Since_1990.csv", header = None)
educ_spend = educ_spend.transpose()
educ_spend[0][0]= "Year"
header = educ_spend.iloc[0] #First row is the header
educ_spend = educ_spend[1:] #The data minus the header row
educ_spend.columns = header #Set the header
educ_spend.tail(10)
"""
# Data Cleaning
"""
variables = ['Year', 'City funds (estimated)', 'State aid', 'Federal aid', 'Private and non-governmental aid', 'Total for all funds', 'Total Commitment to DOE', 'Total enrollment', 'Pedagogic staff', 'Pupils to Pedagogic Staff'  ]
doe = educ_spend[variables] # Millions of 2017 dollars
doe.tail()
doe.columns = ['Year', 'City Funds 1', 'City Funds 2', 'State aid 1', 'State aid 2', 'State aid 3', 'State aid 4', 'Federal aid 1', 'Federal aid 2', 'Private', 'Private and non-governmental aid 2', 'Private and non-governmental aid percentage', 'total for all funds 1', 'total for all funds 2', 'Total Commitment to DOE 1','Total Commitment to DOE 2', 'Total enrollment', 'Pedagogic Staff', 'Pupils to Pedagogic Staff']
keep = ['Year', 'City Funds 1', 'State aid 1', 'Federal aid 1' ,'Private', 'Total Commitment to DOE 1','Total enrollment', 'Pedagogic Staff', 'total for all funds 1']
doe = doe[keep]
doe = doe.replace('[\$,]', '', regex=True).astype(int)
"""
# Cleaned Data
"""
doe.head()
"""
# Visualizations
"""
fig = go.Figure()
fig.add_trace(go.Scatter( x=doe['Year'], y= doe['total for all funds 1'],
         mode='lines+markers'))

fig.update_layout(title='New York City School Spending Per Year in Millions of 2017 Dollars', xaxis_title='Year',
                   yaxis_title='Millions of 2017 Dollars')
"""
This line graph shows the overall trend of two numerical variables (dollars spent and years). It shows the amount of dollars spent in NYC DOE by year.
"""
fig = go.Figure()

fig.add_trace(go.Scatter( x=doe['Year'], y= doe['City Funds 1'],
         mode='lines+markers', name= 'City Aid'))

fig.add_trace(go.Scatter( x=doe['Year'], y= doe['State aid 1'],
         mode='lines+markers', name = "State Aid"))

fig.add_trace(go.Scatter( x=doe['Year'], y= doe['Federal aid 1'],
         mode='lines+markers', name = "Federal Aid"))

fig.update_layout(title='New York City School Spending Per Year in Millions of 2017 Dollars', xaxis_title='Year',
                   yaxis_title='Millions of 2017 Dollars')
"""
This multiple line graph shows the overall trend of two numerical variables (dollars and years) and one categorical variable (City and State aid).
"""
doe['ratio'] = doe['Total enrollment'] / doe['Pedagogic Staff']
fig = go.Figure()
fig.add_trace(go.Scatter( x=doe['Year'], y= doe['ratio'],
         mode='lines+markers'))
              
fig.update_layout(title='Student/Staff Ratios per Year', xaxis_title='Year',
                   yaxis_title='Studen/Staff Ratio')
"""
This line graph shows the student/staff ratio trend over time. The higher the ratio, the less pedagogic staff in comparison to students. The ratio has been decrasing overall. In 2010 the ration incresed, meaning there was a decrease in pegagogic staff in comparison to the student population.
"""
doe['Spending Per Student'] = (doe['total for all funds 1'] / doe['Total enrollment']) * 1000000
fig = go.Figure()
fig.add_trace(go.Scatter( x=doe['Year'], y= doe['Spending Per Student'],
         mode='lines+markers'))
              
fig.update_layout(title='Spending Per Student per Year', xaxis_title='Year',
                   yaxis_title='2017 Dollars')
"""
This graph illustrates the dollar amount spent per student in NYC over the years, all in 2017 dollars.
"""