In [1]:
import pandas as pd
import plotly.graph_objects as go
import plotly.express as px
import plotly
In [2]:
# load Alcohol, recorded per capita (15+) consumption (in litres of pure alcohol)
df_2009 = pd.read_csv('data (3).csv')
df_2018 = pd.read_csv('data (4).csv')
df_2018
Out[2]:
Country Beverage Types 2018 2017 2016 2015 2014 2013 2012 2011 2010
0 Albania All types 4.70 4.75 4.67 4.54 4.33 4.54 4.74 5.42 5.28
1 Albania Beer 1.60 1.60 1.49 1.45 1.27 1.33 1.34 1.88 1.72
2 Albania Wine 1.30 1.30 1.27 1.10 1.00 1.07 1.12 1.08 1.08
3 Albania Spirits 1.72 1.77 1.83 1.90 1.98 2.06 2.19 2.37 2.36
4 Albania Other alcoholic beverages 0.08 0.08 0.08 0.08 0.08 0.09 0.09 0.10 0.12
... ... ... ... ... ... ... ... ... ... ... ...
250 Uzbekistan All types 1.57 1.57 1.59 1.59 1.48 1.52 1.43 1.68 1.69
251 Uzbekistan Beer 0.55 0.55 0.55 0.53 0.44 0.48 0.53 0.77 0.81
252 Uzbekistan Wine 0.14 0.14 0.17 0.18 0.16 0.16 0.10 0.10 0.07
253 Uzbekistan Spirits 0.88 0.87 0.87 0.88 0.89 0.88 0.80 0.81 0.81
254 Uzbekistan Other alcoholic beverages 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

255 rows × 11 columns

In [3]:
a = df_2018.drop(['Beverage Types'], axis=1)
df_2018 = a.rename(columns={'Country': 'Country2'})
df_2018
Out[3]:
Country2 2018 2017 2016 2015 2014 2013 2012 2011 2010
0 Albania 4.70 4.75 4.67 4.54 4.33 4.54 4.74 5.42 5.28
1 Albania 1.60 1.60 1.49 1.45 1.27 1.33 1.34 1.88 1.72
2 Albania 1.30 1.30 1.27 1.10 1.00 1.07 1.12 1.08 1.08
3 Albania 1.72 1.77 1.83 1.90 1.98 2.06 2.19 2.37 2.36
4 Albania 0.08 0.08 0.08 0.08 0.08 0.09 0.09 0.10 0.12
... ... ... ... ... ... ... ... ... ... ...
250 Uzbekistan 1.57 1.57 1.59 1.59 1.48 1.52 1.43 1.68 1.69
251 Uzbekistan 0.55 0.55 0.55 0.53 0.44 0.48 0.53 0.77 0.81
252 Uzbekistan 0.14 0.14 0.17 0.18 0.16 0.16 0.10 0.10 0.07
253 Uzbekistan 0.88 0.87 0.87 0.88 0.89 0.88 0.80 0.81 0.81
254 Uzbekistan 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

255 rows × 10 columns

In [4]:
# Put two datasets together
result = pd.concat([df_2009, df_2018], axis=1)
result
Out[4]:
Country Beverage Types 2009 2008 2007 2006 2005 2004 2003 2002 ... Country2 2018 2017 2016 2015 2014 2013 2012 2011 2010
0 Albania All types 5.79 5.56 5.46 5.17 4.98 4.42 4.33 3.96 ... Albania 4.70 4.75 4.67 4.54 4.33 4.54 4.74 5.42 5.28
1 Albania Beer 2.21 2.13 1.94 1.79 1.65 1.38 1.45 1.29 ... Albania 1.60 1.60 1.49 1.45 1.27 1.33 1.34 1.88 1.72
2 Albania Wine 1.06 0.97 1.06 1.00 1.02 0.82 0.58 0.40 ... Albania 1.30 1.30 1.27 1.10 1.00 1.07 1.12 1.08 1.08
3 Albania Spirits 2.41 2.35 2.35 2.28 2.22 2.14 2.22 2.18 ... Albania 1.72 1.77 1.83 1.90 1.98 2.06 2.19 2.37 2.36
4 Albania Other alcoholic beverages 0.12 0.11 0.11 0.10 0.09 0.09 0.09 0.09 ... Albania 0.08 0.08 0.08 0.08 0.08 0.09 0.09 0.10 0.12
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
250 Uzbekistan All types 1.70 1.72 1.75 1.81 1.76 2.82 2.92 2.73 ... Uzbekistan 1.57 1.57 1.59 1.59 1.48 1.52 1.43 1.68 1.69
251 Uzbekistan Beer 0.81 0.75 0.64 0.60 0.45 0.35 0.27 0.24 ... Uzbekistan 0.55 0.55 0.55 0.53 0.44 0.48 0.53 0.77 0.81
252 Uzbekistan Wine 0.07 0.14 0.16 0.14 0.16 0.24 0.32 0.24 ... Uzbekistan 0.14 0.14 0.17 0.18 0.16 0.16 0.10 0.10 0.07
253 Uzbekistan Spirits 0.82 0.82 0.95 1.07 1.15 2.24 2.32 2.25 ... Uzbekistan 0.88 0.87 0.87 0.88 0.89 0.88 0.80 0.81 0.81
254 Uzbekistan Other alcoholic beverages 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 ... Uzbekistan 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

