<jupyter_start><jupyter_text>Project 1 - Exploring Weather Trends Get the dataTo get the data first I had to see the name of the countrys available in the dataset, I also included as a curiosity a COUNT to see the number of cities for each country.In the next picture you can see the query used to get the list of the cities. ![SQL to get the list of the countries and number of cities for each country](img/countries.png) Once I have checked that the country "Netherlands" exist and it has minimum one city then I can get the data from that city.Then I do another query to get the data from the city where the country is "Netherlands" by doing a INNER JOIN with the tables "city_list" and "city_data". Once I have this join I do a UNION to also add the global data from the table "global_data". Finally I order by year and city.In the next image you can see the query created to do this. ![SQL to get the temperature for Amsterdam and the Global temperature](img/year_temperature.png) Once I have tested that the query works I dowload the data in a CSV and then I import this data as a pandas dataframe.<jupyter_code>import pandas as pd

#Read the csv file as a pandas dataframe
weather_data_original = pd.read_csv('weather_data.csv')<jupyter_output><empty_output><jupyter_text>Once we have read the data and stored in a pandas dataframe we can then start the first plot to see the difference between Amsterdam and the global average temperature.First we will check the data by looking the head of the dataframe.<jupyter_code>weather_data_original.head<jupyter_output><empty_output><jupyter_text>As we can see there is only one column called "city" and the data in in long format, meaning that we have the data from 'Amsterdam' and 'Global' in long format. In this case, I will do a pivot to put the data from each city in individual columns.<jupyter_code>#Pivot the dataframe by year
weather_data = weather_data_original.pivot(index = 'year', columns='city', values='avg_temp')
weather_data.head<jupyter_output><empty_output><jupyter_text>Analyze the data Once we have the data of each city in a column we can do the first plot using the matlplotlib library.<jupyter_code>import matplotlib.pyplot as plt

plt.plot(weather_data.index, weather_data.Amsterdam, label="Avg Temp Amsterdam")
plt.plot(weather_data.index, weather_data.Global, label="Avg Temp Global")

plt.xlabel("Year")
plt.ylabel("Avg Temperature")
plt.title("Average Temperature")
plt.legend()
plt.show()<jupyter_output><empty_output><jupyter_text>But in the previous plot the data doesn't look really nice, so I will apply a SMA (Simple Moving Average) of 10 years to see if the line chart gets more smooth.<jupyter_code>#Calculate the moving average with a window size of 10
weather_data['Amsterdam_10'] = weather_data.Amsterdam.rolling(window=10).mean()
weather_data['Global_10'] = weather_data.Global.rolling(window=10).mean()<jupyter_output><empty_output><jupyter_text>Once I have the moving average for each city I will create again the line chart.<jupyter_code>plt.plot(weather_data.index, weather_data.Amsterdam_10, label="Mov Avg Temp Amsterdam")
plt.plot(weather_data.index, weather_data.Global_10, label="Mov Avg Temp Global")

plt.xlabel("Year")
plt.ylabel("Avg Temperature")
plt.title("Moving Average Temperature (window size 10)")
plt.legend()
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07166220636336604, 'fraction_numerical': 0.005649717514124294, 'mean_word_length': 5.755020080321285, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '386566', 'license': 'MIT', 'n_tokens_mistral': 1020, 'n_tokens_neox': 951, 'n_words': 434, 'path': 'weather_analisis.ipynb', 'repo_name': 'robertgv/Exploring_Weather_Trends', 'size': '3363'}