Read in and prepare the data for storing on current template files

[shumeraa]

Before you start coding, please remember:

• Write Readable Code: Prioritize code readability and simplicity over cleverness.
• Comment Thoughtfully: Add comments to explain why something is done, not just what is done.
• Keep Functions Focused: Ensure that functions do one thing and do it well.
• Handle Errors Gracefully: Write code to gracefully handle potential errors or exceptions.
• Regularly commit your work to version control, providing clear messages and documentation of your progress.
• The directions here are a starting point, covering the key stuff we've got to have. As you dig in, you'll probably spot other important things to add – go for it!

Objective:

• Develop a Python script to read data from multiple Excel files and store it in a structured format that will be ready for integration with the SQLite database once available.

Before you start:

• I recommend you use the library Pandas for data manipulation, but any other similar library should work
• Read the Trip and Trip Leader object descriptions under the "Resources" title below to understand the data and the formats
• Each of the 30 trip leader submits one of these excel files, this is the file format you will be working with: https://uflorida.sharepoint.com/:x:/r/teams/SolarDroneDesignTeam-RealWorldEngineering/_layouts/15/Doc.aspx?sourcedoc=%7B07645BA3-89A9-4C13-9529-3A72A13C9489%7D&file=Prefs%20Template.xlsx&action=default&mobileredirect=true
• On the first page of this file, you will be concerned with the date, the trip name, and the preference rating (from 0 to 5)
• On the same file, there is a second page (called Trip Leader Information) that you can switch to at the bottom

Writing the Script:

• Extract the relevant information from both pages of the "Prefs Template" Excel document linked to above that is relevant to the Trip Leader and Trip classes according to the documentation (not all of it is going to be relevant)
• Handle different data types and formats, ensuring consistency across files.
• Validate the data to ensure it meets the necessary criteria (e.g., checking for missing values, ensuring rankings are within the allowed range).
• Serialize the structured data into a format suitable for storage and later retrieval (e.g., JSON, CSV, or pickle). Flag missing/wrongly formatted data with values such as NA
• Write tests to confirm that data from Excel files is read, validated, cleaned, and serialized correctly.
• Test the script with a variety of Excel files to ensure robustness.
• Consider edge cases and potential data anomalies that could occur in the Excel files.
• Create a detailed log that records the instances of missing data, including the file name, row number, column name, and a brief description.

Resources:

• Trip Leader Object description: https://uflorida.sharepoint.com/:w:/r/teams/SolarDroneDesignTeam-RealWorldEngineering/_layouts/15/Doc.aspx?sourcedoc=%7B5B3694D7-DBD7-4AE9-9558-0434F38C8683%7D&file=Trip%20Leader%20Object%20Outline.docx&action=default&mobileredirect=true
• Trip Object description: https://uflorida.sharepoint.com/:w:/r/teams/SolarDroneDesignTeam-RealWorldEngineering/_layouts/15/Doc.aspx?sourcedoc=%7B273CA465-698F-46E9-806A-AD74765759D4%7D&file=Trip%20Object%20Outline.docx&action=default&mobileredirect=true
• https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html

[lmdulick]

currently working on the issue

[lmdulick]

pushed ExcelFilter.py, Trip.py, and TripLeader.py into Lauren's-Branch

[ramawama]

pushed ExcelFilter.py, Trip.py, and TripLeader.py into Lauren's-Branch

Hello work looks great, just a few suggestions: make sure to follow github naming conventions when creating branches. We utilize feature branching so we refrain from naming branches with our names in it and name them based on the feature we are working on.

Also I'm not sure if you were at the meeting, but we've decided to use sql tables rather than classes for tripleaders / trips, but no need to delete them in case they are needed in the future.

If you had wanted to test the read excel functions, you can just pass in the prefs template sheet and if you just print a pandas df it will print with all its rows and columns after your data manipulation.

Let me know if you have any questions.

[lmdulick]

update: changed the new branch to 'Read-in-and-store-the-data' and linked it to this issue tab; additionally I'll go ahead and delete the trip.py and tripLeader.py files from this branch but I'll keep a copy of them in case we need the classes in the future