I've been working on getting the report up and running using Metasys data instead of the data logged by my own systems. This involves converting the format of the Metasys data into the format accepted by the permanent database `TempAndCO2Log`. This process was lengthier than I anticipated it would be, but once I had figured it out I still had some obstacles remaining.
First, the system was having trouble indexing although the indices of the data table within the database and the indices of the table I wanted to add in looked identical. I was able to debug this successfully when I realized that I had to set the index of the new table to the room number because the database had the room number as the index (it just didn't look like it when it was printed).
Next, I got some ValueErrors like this one:
ValueError: could not convert string to float: '2020 Sept - AHS ZN-T OA-T & CO2.csv'
I was confused as to why I had any kind of data point with a .csv at the end of it, let alone something the program was trying to read as a float! Finally, I figured out that there was some descriptive information at the bottom of the original spreadsheet, that had somehow filtered its way into the data. In fact, the string above had been mistakenly read as a CO2 value!
After adding lines to delete the bottom couple of rows from the original file, I ran into another issue with the permanent database -- now that the faulty rows were in the database, it was still throwing errors! I tried running some SQL commands like this one:
`DELETE FROM TempAndCO2Log WHERE Timestamp='Metasys File'`
However, these commands didn't actually delete anything from the database -- I'm not quite sure why and will try to figure it out in the future. On the other hand, I was able to go into the database using the database browser we had and get rid of all the rows where the timestamp was set to 'Metasys File.' This fixed the problem in the short-term, but for the future, I still want to figure out why the SQL didn't work.
After that, I finally was able to get to the point where I was running generate_historical_report on the Metasys data! However, this is not a process free from errors. As the standard story goes, the little elves descended on my code at some point since July -- generate_historical_report seems to be having some data type/rounding issues, not only with the Metasys data, but with the old data that my systems had logged before (and from which I have successfully generated reports). I'm going to continue looking into this issue in the next few days and hopefully figure out what went wrong.
For more documentation on all of this, see the issue history on the GitHub repository.
A few weeks ago, I made a breakthrough! I was able to successfully use the .melt() and .pivot_tables() functions to effectively do a total rearranging of the data. I now have a rough version of the program that converts the data from the Metasys format into the format accepted by my systems. I'm currently working on testing a full report directly from the Metasys data -- more updates to come!
Also, I met with the recruits one or two times since the last time I've updated this blog -- they are now beginning the same Coursera courses that I completed during my independent study! :)