<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Homework 8
Step1:  1) What is the average renewable freshwater resource?
Step2:  2) What is the average withdrawl rate in 2014?
Step3:  3) Which are the 5 countries with most/least renewable freshwater (total, 2014)?
Step4:  4) How much freshwater is there per 100,000 inhabitants for each country? TOP5 with most, TOP5 with least.
Step5:  5) Compared between 2002 and 2014, how did water withdrawl change?
Step6:  6) 5 with biggest increase in withdrawl, 5 with biggest decrease in withdrawl
Step7:  7) Scatter plot
Step8:  8) Scatter plot
Step9:  9) Scatter plot
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
import matplotlib.pyplot as plt
% matplotlib inline
df = pd.read_csv('water_data_class.csv', encoding='latin-1')
df
# only countries with more than 25 million inhabitants and those who have values in every column (2002 that one with most non-values)
big_ones = df[df['population'] > 25000000]
notnull = df[df['annual freshwater withdrawl [%] 2002'].notnull()]
selection = df[(df['population'] > 25000000) & (df['annual freshwater withdrawl [%] 2002'].notnull()) & (df['annual freshwater withdrawl [%] 2002'] != 0)]
selection
Explanation: Homework 8: Dataset 1: Water
End of explanation
selection['total renewable freshwater resources [billion cubic meters] 2014'].describe()
Explanation: 1) What is the average renewable freshwater resource?
End of explanation
selection['annual freshwater withdrawl [%] 2014'].describe()
# @ TA values over 100% indicate that on top of renewable resources, water of non-renewable sources is withdrawn, 
# as described in the WorldBank metadata for the original datasets
Explanation: 2) What is the average withdrawl rate in 2014?
End of explanation
selection.sort_values('total renewable freshwater resources [billion cubic meters] 2014', ascending = True).head(5)
selection.sort_values('total renewable freshwater resources [billion cubic meters] 2014', ascending = False).head(5)
Explanation: 3) Which are the 5 countries with most/least renewable freshwater (total, 2014)?
End of explanation
#inserting column with population converted to population in 100,000 inhabitants
population_hundret = selection['population']/100000
selection.insert(3,"population in 100,000 inhabitants", population_hundret)
selection
# putting water and population of 100,000 inhabitants into relation
water_per_hundret = (selection['total renewable freshwater resources [billion cubic meters] 2014']/population_hundret)*1000
selection.insert(6, "Renewable freshwater resource per 100,000 inhabitants [million cubic meters]", water_per_hundret)
# sorting for countries with most water
selection.sort_values(by='Renewable freshwater resource per 100,000 inhabitants [million cubic meters]', ascending = False).head(5)
#plotting those with most
plt.style.use('ggplot')
#does not print sorted bars
#selection.sort_values(by='Renewable freshwater resource per 100,000 inhabitants [million cubic meters]', ascending = False).head(5).plot(kind='barh', x='country', y='total renewable freshwater resources [billion cubic meters] 2014', legend=False)
# sorting for countries with least water
selection.sort_values(by='Renewable freshwater resource per 100,000 inhabitants [million cubic meters]').head(5)
#plotting those with least
selection.sort_values(by='Renewable freshwater resource per 100,000 inhabitants [million cubic meters]').head(5).plot(kind="barh", x ="country", y ="Renewable freshwater resource per 100,000 inhabitants [million cubic meters]", legend=False)
Explanation: 4) How much freshwater is there per 100,000 inhabitants for each country? TOP5 with most, TOP5 with least.
End of explanation
selection.columns
withdrawl = selection[['country','annual freshwater withdrawl [%] 2002','annual freshwater withdrawl [%] 2014']]
withdrawl_diff=selection['annual freshwater withdrawl [%] 2014'] - selection['annual freshwater withdrawl [%] 2002']
withdrawl.insert(3, 'Change in withdrawl rate', withdrawl_diff)
withdrawl.head()
Explanation: 5) Compared between 2002 and 2014, how did water withdrawl change?
End of explanation
withdrawl.sort_values(by='Change in withdrawl rate').head(5)
withdrawl.sort_values(by='Change in withdrawl rate').tail(5)
Explanation: 6) 5 with biggest increase in withdrawl, 5 with biggest decrease in withdrawl
End of explanation
selection.sort_values(by='population in 100,000 inhabitants', ascending= False).head(10).plot(kind='scatter', x='population in 100,000 inhabitants', y='annual freshwater withdrawl [%] 2014')
#would be nice to put the country's name next to each dot, no idea how to do that unfortunately. Couldn't google useful things
selection.sort_values(by='population in 100,000 inhabitants').head(10).plot(kind='scatter', x='population in 100,000 inhabitants', y='annual freshwater withdrawl [%] 2014')
#would be nice to put the country's name next to each dot, no idea how to do that unfortunately. Couldn't google useful things
Explanation: 7) Scatter plot: freshwater vs population for 10 most populated vs 10 least populated
End of explanation
#those with most water
selection.sort_values(by='total renewable freshwater resources [billion cubic meters] 2014', ascending=False).head(10).plot(kind='scatter', x='population in 100,000 inhabitants', y='annual freshwater withdrawl [%] 2014')
#those with least water
selection.sort_values(by='total renewable freshwater resources [billion cubic meters] 2014').head(10).plot(kind='scatter', x='population in 100,000 inhabitants', y='annual freshwater withdrawl [%] 2014')
Explanation: 8) Scatter plot: freshwater vs population for 10 with most water vs 10 with least water
End of explanation
selection.plot(kind='scatter', x='annual freshwater withdrawl [%] 2014', y='Renewable freshwater resource per 100,000 inhabitants [million cubic meters]', xlim=(0,80), ylim=(0,3500))
Explanation: 9) Scatter plot: freshwater per capita vs freshwater withdrawl
End of explanation
<END_TASK>