RIDE Uniform Charter of Accounts financial analysis

7/5/2018 Updated for FY2017 data epq

In [ ]:
import re
import numpy as np
import scipy as sc
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib inline

Set max_rows option for display

In [ ]:
pd.get_option("display.max_rows")
In [ ]:
pd.set_option("display.max_rows",1000)
pd.get_option("display.max_rows")

The following code to reads the RI Uniform Charter of Accounts data from the Rhode Island Department of Education Consolidated District Financial Database

See:

http://www.ride.ri.gov/FundingFinance/SchoolDistrictFinancialData/UniformChartofAccounts.aspx#18211078-statewide-consolidated-district-financial-databases

Read the FY2017 (2016-2017 school year) data: Expenditures Excluding Capital and Debt Service

Source file is:

http://media.ride.ri.gov/construction/93-All-Expenditure-Account-Strings-with-Descriptions-no-421-422-FY17.xlsx

In [ ]:
fy17 = pd.read_csv("../RIDE/93-All-Expenditure-Account-Strings-with-Descriptions-no-421-422-FY17.csv")
fy17['Year'] = 2017
print(fy17.shape)
fy17.columns
In [ ]:
fy17rs = fy17.loc[(fy17['Obj']==51110.0) & (fy17['Dist No']==90.0)]
print(fy17rs.shape)
fy17rs

Read the FY2016 (2015-2016 school year) data: Expenditures Excluding Capital and Debt Service

Source file is:

http://media.ride.ri.gov/construction/93-All-Expenditure-Account-Strings-with-Descriptions-no-421-422-FY16.xlsx

In [ ]:
fy16 = pd.read_csv("../RIDE/93-All-Expenditure-Account-Strings-with-Descriptions-no-421-422-FY16.csv")
fy16['Year'] = 2016
print(fy16.shape)
fy16.columns

Read the FY2015 (2014-2015 school year) data: Expenditures Excluding Capital and Debt Service

Source file is:

http://media.ride.ri.gov/construction/93-All-Expenditure-Account-Strings-with-Descriptions-no-421-422-FY15.xlsx

In [ ]:
fy15 = pd.read_csv("../RIDE/93-All-Expenditure-Account-Strings-with-Descriptions-no-421-422-FY15.csv")
fy15['Year'] = 2015
print(fy15.shape)
fy15.columns

Read the FY2014 (2013-2014 school year) data: Expenditures Excluding Capital and Debt Service

Source file is:

http://media.ride.ri.gov/construction/93-All-Expenditure-Account-Strings-with-Descriptions-no-421-422-081415.xlsx

In [ ]:
fy14 = pd.read_csv("../RIDE/93-All-Expenditure-Account-Strings-with-Descriptions-no-421-422-081415.csv")
fy14['Year'] = 2014
print(fy14.shape)
fy14.columns

Read the FY2013 (2012-2013 school year) data: Expenditures Excluding Capital and Debt Service

Source file is:

http://media.ride.ri.gov/construction/93-All-Expenditure-Account-Strings-with-Descriptions-no-421-422-FY13.xlsx

In [ ]:
fy13 = pd.read_csv("../RIDE/93-All-Expenditure-Account-Strings-with-Descriptions-no-421-422-FY13.csv")
fy13['Year'] = 2013
print(fy13.shape)
fy13.columns

Read the FY2012 (2011-2012 school year) data: Expenditures Excluding Capital and Debt Service

Source file is:

http://media.ride.ri.gov/construction/93-All-Expenditure-Account-Strings-with-Descriptions-no-421-422.xlsx

In [ ]:
fy12 = pd.read_csv("../RIDE/93-All-Expenditure-Account-Strings-with-Descriptions-no-421-422.csv")
fy12['Year'] = 2012
print(fy12.shape)
fy12.columns

Read the FY2011 (2010-2011 school year) data: Expenditures Excluding Capital and Debt Service

Source file is:

http://media.ride.ri.gov/construction/93-All-Expenditure-Account-Strings-with-Descriptions-no-421-422-FY11.xlsx

In [ ]:
fy11 = pd.read_csv("../RIDE/93-All-Expenditure-Account-Strings-with-Descriptions-no-421-422-FY11.csv")
fy11['Year'] = 2011
print(fy11.shape)
fy11.columns

Read the FY2010 (2009-2010 school year) data: Expenditures Excluding Capital and Debt Service

Note: No Budget information in this year

Source file is:

http://www.ride.ri.gov/Portals/0/Uploads/Documents/UCOA/2009-2010-Consolidated-UCOA-Database/FY-10-UCOA-Database-Expenditures-Excluding-Capital-and-Debt-Service.xlsx

In [ ]:
fy10 = pd.read_csv("../RIDE/FY-10-UCOA-Database-Expenditures-Excluding-Capital-and-Debt-Service.csv")
fy10['Year'] = 2010
print(fy10.shape)
fy10.columns
In [ ]:
allexp = pd.concat([fy16,fy15,fy14,fy13,fy12,fy11,fy10])
print(allexp.shape)

list Districts

