<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 <p><font size="6"><b>07 - Pandas
Step1:  Tidy data
meltcan be used to make a dataframe longer, i.e. to make a tidy version of your data. In a tidy dataset (also sometimes called 'long-form' data or 'denormalized' data) each observation is stored in its own row and each column contains a single variable
Step2:  This data representation is not "tidy"
Step3:  The usage of the tidy data representation has some important benefits when working with groupby or data visualization libraries such as Seaborn
Step4:  Exercise with energy consumption data
To practice the "melt" operation, we are going to use a dataset from Fluvius (who operates and manages the gas and elektricity networks in Flanders) about the monthly consumption of elektricity and gas in 2021 (https
Step5:  <div class="alert alert-success">
**EXERCISE 2**
Step6:  <div class="alert alert-success">
**EXERCISE 3**
Step7:  <div class="alert alert-success">
**EXERCISE 4**
Step8:  Pivoting data
Cfr. excel
People who know Excel, probably know the Pivot functionality
Step9:  Interested in Grand totals?
Step10:  Pivot is just reordering your data
Step11:  So far, so good...
Let's now use the full titanic dataset
Step12:  And try the same pivot (no worries about the try-except, this is here just used to catch a loooong error)
Step13:  This does not work, because we would end up with multiple values for one cell of the resulting frame, as the error says
Step14:  Since pivot is just restructering data, where would both values of Fare for the same combination of Sex and Pclass need to go?
Well, they need to be combined, according to an aggregation functionality, which is supported by the functionpivot_table
<div class="alert alert-danger">
<b>NOTE</b>
Step15:  <div class="alert alert-info">
<b>REMEMBER</b>
Step16:  <div class="alert alert-info">
<b>REMEMBER</b>
Step17:  Exercises
<div class="alert alert-success">
<b>EXERCISE 5</b>
Step18:  <div class="alert alert-success">
<b>EXERCISE 6</b>
Step19:  <div class="alert alert-success">
**EXERCISE 7**
Step20:  Reshaping with stack and unstack
The docs say
Step21:  To use stack/unstack, we need the values we want to shift from rows to columns or the other way around as the index
Step22:  <div class="alert alert-info">
<b>REMEMBER</b>
Step23:  Exercises
Step24:  <div class="alert alert-success">
<b>EXERCISE 8</b>
Step25:  [OPTIONAL] Exercises
Step26:  <div class="alert alert-success">
<b>EXERCISE 9</b>
Step27:  <div class="alert alert-success">
<b>EXERCISE 10</b>
Step28:  <div class="alert alert-success">
<b>EXERCISE 11</b>
Step29:  <div class="alert alert-success">
<b>EXERCISE 12</b>
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
Explanation: <p><font size="6"><b>07 - Pandas: Tidy data and reshaping</b></font></p>
© 2021, Joris Van den Bossche and Stijn Van Hoey  (&#106;&#111;&#114;&#105;&#115;&#118;&#97;&#110;&#100;&#101;&#110;&#98;&#111;&#115;&#115;&#99;&#104;&#101;&#64;&#103;&#109;&#97;&#105;&#108;&#46;&#99;&#111;&#109;, &#115;&#116;&#105;&#106;&#110;&#118;&#97;&#110;&#104;&#111;&#101;&#121;&#64;&#103;&#109;&#97;&#105;&#108;&#46;&#99;&#111;&#109;). Licensed under CC BY 4.0 Creative Commons
End of explanation
data = pd.DataFrame({
   'WWTP': ['Destelbergen', 'Landegem', 'Dendermonde', 'Eeklo'],
   'Treatment A': [8.0, 7.5, 8.3, 6.5],
   'Treatment B': [6.3, 5.2, 6.2, 7.2]
})
data
Explanation: Tidy data
meltcan be used to make a dataframe longer, i.e. to make a tidy version of your data. In a tidy dataset (also sometimes called 'long-form' data or 'denormalized' data) each observation is stored in its own row and each column contains a single variable:
Consider the following example with measurements in different Waste Water Treatment Plants (WWTP):
End of explanation
pd.melt(data)  #, id_vars=["WWTP"])
data_long = pd.melt(data, id_vars=["WWTP"], 
                    value_name="pH", var_name="Treatment")
data_long
Explanation: This data representation is not "tidy":
Each row contains two observations of pH (each from a different treatment)
'Treatment' (A or B) is a variable not in its own column, but used as column headers
Melt - from wide to long/tidy format
We can melt the data set to tidy the data:
End of explanation
data_long.groupby("Treatment")["pH"].mean()  # switch to `WWTP`
sns.catplot(data=data, x="WWTP", y="...", hue="...", kind="bar")  # this doesn't work that easily
sns.catplot(data=data_long, x="WWTP", y="pH", 
            hue="Treatment", kind="bar")  # switch `WWTP` and `Treatment`
Explanation: The usage of the tidy data representation has some important benefits when working with groupby or data visualization libraries such as Seaborn:
End of explanation
# %load _solutions/pandas_08_reshaping_data1.py
# %load _solutions/pandas_08_reshaping_data2.py
Explanation: Exercise with energy consumption data
To practice the "melt" operation, we are going to use a dataset from Fluvius (who operates and manages the gas and elektricity networks in Flanders) about the monthly consumption of elektricity and gas in 2021 (https://www.fluvius.be/sites/fluvius/files/2021-10/verbruiksgegevens-per-maand.xlsx).
This data is available as an Excel file.
<div class="alert alert-success">
**EXERCISE 1**:
* Read the "verbruiksgegevens-per-maand.xlsx" file (in the "data/" directory) into a DataFrame `df`.
* Drop the "Regio" column (this column has a constant value "Regio 1" and thus is not that interesting).
<details><summary>Hints</summary>
- Reading Excel files can be done with the `pd.read_excel()` function, passing the path to the file as first argument.
- To drop a column, use the `columns` keyword in the `drop()` method.
</details>
</div>
End of explanation
# %load _solutions/pandas_08_reshaping_data3.py
Explanation: <div class="alert alert-success">
**EXERCISE 2**:
The actual data (consumption numbers) is spread over multiple columns: one column per month. Make a tidy version of this dataset with a single "consumption" column, and an additional "time" column. 
Make sure to keep the "Hoofdgemeente", "Energie" and "SLP"  columns in the data set. The "SLP" column contains additional categories about the type of elektricity or gas consumption (eg household vs non-household consumption).
Use `pd.melt()` to create a long or tidy version of the dataset, and call the result `df_tidy`.
<details><summary>Hints</summary>
- If there are columns in the original dataset that you want to keep (with repeated values), pass those names to the `id_vars` keyword of `pd.melt()`.
- You can use the `var_name` and `value_name` keywords to directly specify the column names to use for the new variable and value columns.
</details>
</div>
End of explanation
# %load _solutions/pandas_08_reshaping_data4.py
Explanation: <div class="alert alert-success">
**EXERCISE 3**:
Convert the "time" column to a column with a datetime data type using `pd.to_datetime`.
<details><summary>Hints</summary>
* When using `pd.to_datetime`, remember to specify a `format`.
</details>
</div>
End of explanation
# %load _solutions/pandas_08_reshaping_data5.py
# %load _solutions/pandas_08_reshaping_data6.py
Explanation: <div class="alert alert-success">
**EXERCISE 4**:
* Calculate the total consumption of elektricity and gas over all municipalities ("Hoofdgemeente") for each month. Assign the result to a dataframe called `df_overall`.
* Using `df_overall`, make a line plot of the consumption of elektricity vs gas over time. 
  * Create a separate subplot for elektricity and for gas, putting them next to each other. 
  * Ensure that the y-limit starts at 0 for both subplots.
<details><summary>Hints</summary>
* If we want to sum the consumption over all municipalities that means we should _not_ include this variable in the groupby keys. On the other hand, we want to calculate the sum *for each* month ("time") and *for each* category of elektricity/gas ("Energie").
* Creating a line plot with seaborn can be done with `sns.relplot(..., kind="line")`.
* If you want to split the plot into multiple subplots based on a variable, check the `row` or `col` keyword.
* The `sns.relplot` returns a "facet grid" object, and you can change an element of each of the subplots of this object using the `set()` method of this object. To set the y-limits, you can use the `ylim` keyword.
</details>
</div>
End of explanation
excelample = pd.DataFrame({'Month': ["January", "January", "January", "January", 
                                  "February", "February", "February", "February", 
                                  "March", "March", "March", "March"],
                   'Category': ["Transportation", "Grocery", "Household", "Entertainment",
                                "Transportation", "Grocery", "Household", "Entertainment",
                                "Transportation", "Grocery", "Household", "Entertainment"],
                   'Amount': [74., 235., 175., 100., 115., 240., 225., 125., 90., 260., 200., 120.]})
excelample
excelample_pivot = excelample.pivot(index="Category", columns="Month", values="Amount")
excelample_pivot
Explanation: Pivoting data
Cfr. excel
People who know Excel, probably know the Pivot functionality:
The data of the table:
End of explanation
# sum columns
excelample_pivot.sum(axis=1)
# sum rows
excelample_pivot.sum(axis=0)
Explanation: Interested in Grand totals?
End of explanation
df = pd.DataFrame({'Fare': [7.25, 71.2833, 51.8625, 30.0708, 7.8542, 13.0],
                   'Pclass': [3, 1, 1, 2, 3, 2],
                   'Sex': ['male', 'female', 'male', 'female', 'female', 'male'],
                   'Survived': [0, 1, 0, 1, 0, 1]})
df
df.pivot(index='Pclass', columns='Sex', values='Fare')
df.pivot(index='Pclass', columns='Sex', values='Survived')
Explanation: Pivot is just reordering your data:
Small subsample of the titanic dataset:
End of explanation
df = pd.read_csv("data/titanic.csv")
df.head()
Explanation: So far, so good...
Let's now use the full titanic dataset:
End of explanation
try:
    df.pivot(index='Sex', columns='Pclass', values='Fare')
except Exception as e:
    print("Exception!", e)
Explanation: And try the same pivot (no worries about the try-except, this is here just used to catch a loooong error):
End of explanation
df.loc[[1, 3], ["Sex", 'Pclass', 'Fare']]
Explanation: This does not work, because we would end up with multiple values for one cell of the resulting frame, as the error says: duplicated values for the columns in the selection. As an example, consider the following rows of our three columns of interest:
End of explanation
df = pd.read_csv("data/titanic.csv")
df.pivot_table(index='Sex', columns='Pclass', values='Fare')
Explanation: Since pivot is just restructering data, where would both values of Fare for the same combination of Sex and Pclass need to go?
Well, they need to be combined, according to an aggregation functionality, which is supported by the functionpivot_table
<div class="alert alert-danger">
<b>NOTE</b>:
 <ul>
  <li><b>Pivot</b> is purely restructering: a single value for each index/column combination is required.</li>
</ul>
</div>
Pivot tables - aggregating while pivoting
End of explanation
df.pivot_table(index='Sex', columns='Pclass', 
               values='Fare', aggfunc='max')
df.pivot_table(index='Sex', columns='Pclass', 
               values='Fare', aggfunc='count')
Explanation: <div class="alert alert-info">
<b>REMEMBER</b>:
* By default, `pivot_table` takes the **mean** of all values that would end up into one cell. However, you can also specify other aggregation functions using the `aggfunc` keyword.
</div>
End of explanation
pd.crosstab(index=df['Sex'], columns=df['Pclass'])
Explanation: <div class="alert alert-info">
<b>REMEMBER</b>:
 <ul>
  <li>There is a shortcut function for a <code>pivot_table</code> with a <code>aggfunc='count'</code> as aggregation: <code>crosstab</code></li>
</ul>
</div>
End of explanation
# %load _solutions/pandas_08_reshaping_data7.py
# %load _solutions/pandas_08_reshaping_data8.py
Explanation: Exercises
<div class="alert alert-success">
<b>EXERCISE 5</b>:
 <ul>
  <li>Make a pivot table with the survival rates for Pclass vs Sex.</li>
</ul>
</div>
End of explanation
# %load _solutions/pandas_08_reshaping_data9.py
# %load _solutions/pandas_08_reshaping_data10.py
Explanation: <div class="alert alert-success">
<b>EXERCISE 6</b>:
 <ul>
  <li>Make a table of the median Fare payed by aged/underaged vs Sex.</li>
</ul>
</div>
End of explanation
# %load _solutions/pandas_08_reshaping_data11.py
# %load _solutions/pandas_08_reshaping_data12.py
Explanation: <div class="alert alert-success">
**EXERCISE 7**:
A pivot table aggregates values for each combination of the new row index and column values. That reminds of the "groupby" operation.
Can you mimick the pivot table of the first exercise (a pivot table with the survival rates for Pclass vs Sex) using `groupby()`?
</div>
End of explanation
df = pd.DataFrame({'A':['one', 'one', 'two', 'two'], 
                   'B':['a', 'b', 'a', 'b'], 
                   'C':range(4)})
df
Explanation: Reshaping with stack and unstack
The docs say:
Pivot a level of the (possibly hierarchical) column labels, returning a
DataFrame (or Series in the case of an object with a single level of
column labels) having a hierarchical index with a new inner-most level
of row labels.
Indeed... 
<img src="../img/pandas/schema-stack.svg" width=50%>
Before we speak about hierarchical index, first check it in practice on the following dummy example:
End of explanation
df = df.set_index(['A', 'B']) # Indeed, you can combine two indices
df
result = df['C'].unstack()
result
df = result.stack().reset_index(name='C')
df
Explanation: To use stack/unstack, we need the values we want to shift from rows to columns or the other way around as the index:
End of explanation
df = pd.read_csv("data/titanic.csv")
df.head()
Explanation: <div class="alert alert-info">
<b>REMEMBER</b>:
 <ul>
  <li><b>stack</b>: make your data <i>longer</i> and <i>smaller</i> </li>
  <li><b>unstack</b>: make your data <i>shorter</i> and <i>wider</i> </li>
</ul>
</div>
Mimick pivot table
To better understand and reason about pivot tables, we can express this method as a combination of more basic steps. In short, the pivot is a convenient way of expressing the combination of a groupby and stack/unstack.
End of explanation
df.pivot_table(index='Pclass', columns='Sex', 
               values='Survived', aggfunc='mean')
Explanation: Exercises
End of explanation
# %load _solutions/pandas_08_reshaping_data13.py
Explanation: <div class="alert alert-success">
<b>EXERCISE 8</b>:
 <ul>
  <li>Get the same result as above based on a combination of `groupby` and `unstack`</li>
  <li>First use `groupby` to calculate the survival ratio for all groups`unstack`</li>
  <li>Then, use `unstack` to reshape the output of the groupby operation</li>
</ul>
</div>
End of explanation
cast = pd.read_csv('data/cast.csv')
cast.head()
titles = pd.read_csv('data/titles.csv')
titles.head()
Explanation: [OPTIONAL] Exercises: use the reshaping methods with the movie data
These exercises are based on the PyCon tutorial of Brandon Rhodes (so credit to him!) and the datasets he prepared for that. You can download these data from here: titles.csv and cast.csv and put them in the /notebooks/data folder.
End of explanation
# %load _solutions/pandas_08_reshaping_data14.py
# %load _solutions/pandas_08_reshaping_data15.py
# %load _solutions/pandas_08_reshaping_data16.py
Explanation: <div class="alert alert-success">
<b>EXERCISE 9</b>:
 <ul>
  <li>Plot the number of actor roles each year and the number of actress roles each year over the whole period of available movie data.</li>
</ul>
</div>
End of explanation
# %load _solutions/pandas_08_reshaping_data17.py
Explanation: <div class="alert alert-success">
<b>EXERCISE 10</b>:
 <ul>
  <li>Plot the number of actor roles each year and the number of actress roles each year. Use kind='area' as plot type</li>
</ul>
</div>
End of explanation
# %load _solutions/pandas_08_reshaping_data18.py
Explanation: <div class="alert alert-success">
<b>EXERCISE 11</b>:
 <ul>
  <li>Plot the fraction of roles that have been 'actor' roles each year over the whole period of available movie data.</li>
</ul>
</div>
End of explanation
# %load _solutions/pandas_08_reshaping_data19.py
# %load _solutions/pandas_08_reshaping_data20.py
Explanation: <div class="alert alert-success">
<b>EXERCISE 12</b>:
 <ul>
  <li>Define a year as a "Superman year" when films of that year feature more Superman characters than Batman characters. How many years in film history have been Superman years?</li>
</ul>
</div>
End of explanation
<END_TASK>