Data Collection, Cleaning, & Normalization

Cancer rates across the United States were obtained through an NIH dataset. The datawas relatively clean but some edits were made for analysis. Symbols that existed in some columns were converted to nan values, the index column was removed, and the “County” column was broken into four separate columns: State, County, SEER Registry Number, and NPCR Registry Number. Additionally, all the numerical columns that were imported as strings were removed and converted to numeric values for analysis.

In the histogram below ‘Average Annual Count’ shows a few hundred observations having average annual count of more than 2500, which are possibly outliers. In order to confirm whether these observations are outliers, a subset from cancer data with an average annual count of more than 2500 was created. A large number of such observations are from California, New York and so on. All other variables were normal. Therefore, these variables were not removed as they could contain valuable information in analysis.

Air quality scores were obtained from the Environmental Protection Agency for each county in the United States. This data was also relatively clean but edits were made for the purpose of analysis.

In order to join this dataset with other datasets, the FIPS county and state codes were added to this data frame. To do this, each county string was cleaned to match with the county string in the FIPS county code data frame so that the county and state codes could be easily added. There were no null values that needed to be removed, although outliers were present and discussed below.

Next, the numerical values were normalized into proportions. Since the Air Quality Index was not recorded for the same amount of days for every county, the “Good”, “Moderate”, “Unclean”, etc days columns needed to be divided by the number of days AQI was recorded (“Days with AQI”), so that these records could be compared to each other. This also applied to the “Days CO2”, “Days O3” etc. columns, in which the pollutant mentioned was the primary pollutant for the number of days specified. These columns were added to the data frame as “Previouscolumnname_Norm”. For example: “Good Days_Norm”.

When checking for outliers in the numerical data of the air dataset, many outliers were found by using boxplots. The 'Good Days_Norm', 'Moderate Days_Norm', ‘Unhealthy for Sensitive Groups Days_Norm’, Unhealthy Days_Norm”, ‘Very Unhealthy Days_Norm’, ‘Hazardous Days_Norm’ and 'Max AQI' all showed outliers, but for each column except ‘Max AQI” these outliers were left and deemed “extremes” for each column. However, as the “Max AQI columns contained values above 500, which is the max AQI score, these records were completely removed from the data frame (Figure 2).

Water quality data for each county was collected through data from the Center for Disease Control. This dataset was relatively clean, but edits were made for the purpose of analysis. The “Location” column was split into a “county” and a “state” column. Additionally, columns such as “geoAbbreviation” and “geoID” were redundant and only one was kept. The water quality mean was also binned into 3 categories: “No pollution detected”, “detected but no harm”, and “detected and harmful.” After cleaning, the “result” data frame was used for analysis and contained only 6 columns with pertinent information for analysis. Overall, there were no missing values found after checking each column with .isna(), value_counts and unique() functions in the original dataset during Part 1 data processing stage.

For the water quality dataset, there is only one numeric column: ‘value’ (level of Arsenic concentration). According to the CDC’s documents, lowering the minimum contaminant load (MCL) from 50 to 10 ppb statistically reduces bladder and lung cancer mortality and morbidity by 37-56 cancers a year in the U.S. (EPA 2001b). Therefore, outliers can not be examined and defined only by applying boxplot function (left in Figure 3). Though there are some points out side of the IQR box, but they should not be considered outliers, because there are many zeros in the dataset and these zeros cause the IQR lie at the bottom. Therefore, these points should be considered normal points within a reasonable range. As a second way to test for outliers, ‘Values’ were binned into three groups, and a histogram created to show the frequency of each group. The results of the two figures indicate that there were no outliers in the water quality dataset.

Finally, some values in the dataset were binned to assist with analysis. As mentioned before, the level of Arsenic concentration (water quality indicator) was binned into three groups: “No pollution detected”, “detected but no harm”, and “detected and harmful” by considering the CDC official guide (right in Figure 3). For examining this new binned column, “sum([the binned column]).isna())” code was applied, showing a result of “0”. Therefore, there were no missing values binned.

Hypothesis 1

“We expect that as air quality gets worse, the cancer rates in each county will increase. We also expect this effect to be most significant among lung cancer rates.”

The overall trend in the United States shows that as the median AQI score increases (air quality gets worse) the cancer rates also increase. However, there are some unique outliers that show low cancer rates in counties with very high AQI scores. From these results, it is possible that type of pollutant might matter when examining cancer rates realted to air quality scores, as well as the population of the county. For example, the two-circled counties have similar air quality, but their average lung cancer counts are very different. It turns out the one at the bottom is Tulare County, which is ranked the sixth most ozone-polluted county in the US. So people are more likely to get lung cancer. The one at the top is Los Angeles County. Additionally, the reason that LA county seems to have higher average lung cancer counts is LA County has 20 times more population than that of Tulare county. If a normalized lung cancer rate is calculated by dividing the cancer counts by the population, lung cancer count per person for Tulare County is 0.01% higher.

In contrast with this theory however, the following is a bar plot that compares the number of days with particulate matter less than 10 micrometers but above 2.5 micrometers, number of days with particulate matter less than 2.5 micrometers, and the age adjusted cancer incidence rate per 1,000,000 cases, for each state. The figure shows that even for large variations in the number of days with particulate matter smaller than 10 or 2.5 micrometers, the age adjusted incidence rate does not fluctuate much. It seems that the cancer rate is relatively the same across multiple states.

However, a possible problem is that some states did not collect AQI data for every day of the year. For example, some states collected data only 160 days out of 365 days. Because of this, another bar plot was created to compare median AQI and age adjusted cancer incidence rate per 1,000,000 cases. The plot does not show any noticeable patterns.