255 rows × 22 columns

In [5]:
df_result = result.drop(['Country2'], axis=1)
df_result
Out[5]:
Country Beverage Types 2009 2008 2007 2006 2005 2004 2003 2002 ... 2000 2018 2017 2016 2015 2014 2013 2012 2011 2010
0 Albania All types 5.79 5.56 5.46 5.17 4.98 4.42 4.33 3.96 ... 3.92 4.70 4.75 4.67 4.54 4.33 4.54 4.74 5.42 5.28
1 Albania Beer 2.21 2.13 1.94 1.79 1.65 1.38 1.45 1.29 ... 1.33 1.60 1.60 1.49 1.45 1.27 1.33 1.34 1.88 1.72
2 Albania Wine 1.06 0.97 1.06 1.00 1.02 0.82 0.58 0.40 ... 0.42 1.30 1.30 1.27 1.10 1.00 1.07 1.12 1.08 1.08
3 Albania Spirits 2.41 2.35 2.35 2.28 2.22 2.14 2.22 2.18 ... 2.08 1.72 1.77 1.83 1.90 1.98 2.06 2.19 2.37 2.36
4 Albania Other alcoholic beverages 0.12 0.11 0.11 0.10 0.09 0.09 0.09 0.09 ... 0.09 0.08 0.08 0.08 0.08 0.08 0.09 0.09 0.10 0.12
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
250 Uzbekistan All types 1.70 1.72 1.75 1.81 1.76 2.82 2.92 2.73 ... 3.03 1.57 1.57 1.59 1.59 1.48 1.52 1.43 1.68 1.69
251 Uzbekistan Beer 0.81 0.75 0.64 0.60 0.45 0.35 0.27 0.24 ... 0.20 0.55 0.55 0.55 0.53 0.44 0.48 0.53 0.77 0.81
252 Uzbekistan Wine 0.07 0.14 0.16 0.14 0.16 0.24 0.32 0.24 ... 0.30 0.14 0.14 0.17 0.18 0.16 0.16 0.10 0.10 0.07
253 Uzbekistan Spirits 0.82 0.82 0.95 1.07 1.15 2.24 2.32 2.25 ... 2.53 0.88 0.87 0.87 0.88 0.89 0.88 0.80 0.81 0.81
254 Uzbekistan Other alcoholic beverages 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 ... 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

255 rows × 21 columns

In [6]:
# Transpose dataset
df_result = df_result[['Country','Beverage Types','2000','2001','2002','2003','2004','2005','2006',
                       '2007','2008','2009','2010','2011', '2012','2013','2014','2015','2016','2017','2018']]
df_reset = df_result.set_index('Country')
a = df_result[(df_result["Beverage Types"] == 'All types')]
b=a.drop(["Beverage Types"], axis=1)