In [ ]:
District = allexp.groupby(['Dist No','District Name']).count()
print(District.shape)
print(District.columns)
District
In [ ]:
allexp_EG = allexp.loc[allexp['Dist No']==90.0]
print(allexp_EG.shape)
Tot = allexp_EG.groupby(['Year']).sum()[['Budget','Actual']]
Tot['Variance'] = Tot['Actual'] - Tot['Budget']
Tot

Level 1: - Fund (4 character)

In [ ]:
Fund = allexp_EG.groupby(['Fund','Fund Description']).sum()[['Budget','Actual']]
Fund['Variance'] = Fund['Actual'] - Fund['Budget']
print(Fund.shape)
print(Fund.columns)
Fund

Subfund? (4 char)

Level 2: Location (5 char)

In [ ]:
Location = allexp_EG.groupby(['Loc','Location Description']).sum()[['Budget','Actual']]
Location['Variance'] = Location['Actual'] - Location['Budget']
print(Location.shape)
print(Location.columns)
Location

Level 3: Function (5 char)

In [ ]:
Function = allexp_EG.groupby(['Func','Function Description']).sum()[['Budget','Actual']]
Function['Variance'] = Function['Actual'] - Function['Budget']
print(Function.shape)
print(Function.columns)
Function

Level 4: Program (2 char)

In [ ]:
Program = allexp_EG.groupby(['Prog','Program Description']).sum()[['Actual','Budget']]
Program['Variance'] = Program['Budget'] - Program['Actual']
print(Program.shape)
print(Program.columns)
Program

Level 5: Subject (4 char)

In [ ]:
Subject = allexp_EG.groupby(['Sub','Subject Description']).sum()[['Budget','Actual']]
Subject['Variance'] = Subject['Actual'] - Subject['Budget']
print(Subject.shape)
print(Subject.columns)
Subject

Level 6: Job class (4 char)

In [ ]:
Job_class = allexp_EG.groupby(['JC','Job Class Description']).sum()[['Budget','Actual']]
Job_class['Variance'] = Job_class['Actual'] - Job_class['Budget']
print(Job_class.shape)
print(Job_class.columns)
Job_class

Object (5 char)

In [ ]:
Object = allexp_EG.groupby(['Obj','Object Description']).sum()[['Budget','Actual']]
Object['Variance'] = Object['Actual'] - Object['Budget']
print(Object.shape)
print(Object.columns)
Object
In [ ]:
Job_class = allexp_EG.groupby(['JC','Job Class Description','Obj','Object Description',]).sum()[['Budget','Actual']]
Job_class['Variance'] = Job_class['Actual'] - Job_class['Budget']
print(Job_class.shape)
print(Job_class.columns)
Job_class
In [ ]:
Job_class = allexp_EG[['JC','Job Class Description','Obj','Object Description','Budget','Actual']]
Job_class['Variance'] = Job_class['Actual'] - Job_class['Budget']
print(Job_class.shape)
print(Job_class.columns)
Job_class

Read the FY2016 (2015-2016 school year) data: Expenditures: Capital and Debt Service Expenditures

Source file is:

http://www.ride.ri.gov/Portals/0/Uploads/Documents/UCOA/2015-2016-Consolidated/FY16-All-Expenditure-Account-Strings-with-Descriptions-421-422.xlsx

In [ ]:
fy16cap = pd.read_csv("../RIDE/FY16-All-Expenditure-Account-Strings-with-Descriptions-421-422.csv")
fy16cap['Year'] = 2016
print(fy16cap.shape)
fy16cap.columns

Read the FY2015 (2014-2015 school year) data: Expenditures: Capital and Debt Service Expenditures

Source file is:

http://www.ride.ri.gov/Portals/0/Uploads/Documents/UCOA/2014-2015-Consolidated/93-All-Expenditure-Account-Strings-with-Descriptions-with-budget-421-422-only.xlsx

In [ ]:
fy15cap = pd.read_csv("../RIDE/93-All-Expenditure-Account-Strings-with-Descriptions-with-budget-421-422-only.csv")
fy15cap['Year'] = 2015
print(fy15cap.shape)
fy15cap.columns

Read the FY2014 (2013-2014 school year) data: Expenditures: Capital and Debt Service Expenditures

Source file is:

http://www.ride.ri.gov/Portals/0/Uploads/Documents/UCOA/93-All-Expenditure-Account-Strings-with-Descriptions-421-422-081415.xlsx

In [ ]:
fy14cap = pd.read_csv("../RIDE/93-All-Expenditure-Account-Strings-with-Descriptions-421-422-081415.csv")
fy14cap['Year'] = 2014
print(fy14cap.shape)
fy14cap.columns

Read the FY2013 (2012-2013 school year) data: Expenditures: Capital and Debt Service Expenditures

Source file is:

http://www.ride.ri.gov/Portals/0/Uploads/Documents/UCOA/2012-2013-Consolidated-Data/93%20All%20Expenditure%20Account%20Strings%20with%20Descriptions%20421-422%20ONLY.xlsx

