UTSA Datathon 2024 - The Fannypacks

First Analysis of USDA_PDP_AnalyticalResults.csv

We discovered through the PDP online database search that missing values for confirmation 1 & 2 are to be expected. Therefore, we did not remove those records from our dataset moving towards data profiling and new fields. We also consulted a mentor. We believe our analysis will be of other values and these missing variables will not invalidate our data.

Data Conversion and Normalization Process:

In our dataset, we are measuring concentrations using two different units: parts per million (ppm) and parts per trillion (ppt). To ensure consistency in our analysis, we converted all concentrations to the same unit, specifically parts per trillion (ppt). This was done because working with a single unit allows for more straightforward and accurate comparisons.

To convert concentrations from parts per million to parts per trillion, we simply multiplied by one million. This is because one part per million (ppm) equals one million parts per trillion (ppt). For example, if we have a concentration of 2 ppm, converting this to ppt gives:

2 ppm×1,000,000 =2,000,000 ppt

Once all values were expressed in parts per trillion, we applied a natural logarithm (ln) transformation to the data. The reason we took the natural log is twofold:

Normalization of the data: The raw concentration data can vary widely, often by several orders of magnitude, leading to highly skewed data. When skewed data is present, it can be problematic for many statistical analyses assuming normally distributed data. Applying the natural log compresses large values more than smaller ones, helping to bring the data closer to a normal distribution, which makes it more suitable for further statistical testing.
Stabilizing variance: The natural log also helps stabilize the variance in our data. In many cases, data with large ranges of values tends to have increasing variability. Taking the logarithm helps to equalize this variability, allowing for more consistent and reliable statistical results.

By converting all measurements to PPT and applying the natural log transformation, we’ve prepared the data in a way that allows us to perform robust statistical analyses and draw more meaningful conclusions.

Insights in Excel and Tableau

After this transformation, we began creating pivot table summarizations of the concentration data by state. We discovered that Alaska, Nevada, New Mexico, and Vermont do not have any records of pesticide samples.

However, after making some insights, we realized that we were required to identify the highest concentrated pesticide in each state to generate our final dataset for our USA map. Therefore, we backtracked and abandoned our previous pivot table.

Before creating a new pivot table with both state and pesticide separation for concentration, we began subsetting the SampleID column into its parts (Date, Site codes, etc). We also began cleaning the EPA Tolerance column by properly separating it into its parts. This mainly consisted of removing the character values from the column in order to make it numerical. We tried to maintain the FDA action level and No Tolerance result as separate, new columns.

At the same time, we began experimenting with visualization of the required maps in Tableau. Using the older pivot table, we learned how to properly display our 46 represented state/provinces on a color map. However, our team had to break up as night came and did not operate with all members until the morning.

We then had to web-scrape data from JourneyNorth to generate our second dataset for our second USA map. This dataset simply contains a sum of sightings by state. We decided to maintain the year of the observations in our dataset in case we wanted to work with it later.

The process of web-scraping began by using the sample code provided in the Supplemental Guide to construct our own Beautiful Soup Python program.

After designing the first draft, we iterated on our design until our file scraped the JourneyNorth site for adult monarch butterfly sightings from 1997 to 2022.

Using python, we combined and reformatted the results scraped from JourneyNorth to create one final dataset to take summary statistics from. With these summary statistics, we could generate a the second required map, a frequency map in Tableau.

Challenges

We did not have enough time to successfully create a color map for step 2 -- separating the most concentrated pesticide per state. Instead, we created a summary by state map for the natural log of concentration. This makes for an interesting holistic visualization that made some inferential insights possible.

We also did not fully explore time series analysis, despite preparing our datasets to do so. If we had more time, we would absolutely spend it on statistical analysis of pesticides by year and monarch sightings by year. Instead, we used data representing the summary of all years from 1997 to 2023.