I have been provided the temperature database from the portal from where I have extracted the data related to global temperature and the temperature of New Delhi. I have analyzed and compared the temperature around the globle with New Delhi by selecting and extracting key data from the database.
- Selecting city and country from the database “city_list”.
- Extracting the City level data from the database “city_data” and export to CSV file.
- Extracting the global temperature from the database “global_data” and export to CSV file.
- SQL: To extract the data from the database
- Python: For calculating moving average and plotting line chart
- ANACONDA – Jupyter Notebook: For writing python code, making observations and writing the project
Step 1: Extraction of data from the database
- To check which countries and cities are available in the database.SELECT *
FROM city_list WHERE Country='India' AND City='New Delhi';
- To select data from the City database.SELECT avg_temp,year,city,country
FROM city_data WHERE city='New Delhi';
- I observed there is a column called avg_temp which is same in both city_data and global_data. I want to change the schema so I joined both the tables and changed the column names in both the databases.ALTER TABLE city_data
RENAME COLUMN avg_temp to CAT;ALTER TABLE global_data
RENAME COLUMN avg_temp to GAT;
- I have joined the two tables using JOIN also called as INNER JOIN as avg_temp is same in both the tables.SELECT city_data.CAT, global_data.GAT, global_data.year
FROM global_data JOIN city_data ON global_data.year = City_data.year WHERE city=’New Delhi’ Country=’India’;
Now, I have got an option to download a CSV file. I downloaded file as “results.csv”.
Step 2: Using Python for creating line charts
I have imported some python libraries here such as Numpy, Pandas (Matplotlib as well as Pyplot). I have written the code using Jupyter Notebook.
- The chart of the temperatures of New Delhi and Global Temperature shows that the difference between the average temperature of New Delhi and the World is very big.
- The global data caused a slighty inclined result so I have plotted another graph for it which is the first graph.
- The first graph shows the global temperature is increating constantly over the years by around 0.1 degree centigrade.
- On the second graph I have observed that New Delhi has temperatures greater than the global average.
- If I draw a line of best fit through the line of New Delhi, I can see a similar rise in temperature between New Delhi and the Global average over time. To verify this claim I used data.tail(10) to show me this trend continuing in the most recent years leading up to 2013.
- New Delhi is a very hot city compared to most other cities in the world. From my research as a micro-study, New Delhi is surrounded by industrial factories and is nearby another city called Agra which also features large industrial facilities. Between them, they are two of the biggest industrial hubs in the world. I believe this may partly be the reason for the higher temperatures locally.
From my research as a macro-study, India itself lies nearer to the Equator so like most cities between the equator and the tropic of capricon or cancer it will have higher temperatures than the global average. Another reason for this is the area of these regions latitudally is very small compared to the rest of the globe so the temperatures fall as we move away from the equator. The Global Average Temperature is quite low compared to equatorial regions like New Delhi which can vary in temperature approximately between 8.4 to 9.6 degrees centigrade higher.
- The temperature of the Globe is constantly rising from observing the second graph.
- X-Axis: Years.
- Y-Axis: Temperature(°C).
- Different colors of lines for city and global average.
- Use of Matplotlib library for visualization.
- Applied Moving Average (MA) on city_data in order to get a relatively smooth line.
- Defined functions for easier to understand coding.
- Saved all of the code in .ipynb files (Jupyter Notebook) for later reference or revisions.
- Change Column Name: https://www.1keydata.com/sql/alter-table-rename-column.html
- Joining the tables: http://www.dofactory.com/sql/join
- Calculation command for Moving Average in Python: http://www.learndatasci.com/python-finance-part-3-moving-average-tradingstrategy/
- Parameters for .rolling() https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.rolling.html
# Importing the important Libraries import numpy as np import pandas as pd # for loading data into the notebook from matplotlib import pyplot as plt #for making line chart
# Importing the extracted Data Set data = pd.read_csv( "results.csv" )
218 rows × 3 columns
This should define a function for the calculation of moving averages in order to get smooth graph. And data.info should help show the data more clearly.In :
# function that calculates the MOVING AVERAGE def moving_avg (mA_range, data_input): output = data_input.rolling(window = mA_range, on = "cat" ).mean().dropna() return output
# Function Calling with the range of Moving Average mA_value = 150 chart_moving_avg = moving_avg(mA_value, data)
# Drawing the graph: Global Temperature plt.plot(chart_moving_avg [ 'year' ], chart_moving_avg [ 'gat' ], label = 'Global' ) plt.legend() plt.xlabel ( "Years" ) plt.ylabel ( "Temperature (°C)" ) plt.title ( "Global Average Temperature" ) plt.show ()
Above is the output. Next, I have analysed the global data separately in order to check and distinguish it from combined data of New Delhi and Global Average temperatures.
Now combined with New Delhi data:In :
# Drawing the graph: New Delhi and Global Temperature # Introducing this line in the previous code above the “global_data plot command” plt.plot(chart_moving_avg ['year'], chart_moving_avg ['cat'], label = 'New Delhi') plt.plot(chart_moving_avg ['year'], chart_moving_avg ['gat'], label = 'Global') plt.legend() plt.xlabel ( "Years" ) plt.ylabel ( "Temperature (°C)" ) plt.title ( "New Delhi and Global Temperature" ) plt.show ()
Above is the resulting graph. I have analysed my results further by adding:In :
In [ ]: