<jupyter_start><jupyter_text>07 - Pandas: Reshaping data> *© 2021, <NAME> and <NAME>  (, ). Licensed under [CC BY 4.0 Creative Commons](http://creativecommons.org/licenses/by/4.0/)*---<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>Pivoting data  Cfr. excel People who know Excel, probably know the **Pivot** functionality: ![](../img/pandas/pivot_excel.png) The data of the table:<jupyter_code>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<jupyter_output><empty_output><jupyter_text>Interested in *Grand totals*?<jupyter_code># sum columns
excelample_pivot.sum(axis=1)
# sum rows
excelample_pivot.sum(axis=0)<jupyter_output><empty_output><jupyter_text>Pivot is just reordering your data: Small subsample of the titanic dataset:<jupyter_code>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')<jupyter_output><empty_output><jupyter_text>So far, so good... Let's now use the full titanic dataset:<jupyter_code>df = pd.read_csv("data/titanic.csv")
df.head()<jupyter_output><empty_output><jupyter_text>And try the same pivot (*no worries about the try-except, this is here just used to catch a loooong error*):<jupyter_code>try:
    df.pivot(index='Sex', columns='Pclass', values='Fare')
except Exception as e:
    print("Exception!", e)<jupyter_output><empty_output><jupyter_text>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:<jupyter_code>df.loc[[1, 3], ["Sex", 'Pclass', 'Fare']]<jupyter_output><empty_output><jupyter_text>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 function`pivot_table` NOTE:   Pivot is purely restructering: a single value for each index/column combination is required.  Pivot tables - aggregating while pivoting<jupyter_code>df = pd.read_csv("data/titanic.csv")
df.pivot_table(index='Sex', columns='Pclass', values='Fare')<jupyter_output><empty_output><jupyter_text>REMEMBER:* 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.<jupyter_code>df.pivot_table(index='Sex', columns='Pclass', 
               values='Fare', aggfunc='max')
df.pivot_table(index='Sex', columns='Pclass', 
               values='Fare', aggfunc='count')<jupyter_output><empty_output><jupyter_text>REMEMBER:   There is a shortcut function for a pivot_table with a aggfunc='count' as aggregation: crosstab<jupyter_code>pd.crosstab(index=df['Sex'], columns=df['Pclass'])<jupyter_output><empty_output><jupyter_text>EXERCISE:   Make a pivot table with the survival rates for Pclass vs Sex.<jupyter_code># %load _solutions/pandas_07_reshaping_data1.py
# %load _solutions/pandas_07_reshaping_data2.py<jupyter_output><empty_output><jupyter_text>EXERCISE:   Make a table of the median Fare payed by aged/underaged vs Sex.<jupyter_code># %load _solutions/pandas_07_reshaping_data3.py
# %load _solutions/pandas_07_reshaping_data4.py<jupyter_output><empty_output><jupyter_text>Melt - from pivot table to long or tidy format 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.<jupyter_code>pivoted = df.pivot_table(index='Sex', columns='Pclass', values='Fare').reset_index()
pivoted.columns.name = None
pivoted<jupyter_output><empty_output><jupyter_text>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:<jupyter_code>pd.melt(pivoted)<jupyter_output><empty_output><jupyter_text>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:<jupyter_code>pd.melt(pivoted, id_vars=['Sex']) #, var_name='Pclass', value_name='Fare')<jupyter_output><empty_output><jupyter_text>Reshaping with `stack` and `unstack` The docs say:> Pivot a level of the (possibly hierarchical) column labels, returning aDataFrame (or Series in the case of an object with a single level ofcolumn labels) having a hierarchical index with a new inner-most levelof row labels.Indeed... Before we speak about `hierarchical index`, first check it in practice on the following dummy example:<jupyter_code>df = pd.DataFrame({'A':['one', 'one', 'two', 'two'], 
                   'B':['a', 'b', 'a', 'b'], 
                   'C':range(4)})
df<jupyter_output><empty_output><jupyter_text>To use `stack`/`unstack`, we need the values we want to shift from rows to columns or the other way around as the index:<jupyter_code>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<jupyter_output><empty_output><jupyter_text>REMEMBER:   stack: make your data longer and smaller   unstack: make your data shorter and wider   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`.<jupyter_code>df = pd.read_csv("data/titanic.csv")
df.head()
df.pivot_table(index='Pclass', columns='Sex', 
               values='Survived', aggfunc='mean')<jupyter_output><empty_output><jupyter_text>EXERCISE:   Get the same result as above based on a combination of `groupby` and `unstack`  First use `groupby` to calculate the survival ratio for all groups`unstack`  Then, use `unstack` to reshape the output of the groupby operation<jupyter_code># %load _solutions/pandas_07_reshaping_data5.py<jupyter_output><empty_output><jupyter_text>[OPTIONAL] Exercises: use the reshaping methods with the movie data These exercises are based on the [PyCon tutorial of <NAME>](https://github.com/brandon-rhodes/pycon-pandas-tutorial/) (so credit to him!) and the datasets he prepared for that. You can download these data from here: [`titles.csv`](https://drive.google.com/file/d/0B3G70MlBnCgKajNMa1pfSzN6Q3M/view?usp=sharing&resourcekey=0-EC8vCWiiKisQIkhYLBvOpQ) and [`cast.csv`](https://drive.google.com/file/d/0B3G70MlBnCgKal9UYTJSR2ZhSW8/view?usp=sharing&resourcekey=0-w_mAR11xVeXm84sRZtyAhg) and put them in the `/data` folder.<jupyter_code>cast = pd.read_csv('data/cast.csv')
cast.head()
titles = pd.read_csv('data/titles.csv')
titles.head()<jupyter_output><empty_output><jupyter_text>EXERCISE:   Plot the number of actor roles each year and the number of actress roles each year over the whole period of available movie data.<jupyter_code># %load _solutions/pandas_07_reshaping_data6.py
# %load _solutions/pandas_07_reshaping_data7.py
# %load _solutions/pandas_07_reshaping_data8.py<jupyter_output><empty_output><jupyter_text>EXERCISE:   Plot the number of actor roles each year and the number of actress roles each year. Use kind='area' as plot type<jupyter_code># %load _solutions/pandas_07_reshaping_data9.py<jupyter_output><empty_output><jupyter_text>EXERCISE:   Plot the fraction of roles that have been 'actor' roles each year over the whole period of available movie data.<jupyter_code># %load _solutions/pandas_07_reshaping_data10.py<jupyter_output><empty_output><jupyter_text>EXERCISE:   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?<jupyter_code># %load _solutions/pandas_07_reshaping_data11.py
# %load _solutions/pandas_07_reshaping_data12.py<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11741963989837623, 'fraction_numerical': 0.016569093118303325, 'mean_word_length': 5.380549682875264, 'pattern_counts': {'":': 0, '<': 100, '<?xml version=': 0, '>': 102, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '24', 'dir': 'jupyter-structured-clean-dedup', 'id': '30225', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 3169, 'n_tokens_neox': 2953, 'n_words': 862, 'path': 'notebooks/pandas_07_reshaping_data.ipynb', 'repo_name': 'jorisvandenbossche/FLAMES-python-data-wrangling', 'size': '9084'}