ANALYSIS REPORT

AQI - Covid Cases Data

Pranav Modh

Links:

• Jupyter Notebook With Code for Analysis
• My Github Profile Page
• My Linkedin

Libraries And Requirements

• I utilized PySpark, a highly efficient and scalable library, to work with the Covid-19 cases data, which is a large dataset of approximately 5GB.

• Additionally, I employed the Pandas library to manipulate the AQI dataset.

• To visually represent the data, I utilized the popular data visualization libraries, Matplotlib and Seaborn.

• To perform data preprocessing, I leveraged various SQL functions provided by PySpark.

AQI Data Loading

• I successfully acquired the AQI data from multiple URLs, with each URL containing data for a single year, ranging from 1980 to 2021.

• Utilizing Python code, I efficiently downloaded and unzipped all the data, and subsequently loaded each of the CSVs into a Pandas data Frame.

• Through merging all the CSVs, I created a comprehensive data Frame that incorporates the AQI data from 1980 to 2021, streamlining data analysis and facilitating the extraction of valuable insights.

Data Exploration

The following steps were executed to effectively explore the AQI data:

• To facilitate ease of analysis and consistency during the merging process, all columns were renamed to lowercase with no spaces.

• The AQI data of Canada was present within the dataframe; however, as our focus was solely on USA AQI data, all rows with "CC" as the state code were removed.

• The datatypes of the columns were updated to their appropriate format to reduce memory consumption and improve the functionality when working with categorical data.

• To address inconsistencies in case usage for string data values such as county name and state name, all string columns were converted to lowercase.

• Missing data was identified and represented as NaN by standardizing all occurrences of "missing", "NA", "null", and "unknown" values in the columns.

COVID-19 Case Surveillance Public Use Data with Geography

• Due to the substantial size of the COVID-19 Case Surveillance Public Use Data with Geography, it was necessary to utilize Py Spark to load the data into a PySpark dataframe.

• A Py Spark session was initiated and the data URL, which contains the cases.csv file, was passed as an argument to efficiently load the data.

Data Exploration

The following data exploration steps were executed:

• The "case_month" column was converted from a string data type to datetime to facilitate further analysis.
• Upon conversion, two new columns "year" and "month" were created.
• To maintain consistency in string data, all string data type columns were converted to lowercase.
• As the data only contained state codes, a mapping of USA state codes to names was retrieved from Google and a new column with state names was added to the dataframe.
• For state codes that were not found in the downloaded mapping, the state names were manually searched and added to the dataframe.
• The number of null values in each column was then assessed.

• Rows without county name or state name were removed as these were crucial for the analysis.
• To standardize the column names, the "state", "county", and "date" column names were updated.
• The "age group" column contained unknown values, which were filled with the most frequently occurring age group in the dataset.
• Relationships between the "hospitalization" column and other columns were explored to train a random forest model that could predict missing values in this column.
• Using the scikit-learn library, a random forest model was trained to predict missing values in the "hospitalization" column.
• The trained model was then utilized to predict the missing values in the "hospitalization" column.

Hospitalization relation with other columns

• When conducting an analysis to examine the relationship between hospitalization rates and other variables, it is crucial to consider the impact that missing data can have on the results.

• The presence of missing or null values can potentially skew the results and lead to inaccuracies in the conclusion drawn.

• Therefore, it is essential to remove or treat these missing values appropriately to ensure that the findings of the analysis are accurate and valid. Failure to account for missing data can result in biased results that may not accurately reflect the true relationship between the variables being studied. Hence, we have not included missing data.

• Hospitalization relation with different age groups:

• The bar graph depicts the normalized count of individuals who were admitted to the hospital and their respective age groups.

• The data analysis shows that individuals within the age range of 18 to 49 years have the lowest hospitalization rates.

• The hospitalization count for individuals between the ages of 0 to 17 years was observed to be minimal.

• As expected, the highest rate of hospitalization was observed for individuals aged 65 years and above.

• Hospitalization relation with ICU:

• As per the data we can say that most of the hospitalized people were not in the ICU.

• Hospitalization relation with death rate:

• Hospitalization relation with underlying conditions:

• Hospitalization relation with date of covid started:

Air Quality index and impact of lockdown

To perform the analysis on the impact of lockdown on air quality, the following steps were undertaken:

• A new column named "lockdown" was created to determine the AQI index at the time of lockdown.

• The lockdown period in the USA was between March and August of 2020, so the "lockdown" column was populated with "yes" for all data within this period, and "no" for all other data.

• To eliminate potential bias, only data from the months of March to August was retained in the analysis, as this is the period in which the lockdown was implemented.

• The data was then grouped by "county name" and "state name".

• To determine the impact of the lockdown on the AQI index, the t-test method was used to calculate the p-value for the data before and during the lockdown period. If the p-value was less than 0.05, it was concluded that there was a statistically significant impact on the AQI index.

• The results were sorted in ascending order to identify the counties that were most affected.

• The top ten affected counties and states, as well as the lowest ten affected, were then plotted for visualization.

• Here is the visual representation of top five and lowest five affected counties and states:

Top five

Lowest 5

CONCLUSION

The AQI is a measure of the quality of the air we breathe, with higher AQI values indicating poorer air quality. The visual representations are used to track changes in AQI over time and identify any trends or patterns for different states and counties. For example, if the line on the graph for a specific state or county is trending upwards, it would indicate that the AQI is getting worse over time for that location, while a downward trend would indicate improvement in air quality.

