A repository for the Regis University Data Science Practicum II project on Colorado K-12 Financial Transparency.
*Formal Warning: This project became a research method approach with little visualizations. Due to the extenuating limitations of the data set and extensive subject matter knowledge to penetrate, the limitations were not evident until the disparity of codes and data began to reveal itself.
Project Title: Colorado K-12 Financial Transparency – An Exploratory Data Science Comparison Across Schools
In this past academic year, my kindergartner son has brought home 5 different fundraisers and a quarterly scholastic book fair flyer. Unfortunately, I am unclear where the funding is going, what the funding is for, and how fundraisers feed into the school. These implications on the school and district fundraiser efforts need further analysis. Especially since school ends in a little over two months, I am curious to learn how expenses are handled. The primary research question is to address how financial expenditures are being spent across K through 12 schools in Colorado. An exploratory data science approach will be conducted to answer this research question. Overall, what are the school expenses that impact our children’s educational needs? These high level research questions may expand upon themselves as the exploratory data analysis (EDA) can lead to other areas of interest. As well as, the unknown world of what is contained within this data.
• Regression using supervised machine learning.
• Linear regression to find relationship(s) between different expenses across schools and/or districts.
• ANOVA analysis of variance on data.
• Possible KNN analysis on the clustering of different types of expenses to demonstrate possible clusters of similar or different data points.
• Data visualization(s) to show expenses of K-12 schools in Colorado.
• Data visualization(s) to conduct a comparison and contrast of data.
• Colorado K2 Financial Transparency: https://coloradok12financialtransparency.com/#/
• Complete dataset of all Colorado K12 districts and BOCES: https://s3.amazonaws.com/bb-pub-pipeline-production/home/circleci/repo/public/uploads/data_files/uploads/000/000/416/20200114030354-colorado-financial-public-data-pipeline-v-1-Run_1-export-source-lep-facts.csv20200114-2678-rqjqmn.csv?1578999834
• Colorado Department of Education- Financial Policies and Procedures Chart of Accounts: https://www.cde.state.co.us/cdefinance/fpp_coa1617
• Colorado BOCES Association: https://www.coloradoboces.org/
• Colorado Department of Education: http://www.cde.state.co.us/schoolview
• Colorado Department of Education- Chart of Accounts: 2019-20: http://www.cde.state.co.us/cdefinance/sfCOA.htm
• Data.Gov: https://data.colorado.gov/K-12/Colorado-Department-of-Education-s-School-View/tedy-p7a5
• Data.Gov: https://data.colorado.gov/browse?category=Education
• Financial Policies and Procedures Chart of Accounts: https://www.cde.state.co.us/cdefinance/fpp_coa1617
Original Goal: How are financial expenditures are being spent across K through 12 schools in Colorado?
Initially, my main concern was to learn why so many fundraisers were being conducted at my kindergartner’s elementary school. By the beginning of January 2020, we were halfway through the academic school year and already spent money on five different fundraisers. All of which, I did not where those funds were going and for whatever reasons they were meant for. This was a time before the pandemic of the coronavirus when fundraising was advocated. So, trying to find a means to understand financial expenses or expenditures across Colorado K through 12 schools in Colorado was my primary goal.
What Objective's did I meet?
I learned Colorado K-12 financial transparency is rather hazy and very ambiguous. In reflection, we learned Denver County 1, Jefferson County, Douglas County, and Adams 12 Five Star schools are amongst some of the most commonly used codes within the district and administrative unit categories in the financial chart of accounts. The top five used school codes used for financial funding are all within the city of Commerce City. This included school codes that belong to Adams City High School, Kearney Middle School, Adams City Middle School, Central Middle School and Rose Hill Elementary School. The top 5 expenditures by fund codes include the General fund, Governmental designated-purpose grants fund, charter school fund, food service find, and the pupil activity special revenue fund. It was evident that the General Fund is the most commonly used fund and there seems to some sort of a discrepancy from the vague usage of the General Fund. More importantly to note, the details on the usage of the Program codes within the financial chart of accounts shows that the value zero is the most commonly used value. This can be a bit questionable. Then at most, we learn that the object/source/balance code accounts for salaries, employee benefits, supplies, other purchased services, and travel, registration and entrance are the top most used codes.
In looking back at the Research Questions, did I come to some answers?
I wanted to expand on, what are the school expenses that impact our children’s educational needs? Could the type of staffing, teacher types, and number of students be an impact? What are the contributing factors of the various expenses? How do schools and districts compare to one another? What variables are impacting the data reported? Could federal and state funding not be enough?
Aside from the findings from the prior question, the amount cents spent value was an astronomical amount that gave very little value as to what really is spent across Colorado K-12 schools. I am left defeated with little to no answers as to where the fund really goes to and why fundraisers are vital to the education of my child.
Most of all financial codes and the usage of financial coding is very vague. This leads to controversy against the purpose of the Colorado K through 12 so called Financial Transparency data set and website. Which leads to the final question..
Did I meet the expectations of the "now" revealed question in this project?
Is there true transparency within this dataset from the Colorado K-12, BOCES, and Colorado Department of Education?
What really is transparent to us, our families, our children, our students, and future role models of society.
We looked at a number of visualizations and more than enough of my EDA markdown to write a research report. We compared my findings. I attempted to apply two Machine Learning Algorithms of Knn. However, with our challenges on the high categorical values, both machine learning algorithms proved challenging and a near unsuccess.
In fact, did we really succeed at our project goals? Or are we in fact left with more questions and uncertainty? We learned no information on fundraisers and how they feed into our schools. We learned there is funding of some sort, very little funding is spread out amongst a variety of needs, and most of it seems to be going to salaries and administrative duties that are currently put at a halt due to the coronavirus. Should we be questioning the financial transparency and state funding into public education?
Noted Lessons Learned: • I continue to learn and will always learn that exploratory data analysis takes a great amount of time to synthesize.
• Categorical data can be a big pain and heart wrenching circumstance. ( I think I cried about 3 times.)
• I learned to understand complex data, you have to immerse yourself into studying every detail of the financial codes. This is not easy and is very complex. You have to become a subject matter expert in financial material you will question and may never really understand..
• I also learned to really embrace the wisdom of K.I.S.S; keep it simple stupid!
• In final, I learned you may not get the answers you had hoped for and you may find yourself with more questions than what you began with. Thus, learning and future research is necessary.
• Last but not least, I learned financing, accounting, and codes related to expenses may not my forte. It is what it is.
This project and the data within this project began as an interesting aspect of my daily life and the current situation of events with the coronavirus. I learned being a stay-at-home mom, 24 hours a day, 7 days a week; with no vacation; became even more challenging when I was soon a teacher to two children who were remote learning, sharing my computer for all our schoolwork, and zoom sessions. Exhausting the Wi-Fi and internet connection to meet all our household electronic needs is somewhat a new and lesson to be learned quickly on the need to expand the internet speed. Let's just say our internet bill became a lot more expensive and so did our other utilities that were essential to get by our daily stay-at-home needs.
Getting back on subject, I found that the Colorado K-12 financial expenses are beyond my project expectations. I have gained a lot of respect for the time, patience, and concentration it takes for teachers to teach our children. On the other hand, I question how financial transparency is really transparent. Especially in these circumstances of closed school, jobless administrative staff, remote teachers, on-site overhead costs for operations such as electricity, water, or building operations have come to a halt. So where are the financial expenditures going and do salary and pay still occur normally? Should we question such matters.
In no bitter way am I targeting anything, I am only curious as I am challenged with my own teaching, parenting, and time management skills during this time. Overall, again I can respect the difficulties schools, teachers, and administrative deal with during the school year. I, too, worked in higher education and have always been a firm and avid believer of education. The thing is, when do we question public schooling and the lack in our system pertaining to the drop of high school graduates and student education rates? When do we question if our schools are failing our students or us? When do we ask for real financial transparency that tells us exactly what our schools are spending and how are our students' education benefiting or being impacted by such financial ramifications?
It may have not resulted in a huge accomplishment, but I would like to think it brings to light the the fact that there is a need for more clearer transparency in the financial aspect of our Colorado K-12 schools. Do we all need to be a high risk and high needs school to be given some funding from the state? Do we all have to consider other forms of schooling whether private, Montessori, religious, or paid education to get the funding needed for our students? These are questions we are left with and have little answers but to continue to research and investigate the needs of our students and future role models.
So, where do we go from here?
We continue to be more conscientious of our children, students, and our future leaders’ educations. We can pray that things work in their best interests and put our faith into a system that has been around for a great amount of our history. We can continue to ask questions, research, and try to find meaning or answers to our questions, concerns, and worries. All in all, we must put faith in our teachers and schools. We must trust the education of our students will be met based on those with a passion to teach our children and not for the financial purpose of it.
We must trust our schools fund raisers really do benefit the schools financially. We must do our best to support those fund raisers the best we can whether 4 or more times a year. Though, we must know that as our best effort, we can only do so much. Learning to fuel our children’s education in other ways and not only financially can be done through donations and other efforts in our area of interest. To end on a good note, we must put the most trust and faith in our children, students, and future leaders to embrace the circumstances of their current situation and learn as best as they can. This is entirely my own opinion so it should be taken lightly and not as a form of influence.
Again, as an attempt to end this project on a positive note. There is more to be done, more research and a lot more expenses and funding to bring to light. I am left with so many questions that only fuel my burning need of knowledge on the disparity of information and the want to reveal the true financial transparency on Colorado K-12 education. These are the last of my words here in my graduate degree requirements for the completion of my Master of Science in Data Science degree. Thank you for your patience.
About BOCES: https://www.coloradoboces.org/colorado-boces-association/about-boces/
Babel Number Formatting – source: http://babel.pocoo.org/en/latest/numbers.html
Blane, A. (2017, December 4). DataFrame to image. Retrieved from https://github.com/andrewblane/DataFrame_to_image/blob/master/DataFrame_to_image.ipynb
Colorado BOCES Association. (n.d). BOCES (Board of Cooperative Educational Services). Retrieved from https://www.coloradoboces.org/
Colorado Department of Education. (2016, July 1). Chart of Accounts: 2016-17. Copyright © 1999-2020 Colorado Department of Education. Retrieved from https://www.cde.state.co.us/cdefinance/fpp_coa1617
Colorado Department of Education. (2019, October 30). Chart of Accounts: 2019-20. Copyright © 1999-2020 Colorado Department of Education. Retrieved from http://www.cde.state.co.us/cdefinance/sfCOA.htm
Colorado Department of Education. (2019, June 13). Chart of Accounts: Archive. Copyright © 1999-2020 Colorado Department of Education. Retrieved from http://www.cde.state.co.us/cdefinance/sfcoa_archive
Colorado Department of Education. (2015, March 18). Data Pipeline Organization Codes – District/BOCES Codes. Retrieved from https://www.cde.state.co.us/datapipeline/org_dist-boces
Colorado Department of Education. (2019, August 21). Periodic Collections – December Finance . Retrieved from http://www.cde.state.co.us/datapipeline/per_december-finance
Colorado Department of Education. (2019, October 22). School View: School and District Data. Copyright © 1999-2020 Colorado Department of Education. Retrieved from http://www.cde.state.co.us/schoolview
Colorado K12 Financial Transparency. (Fiscal Year 2017 – 2018). Financial Transparency for Colorado Schools. Retrieved from https://coloradok12financialtransparency.com/#/
Data.Gov. (n.d.). Colorado Department of Education School View. Retrieved from https://data.colorado.gov/K-12/Colorado-Department-of-Education-s-School-View/tedy-p7a5
Data.Gov. (n.d.). Colorado Information marketplace. Retrieved from https://data.colorado.gov/browse?category=Education
DataFlair Team. (2019, July 10). Train and Test Set in Python Machine Learning – How to Split. Retrieved from https://data-flair.training/blogs/train-test-set-in-python-ml/
Featureranking.com (n.d.). Data Preparation for Machine Learning. Retrieved from https://www.featureranking.com/tutorials/machine-learning-tutorials/data-preparation-for-machine-learning/#2.2.2
Format numbers as currency in python – source: https://stackoverflow.com/questions/37579712/format-numbers-as-currency-in-python
Lane, A. (2017, December 4). Convert Pandas DataFrames to images using IMGKit. Retrieved from https://medium.com/@andy.lane/convert-pandas-dataframes-to-images-using-imgkit-5da7e5108d55
locale Internationalization services – source: https://docs.python.org/2/library/locale.html
Menon, U. (2019, January 18). What is one-hot encoding and when is it used in data science? Retrieved from https://www.quora.com/What-is-one-hot-encoding-and-when-is-it-used-in-data-science
Ng. P. (2010, April 23). K-nearest Neighbors (KNN) Classification Model. © 2020 Ritchie Ng. Retrieved from https://www.ritchieng.com/machine-learning-k-nearest-neighbors-knn/
Pathalk, M. (2020, January 6). Handling Categorical Data in Python. Retrieved from https://www.datacamp.com/community/tutorials/categorical-data
Sanjay.M. (2018, October 26). Machine Learning –– KNN using scikit-learn. Retrieved from https://towardsdatascience.com/knn-using-scikit-learn-c6bed765be75
Sarkar, D. (2018, January 6). Categorical Data: Strategies for working with discrete, categorical data. Retrieved from https://towardsdatascience.com/understanding-feature-engineering-part-2-categorical-data-f54324193e63
Scikit-learn: Machine Learning in Python, Pedregosa et al., JMLR 12, pp. 2825-2830, 2011. Sklearn.cluster.DBSCAN. Retrieved from https://scikit-learn.org/stable/modules/generated/sklearn.cluster.DBSCAN.html
Scikit-learn: Machine Learning in Python, Pedregosa et al., JMLR 12, pp. 2825-2830, 2011. Demo of DBSCAN clustering algorithm. Retrieved from https://scikit-learn.org/stable/auto_examples/cluster/plot_dbscan.html
Shaikh, R. (2018, November 8). Choosing the right Encoding method – Label vs OneHot Endoer. Retrieved from https://towardsdatascience.com/choosing-the-right-encoding-method-label-vs-onehot-encoder-a4434493149b
Sharma, M. (2018, November 2). Data Visualization using Seaborn. Retrieved from https://towardsdatascience.com/data-visualization-using-seaborn-fc24db95a850
Tiwari, A. (2019, October 19). Data Cleaning with Python — Categorical Variables. Retrieved from https://medium.com/analytics-vidhya/data-cleaning-with-python-categorical-variables-1a904761fa27
Waskom, M. (n.d.). Plotting with categorical data. © Copyright 2012-2020. Retrieved from https://seaborn.pydata.org/tutorial/categorical.html