c = b.set_index('Country').T
c.drop([])
Out[6]:
Country Albania Andorra Armenia Austria Azerbaijan Belarus Belgium Bosnia and Herzegovina Bulgaria Croatia ... Slovenia Spain Sweden Switzerland Tajikistan Turkey Turkmenistan Ukraine United Kingdom of Great Britain and Northern Ireland Uzbekistan
2000 3.92 12.77 2.91 13.35 0.66 12.92 11.21 3.87 10.26 13.24 ... 12.80 11.61 6.20 11.26 0.81 1.66 2.89 6.95 10.82 3.03
2001 4.51 13.08 2.87 12.35 0.50 10.74 11.01 4.11 11.07 13.69 ... 11.58 12.01 6.50 11.12 0.80 1.60 2.36 7.31 11.31 2.92
2002 3.96 12.16 2.87 12.09 0.55 11.78 11.29 4.08 10.80 13.88 ... 9.87 10.41 6.88 10.85 0.80 1.59 2.41 7.13 11.33 2.73
2003 4.33 12.25 3.00 12.31 0.55 11.17 11.28 4.29 10.92 12.99 ... 11.55 11.04 6.88 10.82 0.74 1.53 2.57 6.81 11.28 2.92
2004 4.42 12.23 3.73 12.17 0.63 12.08 12.05 4.38 10.70 12.42 ... 10.01 11.32 6.51 10.54 0.70 1.48 2.58 6.51 11.58 2.82
2005 4.98 11.82 4.17 12.14 0.73 11.19 12.21 4.50 10.22 10.72 ... 11.19 11.92 6.53 10.15 0.65 1.39 2.63 6.82 11.38 1.76
2006 5.17 11.89 3.99 12.58 0.85 12.61 10.94 4.72 10.18 10.98 ... 12.26 11.86 6.81 10.24 0.56 1.39 2.54 7.42 10.93 1.81
2007 5.46 11.53 3.91 12.54 0.95 13.91 13.43 5.03 10.66 11.36 ... 11.02 11.05 6.98 10.44 0.54 1.37 2.23 8.12 11.06 1.75
2008 5.56 10.96 3.96 12.03 1.12 14.58 10.51 5.03 10.73 11.36 ... 10.94 10.24 7.02 10.29 0.56 1.51 2.12 9.41 10.66 1.72
2009 5.79 10.86 3.97 11.87 1.92 13.98 10.10 4.69 10.53 11.11 ... 10.52 9.99 7.31 10.15 0.66 1.52 2.00 8.97 9.98 1.70
2010 5.28 10.58 4.23 12.10 1.92 14.34 10.27 4.55 10.16 11.10 ... 10.33 8.79 7.38 10.01 0.67 1.56 2.90 7.77 10.01 1.69
2011 5.42 10.46 4.07 11.90 1.97 14.41 10.14 4.67 10.22 11.40 ... 10.61 8.58 7.31 9.99 0.69 1.59 2.88 8.75 9.80 1.68
2012 4.74 10.34 3.89 12.10 2.01 13.47 10.09 4.76 11.23 10.96 ... 10.95 8.44 7.23 9.87 0.76 1.60 3.00 8.55 9.53 1.43
2013 4.54 10.07 3.92 11.60 2.08 12.49 10.33 4.59 10.67 10.76 ... 9.53 8.39 7.32 9.74 0.78 1.46 3.18 8.74 9.42 1.52
2014 4.33 9.89 4.22 12.20 2.00 11.21 10.57 4.64 10.65 9.87 ... 10.92 7.67 7.20 9.62 0.81 1.50 3.24 7.60 9.45 1.48
2015 4.54 9.85 4.04 11.60 4.14 9.72 10.36 4.73 11.24 9.99 ... 11.49 10.35 7.13 9.62 0.90 1.43 3.36 6.06 9.59 1.59
2016 4.67 9.94 3.82 11.70 3.05 9.70 9.42 4.81 11.48 10.39 ... 10.51 10.80 7.15 9.51 0.86 1.38 3.32 5.74 9.69 1.59
2017 4.75 9.93 3.83 11.70 2.92 9.75 9.42 4.97 11.28 10.06 ... 10.12 10.84 7.04 9.51 0.86 1.38 3.17 5.15 9.88 1.57
2018 4.70 9.75 3.71 11.80 2.92 10.09 9.42 4.99 11.42 10.10 ... 9.99 10.43 7.20 9.51 0.84 1.43 3.08 5.15 10.01 1.57

19 rows × 51 columns

In [7]:
# generate linear graph of alcohol, per capita consumption of Europe over time
fig = px.line(c, x = c.index, y=c.columns,
              title='Alcohol, per capita consumption of Europe over time')
fig.update_layout(xaxis_title="Year", yaxis_title='Liters')
fig.show()
plotly.io.write_html(fig,"europe.html", full_html=False)
In [8]:
# generate linear graph of alcohol, per capita consumption of Europe over time, top 5 countries
df = c[['Czechia','Latvia','Austria','Lithuania','Bulgaria']]
fig = px.line(df, x = df.index, y=df.columns,
              title='Alcohol, per capita consumption of Europe over time, top 5 countries')
fig.update_layout(xaxis_title="Year", yaxis_title='Liters')
fig.show()
plotly.io.write_html(fig,"top5.html", full_html=False)
In [9]:
# generate linear graph of alcohol, per capita consumption of Europe over time, vottom 5 countries
df1 = c[['Tajikistan','Turkey','Uzbekistan','Azerbaijan','Israel']]
fig = px.line(df1, x = df1.index, y=df1.columns,
              title='Alcohol, per capita consumption of Europe over time, bottom 5 countries')