Is there a correlation between Air Quality Index (AQI) and COVID-19 hospitalization rate?

The correlation between Air Quality Index (AQI) and COVID-19 hospitalization rate is not a straightforward relationship and is dependent on data provided and other factors, such as population density, health infrastructure, and individual health behaviors, can also play a significant role in COVID-19 hospitalization rates.

The following steps were carried out to investigate the correlation between AQI and COVID-19 hospitalization rate:

• Data loading and pre-processing: I loaded and stored both the AQI and hospitalization data into separate variables. To ensure that both datasets have a consistent time frame, I filtered the AQI data based on the dates present in the hospitalization data. Additionally, I made sure that both datasets have similar column names for easy join operation.

• Data cleaning and preparation: I created a new column, "avg_aqi", in the AQI data to store the mean AQI value for each month. I also created new columns, "month" and "year", from the date column. For the analysis, I only needed columns "aqi", "month", "year", "county_name", and "state_name" from the AQI data, and "hosp_yn", "month", "year", "county_name", and "state_name" from the hospitalization data.

• Joining the datasets: I joined both datasets using the inner join method, based on common columns.

• Data analysis: To find the hospitalization rate, I grouped the data by month and year, and counted the number of total cases and number of hosp_yn with the value "yes". Using this information, I calculated the hospitalization rate for each month and year.
• Correlation analysis: I used Pearson's correlation method to determine the correlation between the average AQI value and hospitalization rate.

CONCLUSION

• After analyzing the correlation between AQI and hospitalization rate, the correlation coefficient was 0.05. This suggests that there is a very weak relationship between AQI and hospitalization rate. This means that an increase or decrease in AQI does not have a strong impact on the hospitalization rate.

• However, it is important to keep in mind that correlation does not imply causation. There could be other factors that are affecting the hospitalization rate and AQI, and it's possible that the weak correlation between AQI and hospitalization rate is simply due to chance.

• Therefore, it is crucial to consider additional data and perform a more comprehensive analysis to determine if there is a real relationship between AQI and hospitalization rate. This would involve exploring the relationship between AQI and other factors that could impact hospitalization, such as demographic data, medical histories, and environmental variables.

• In conclusion, based on the correlation coefficient of 0.05, I would suggest that AQI is not a strong predictor of hospitalization rate. Further analysis is needed to determine if there is a real relationship between AQI and hospitalization rate and what factors may be contributing to any observed relationship.

Are there any confounding factors you should control for?

Yes, when analyzing the relationship between COVID-19 cases data and AQI (Air Quality Index) data in the United States, it is important to control for confounding factors that may influence both the COVID-19 cases and AQI. Some of these confounding factors include:

• Demographics: Age, and socioeconomic status can impact both COVID-19 cases and AQI levels.

• Climate: Weather patterns and temperature can influence both COVID-19 transmission rates and air pollution levels.

• Mobility: Travel patterns, population density, and mobility trends can impact both COVID-19 transmission rates and AQI levels.

• Public health measures: Measures such as lockdowns, school closures, and social distancing can impact both COVID-19 transmission rates and AQI levels.

• Healthcare capacity: The availability and quality of healthcare systems can impact both COVID-19 cases and AQI levels.

• It is important to consider and control for these confounding factors when analyzing the relationship between COVID-19 cases and AQI. This can be done by using regression models or other statistical techniques to isolate the effect of each variable and control for the influence of other variables.

How would you present the results visually? How do you label your axes?

• To present the results visually, I have used several types of graphs and charts, including bar charts and line graphs. These types of visualizations allow us to effectively communicate and explore the relationship between AQI and Covid-19 data.

• First, I have created a line graph showing the most impactful county-state pairs on the AQI index by lockdown. The y-axis represents the AQI index, while the x-axis represents the change with each year. This graph provides a clear visual representation of how AQI was affected by lockdowns in various locations.

• Second, I have used bar graphs to show the relationship between different columns of Covid-19 data and its hospitalization column. The y-axis represents the total count of hospitalized patients. While the x-axis represents the Covid-19 data columns, such as age group, sex, race, and ICU admission. The bars with distinct colors show whether it was hospitalized or not. These graphs provide a clear understanding of the relation that these columns have on the hospitalization.

• Finally, to ensure the results are easily interpretable and actionable, I have labeled the axes clearly and succinctly. The labels clearly describe what each axis represents, making it easy for the reader to understand the results and draw insights from the data.

• Overall, my approach to presenting the results visually was to use clear, concise, and easily interpretable graphs and charts that effectively communicate the insights and findings of my analysis.

How did you treat missing data?

• In order to effectively analyze the data, it was important to address the presence of missing values in the columns. The missing data was systematically identified and standardized to NaN, by standardizing all instances of "missing", "NA", "null", and "unknown" values in the columns. This process ensured consistent representation of missing data and facilitated the identification and treatment of missing values in the analysis.
• Understanding the importance of missing data: I understand that missing data can have a significant impact on the results of any analysis, and it is crucial to treat it carefully.
• Applying appropriate methods: For the Covid-19 cases data, I treated missing values in the columns "death_yn", "icu_yn", "age_group", "sex", and "race" by using the mode of the column values. I also used a Random Forest Classifier to predict values for the "hosp_yn" column.
• Essential data preservation: I ensured that essential information, such as county and state names, was not lost by removing missing values in these columns. I also removed missing date data for the same reason.
• AQI data analysis: For the AQI data, I did not find any missing values and did not need to perform any manipulations.

Thank You