<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>Reshaping data</b></font></p>
© 2016, 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
Step1:  Pivoting data
Cfr. excel
People who know Excel, probably know the Pivot functionality
Step2:  Interested in Grand totals?
Step3:  Pivot is just reordering your data
Small subsample of the titanic dataset
Step4:  So far, so good...
Let's now use the full titanic dataset
Step5:  And try the same pivot (no worries about the try-except, this is here just used to catch a loooong error)
Step6:  This does not work, because we would end up with multiple values for one cell of the resulting frame, as the error says
Step7:  Since pivot is just restructuring 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>
Step8:  <div class="alert alert-info">
<b>REMEMBER</b>
Step9:  <div class="alert alert-info">
<b>REMEMBER</b>
Step10:  <div class="alert alert-success">
<b>EXERCISE</b>
Step11:  <div class="alert alert-success">
<b>EXERCISE</b>
Step12:  Melt
The melt function performs the inverse operation of a pivot. This can be used to make your frame longer, i.e. to make a tidy version of your data.
Step13:  Assume we have a DataFrame like the above. The observations (the average Fare people payed) are spread over different columns. In a tidy dataset, each observation is stored in one row. To obtain this, we can use the melt function
Step14:  As you can see above, the melt function puts all column labels in one column, and all values in a second column.
In this case, this is not fully what we want. We would like to keep the 'Sex' column separately
Step15:  Reshaping with stack and unstack
The docs say
Step16:  To use stack/unstack, we need the values we want to shift from rows to columns or the other way around as the index
Step17:  <div class="alert alert-info">
<b>REMEMBER</b>
Step18:  <div class="alert alert-success">
<b>EXERCISE</b>
Step19:  Mimick melt
Like the pivot table above, we can now also obtain the result of melt with stack/unstack.
Let's use the same pivoted frame as above, and look at the final melt result
Step20:  <div class="alert alert-success">
<b>EXERCISE</b>
Step21:  Exercises
Step22:  <div class="alert alert-success">
<b>EXERCISE</b>
Step23:  <div class="alert alert-success">
<b>EXERCISE</b>
Step24:  <div class="alert alert-success">
<b>EXERCISE</b>
Step25:  <div class="alert alert-success">
<b>EXERCISE</b>
<ASSISTANT_TASK:>
Python Code:
%matplotlib inline
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
Explanation: <p><font size="6"><b>Reshaping data</b></font></p>
© 2016, 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
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 restructuring 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>**Pivot** is purely restructuring: 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>:
 <ul>
  <li>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.</li>
</ul>
</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 `pivot_table` with a `aggfunc=count` as aggregation: `crosstab`</li>
</ul>
</div>
End of explanation
df.pivot_table(index='Pclass', columns='Sex', 
               values='Survived', aggfunc='mean')
fig, ax1 = plt.subplots()
df.pivot_table(index='Pclass', columns='Sex', 
               values='Survived', aggfunc='mean').plot(kind='bar', 
                                                       rot=0, 
                                                       ax=ax1)
ax1.set_ylabel('Survival ratio')
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Make a pivot table with the survival rates (= number of persons survived / total number of persons) for Pclass vs Sex.</li>
  <li>Plot the result as a bar plot.</li>
</ul>
</div>
End of explanation
df['Underaged'] = df['Age'] <= 18
df.pivot_table(index='Underaged', columns='Sex', 
               values='Fare', aggfunc='mean')
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Make a table of the median Fare payed by aged/underaged vs Sex.</li>
</ul>
</div>
End of explanation
pivoted = df.pivot_table(index='Sex', columns='Pclass', values='Fare').reset_index()
pivoted.columns.name = None
pivoted
Explanation: Melt
The melt function performs the inverse operation of a pivot. This can be used to make your frame longer, i.e. to make a tidy version of your data.
End of explanation
pd.melt(pivoted)
Explanation: Assume we have a DataFrame like the above. The observations (the average Fare people payed) are spread over different columns. In a tidy dataset, each observation is stored in one row. To obtain this, we can use the melt function:
End of explanation
pd.melt(pivoted, id_vars=['Sex']) #, var_name='Pclass', value_name='Fare')
Explanation: As you can see above, the melt function puts all column labels in one column, and all values in a second column.
In this case, this is not fully what we want. We would like to keep the 'Sex' column separately:
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/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()
df.pivot_table(index='Pclass', columns='Sex', 
               values='Survived', aggfunc='mean')
Explanation: <div class="alert alert-info">
<b>REMEMBER</b>:
 <ul>
  <li>**stack**: make your data *longer* and *smaller* </li>
  <li>**unstack**: make your data *shorter* and *wider* </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.groupby(['Pclass', 'Sex'])['Survived'].mean().unstack()
Explanation: <div class="alert alert-success">
<b>EXERCISE</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</li>
  <li>Then, use `unstack` to reshape the output of the groupby operation</li>
</ul>
</div>
End of explanation
pivoted = df.pivot_table(index='Sex', columns='Pclass', values='Fare').reset_index()
pivoted.columns.name = None
pivoted
pd.melt(pivoted, id_vars=['Sex'], var_name='Pclass', value_name='Fare')
Explanation: Mimick melt
Like the pivot table above, we can now also obtain the result of melt with stack/unstack.
Let's use the same pivoted frame as above, and look at the final melt result:
End of explanation
temp = pivoted.set_index('Sex')
temp
temp.columns.name = 'Pclass'
temp = temp.stack()
temp
temp.reset_index(name='Fare')
# alternative: rename columns at the end
temp = pivoted.set_index('Sex').stack().reset_index()
temp.rename(columns={'level_1': 'Pclass', 0: 'Fare'})
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Get the same result as above using `stack`/`unstack` (combined with `set_index` / `reset_index`)</li>
  <li>Tip: set those columns as the index that you do not want to stack</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: 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 /data folder.
End of explanation
grouped = cast.groupby(['year', 'type']).size()
table = grouped.unstack('type')
table.plot()
cast.pivot_table(index='year', columns='type', values="character", aggfunc='count').plot() 
# for values in using the , take a column with no Nan values in order to count effectively all values -> at this stage: aha-erlebnis about crosstab function(!)
pd.crosstab(index=cast['year'], columns=cast['type']).plot()
Explanation: <div class="alert alert-success">
<b>EXERCISE</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
pd.crosstab(index=cast['year'], columns=cast['type']).plot(kind='area')
Explanation: <div class="alert alert-success">
<b>EXERCISE</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
grouped = cast.groupby(['year', 'type']).size()
table = grouped.unstack('type')
(table['actor'] / (table['actor'] + table['actress'])).plot(ylim=[0,1])
Explanation: <div class="alert alert-success">
<b>EXERCISE</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
sel = cast[(cast.character == 'Superman') | (cast.character == 'Batman')]
sel = sel.groupby(['year', 'character']).size()
sel = sel.unstack()
sel = sel.fillna(0)
sel.head()
d = sel['Superman'] - sel['Batman']
print('Superman years:')
print(len(d[d > 0.0]))
Explanation: <div class="alert alert-success">
<b>EXERCISE</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>