In [ ]:
fy13cap = pd.read_csv("../RIDE/93-All-Expenditure-Account-Strings-with-Descriptions-421-422-ONLY.csv")
fy13cap['Year'] = 2013
print(fy13cap.shape)
fy13cap.columns

Read the FY2012 (2011-2012 school year) data: Expenditures: Capital and Debt Service Expenditures

Source file is:

http://www.ride.ri.gov/Portals/0/Uploads/Documents/UCOA/2011-2012-Consolidated/93-All-Expenditure-Account-Strings-with-Descriptions-421-422.xlsx

In [ ]:
fy12cap = pd.read_csv("../RIDE/93-All-Expenditure-Account-Strings-with-Descriptions-421-422-2011-2012.csv")
fy12cap['Year'] = 2012
print(fy12cap.shape)
fy12cap.columns

Read the FY2011 (2010-2011 school year) data: Expenditures: Capital and Debt Service Expenditures

Source file is:

http://www.ride.ri.gov/Portals/0/Uploads/Documents/UCOA/2010-2011-Consolidated-UCOA-Databases/FY11-93-All-Expenditure-Account-Strings-with-Descriptions-421-422.xlsx

In [ ]:
fy11cap = pd.read_csv("../RIDE/FY11-93-All-Expenditure-Account-Strings-with-Descriptions-421-422.csv")
fy11cap['Year'] = 2011
print(fy11cap.shape)
fy11cap.columns

Read the FY2010 (2009-2010 school year) data: Expenditures: Capital and Debt Service Expenditures

Note: no Budget information this year

Source file is:

http://www.ride.ri.gov/Portals/0/Uploads/Documents/UCOA/2009-2010-Consolidated-UCOA-Database/FY-10-UCOA-Database-Expenditures-Capital-and-Debt-Service-Expenditures.xlsx

In [ ]:
fy10cap = pd.read_csv("../RIDE/FY-10-UCOA-Database-Expenditures-Capital-and-Debt-Service-Expenditures.csv")
fy10cap['Year'] = 2010
print(fy10cap.shape)
fy10cap.columns
In [ ]:
allcap = pd.concat([fy16cap,fy15cap,fy14cap,fy13cap,fy12cap,fy11cap,fy10cap])
print(allcap.shape)
In [ ]:
allcap_EG = allcap.loc[allcap['Dist No']==90.0]
print(allcap_EG.shape)
Tot = allcap_EG.groupby(['Year']).sum()[['Budget','Actual']]
Tot['Variance'] = Tot['Actual'] - Tot['Budget']
Tot
In [ ]:
fy16rev = pd.read_csv("../RIDE/FY16-All-Revenue-Account-Strings-with-Descriptions.csv")
fy16rev['Year'] = 2016
print(fy16rev.shape)
fy16rev.columns
In [ ]:
fy15rev = pd.read_csv("../RIDE/94-All-Revenue-Account-Strings-with-Descriptions-with-budget.csv")
fy15rev['Year'] = 2015
print(fy15rev.shape)
fy15rev.columns
In [ ]:
fy14rev = pd.read_csv("../RIDE/94-All-Revenue-Account-Strings-with-Descriptions-081415.csv")
fy14rev['Year'] = 2014
print(fy14rev.shape)
fy14rev.columns
In [ ]:
fy13rev = pd.read_csv("../RIDE/94-All-Revenue-Account-Strings-with-Descriptions-FY13.csv")
fy13rev['Year'] = 2013
print(fy13rev.shape)
fy13rev.columns

Read the FY2012 (2011-2012 school year) UCOA data: Revenue

Source file is:

http://www.ride.ri.gov/Portals/0/Uploads/Documents/UCOA/FY12-Revenue.xlsx

In [ ]:
fy12rev = pd.read_csv("../RIDE/FY12-Revenue.csv")
fy12rev['Year'] = 2012
print(fy12rev.shape)
fy12rev.columns
In [ ]:
fy11rev = pd.read_csv("../RIDE/FY11-94-All-Revenue-Account-Strings-with-Descriptions.csv")
fy11rev['Year'] = 2011
print(fy11rev.shape)
fy11rev.columns

Read the FY2010 (2009-2010 school year) UCOA data: Revenue

Note: no Budget data for this year

Source file is:

http://www.ride.ri.gov/Portals/0/Uploads/Documents/UCOA/FY10-Revenue.xlsx

In [ ]:
fy10rev = pd.read_csv("../RIDE/FY10-Revenue.csv")
fy10rev['Year'] = 2010
fy10rev['District ID'] = fy10rev['Dist No']     #differs from 2011-2016
print(fy10rev.shape)
fy10rev.columns
In [ ]:
allrev = pd.concat([fy16rev,fy15rev,fy14rev,fy13rev,fy12rev,fy11rev,fy10rev])
allrev['Dist No'] = allrev['District ID']            #this is the name used on the expense side
print(allrev.shape)
In [ ]:
allrev_EG = allrev.loc[allrev['Dist No']==90.0]
print(allrev_EG.shape)
Tot = allrev_EG.groupby(['Year']).sum()[['Budget','Actual']]
Tot['Variance'] = Tot['Actual'] - Tot['Budget']
Tot