fig.update_layout(xaxis_title="Year", yaxis_title='Liters')
fig.show()
plotly.io.write_html(fig,"bottom5.html", full_html=False)
In [10]:
# calculate sum of all alcohol consumption of all countries in each year
c["sum"] = c.sum(axis=1)
c
Out[10]:
Country Albania Andorra Armenia Austria Azerbaijan Belarus Belgium Bosnia and Herzegovina Bulgaria Croatia ... Spain Sweden Switzerland Tajikistan Turkey Turkmenistan Ukraine United Kingdom of Great Britain and Northern Ireland Uzbekistan sum
2000 3.92 12.77 2.91 13.35 0.66 12.92 11.21 3.87 10.26 13.24 ... 11.61 6.20 11.26 0.81 1.66 2.89 6.95 10.82 3.03 415.53
2001 4.51 13.08 2.87 12.35 0.50 10.74 11.01 4.11 11.07 13.69 ... 12.01 6.50 11.12 0.80 1.60 2.36 7.31 11.31 2.92 417.30
2002 3.96 12.16 2.87 12.09 0.55 11.78 11.29 4.08 10.80 13.88 ... 10.41 6.88 10.85 0.80 1.59 2.41 7.13 11.33 2.73 419.45
2003 4.33 12.25 3.00 12.31 0.55 11.17 11.28 4.29 10.92 12.99 ... 11.04 6.88 10.82 0.74 1.53 2.57 6.81 11.28 2.92 420.46
2004 4.42 12.23 3.73 12.17 0.63 12.08 12.05 4.38 10.70 12.42 ... 11.32 6.51 10.54 0.70 1.48 2.58 6.51 11.58 2.82 429.82
2005 4.98 11.82 4.17 12.14 0.73 11.19 12.21 4.50 10.22 10.72 ... 11.92 6.53 10.15 0.65 1.39 2.63 6.82 11.38 1.76 431.32
2006 5.17 11.89 3.99 12.58 0.85 12.61 10.94 4.72 10.18 10.98 ... 11.86 6.81 10.24 0.56 1.39 2.54 7.42 10.93 1.81 456.49
2007 5.46 11.53 3.91 12.54 0.95 13.91 13.43 5.03 10.66 11.36 ... 11.05 6.98 10.44 0.54 1.37 2.23 8.12 11.06 1.75 461.58
2008 5.56 10.96 3.96 12.03 1.12 14.58 10.51 5.03 10.73 11.36 ... 10.24 7.02 10.29 0.56 1.51 2.12 9.41 10.66 1.72 457.42
2009 5.79 10.86 3.97 11.87 1.92 13.98 10.10 4.69 10.53 11.11 ... 9.99 7.31 10.15 0.66 1.52 2.00 8.97 9.98 1.70 436.40
2010 5.28 10.58 4.23 12.10 1.92 14.34 10.27 4.55 10.16 11.10 ... 8.79 7.38 10.01 0.67 1.56 2.90 7.77 10.01 1.69 431.51
2011 5.42 10.46 4.07 11.90 1.97 14.41 10.14 4.67 10.22 11.40 ... 8.58 7.31 9.99 0.69 1.59 2.88 8.75 9.80 1.68 434.30
2012 4.74 10.34 3.89 12.10 2.01 13.47 10.09 4.76 11.23 10.96 ... 8.44 7.23 9.87 0.76 1.60 3.00 8.55 9.53 1.43 430.91
2013 4.54 10.07 3.92 11.60 2.08 12.49 10.33 4.59 10.67 10.76 ... 8.39 7.32 9.74 0.78 1.46 3.18 8.74 9.42 1.52 422.15
2014 4.33 9.89 4.22 12.20 2.00 11.21 10.57 4.64 10.65 9.87 ... 7.67 7.20 9.62 0.81 1.50 3.24 7.60 9.45 1.48 417.17
2015 4.54 9.85 4.04 11.60 4.14 9.72 10.36 4.73 11.24 9.99 ... 10.35 7.13 9.62 0.90 1.43 3.36 6.06 9.59 1.59 415.93
2016 4.67 9.94 3.82 11.70 3.05 9.70 9.42 4.81 11.48 10.39 ... 10.80 7.15 9.51 0.86 1.38 3.32 5.74 9.69 1.59 413.70
2017 4.75 9.93 3.83 11.70 2.92 9.75 9.42 4.97 11.28 10.06 ... 10.84 7.04 9.51 0.86 1.38 3.17 5.15 9.88 1.57 408.10
2018 4.70 9.75 3.71 11.80 2.92 10.09 9.42 4.99 11.42 10.10 ... 10.43 7.20 9.51 0.84 1.43 3.08 5.15 10.01 1.57 405.28

19 rows × 52 columns

In [11]:
# Generate a linear graph of alcohol consumption over time, 2000-2018
fig = go.Figure(go.Scatter(
    x = c.index,
    y = c['sum']
))

fig.update_xaxes(
    rangeslider_visible=True)

fig.show()
plotly.io.write_html(fig,"time.html", full_html=False)