Air Quality Index - Build Your Data Skills by Analyzing AQI of Different Indian Cities

[Joseph]

Instructions Sheet : Follow the instructions given below to do your very first data analysis and understand more about Air Pollution and AQI.

Setup

We will setup the sheet before we start analysing the data.

---

Step 1 : Right Click on the Data tab and Duplicate the sheet This is done as a safe practice to avoid making changes to original data set.

Step 2 : Click on the + button on the left corner and add a new tab

Step 3 : Rename it to “Analysis”

---

First Analysis - Finding Month Wise AQI Trend of Delhi Over 5 Years

We will find the Month Wise AQI Trend of Delhi Over the Years with the help of Pivot Tables and Graphs

---

Step 1 : Click on “Insert” from Top Menu

Step 2 : Click on “Pivot Table”

Step 3 : In “Create pivot table” click on the “Select data range” icon on the right. ( Do not Click on Create Button Yet)

Step 4 : Enter the range - ”Copy of Data!A1:L11510” ( Do not Click on Create Button Yet)

Step 5 : Tick mark “Existing sheet” in “Insert to” and click on “Select data range” icon on the right

Step 6 : Enter the location - “Analysis!A1”

Step 7 : Click on “ Create”

Step 8 : In the “Pivot table editor”, go to “Rows” and add “Month”

Step 9 : Un- check the “Show total” checkbox

Step 10 : Go to “Columns” add “Year”

Step 11 : Un- check the “Show total” checkbox

Step 12 : Go to “Values” add “AQI”

Step 13 : In “AQI”, go to “Summarise by”, and select “AVERAGE”

Step 15 : Go to “Filters” add “City”

Step 16 : In the “City”, go to “ Showing all items” and Select only “Delhi”

## The Table is showing the Month Wise Average AQI of Delhi over 5 years

---

Visualization

Step 1 : In the same “Analysis” sheet, we will create the graph for the table.

Step 2 : Click on “Insert” from Top Menu and click on “Chart”

Step 3 : In the “Chart Editor” tab on the right, select “Line chart” as “Chart type”

Step 4 : In “Data Range’’, click on the data range icon to select data range

Step 5 : Select “A2:F14” as the data range and click “OK”

Step 6 : Scroll down and tick mark “Use row 2 as headers”

Step 7 : Go to “Customise” tab in “Chart Editor” and click on “Chart and Axis Title”

Step 8 : Select “Chart title” from the dropdown

Step 9 : In the “Title Text”, type “ Month Wise AQI Trend of Delhi From 2016 to 2020”

Step 10 : Select “Horizontal axis title” from the “Chart and Axis Title” dropdown

Step 11 : In the ”Title Text”, type “Months”

Step 12 : Select “Vertical axis title” from the “Chart and Axis Title” dropdown

In the ”Title Text”, type “AQI”

You have a graph showing Month Wise AQI Trend of Delhi From 2016 to 2020. Observe the graph and share your findings in the group

---

---