<jupyter_start><jupyter_text>Reshape**Prerequisites**- [pandas intro](https://datascience.quantecon.org/intro.html)  - [pandas basics](https://datascience.quantecon.org/basics.html)  - [Importance of index](https://datascience.quantecon.org/the_index.html)  **Outcomes**- Understand and be able to apply the `melt`/`stack`/`unstack`/`pivot` methods  - Practice transformations of indices  - Understand tidy data<jupyter_code># Uncomment following line to install on colab
! pip install qeds
import numpy as np
import pandas as pd

%matplotlib inline
# activate plot theme
import qeds
qeds.themes.mpl_style();<jupyter_output><empty_output><jupyter_text>Outline- [Reshape](Reshape)    - [Tidy Data](Tidy-Data)    - [Reshaping your Data](Reshaping-your-Data)    - [Long vs Wide](Long-vs-Wide)    - [`set_index`, `reset_index`, and Transpose](`set_index`,-`reset_index`,-and-Transpose)    - [`stack` and `unstack`](`stack`-and-`unstack`)    - [`melt`](`melt`)    - [`pivot` and `pivot_table`](`pivot`-and-`pivot_table`)    - [Visualizing Reshaping](Visualizing-Reshaping)    - [Exercises](Exercises)    Tidy DataWhile pushed more generally in the `R` language, the concept of “[tidy data](https://en.wikipedia.org/wiki/Tidy_data)” is helpful in understanding theobjectives for reshaping data, which in turn makes advanced features like[GroupBy](https://datascience.quantecon.org/groupby.html) more seamless.<NAME> gives a terminology slightly better-adapted for the experimentalsciences, but nevertheless useful for the social sciences.> A dataset is a collection of values, usually either numbers (ifquantitative) or strings (if qualitative). Values are organized in twoways. Every value belongs to a variable and an observation. A variablecontains all values that measure the same underlying attribute (likeheight, temperature, duration) across units. An observation contains allvalues measured on the same unit (like a person, or a day, or a race)across attributes. – [Tidy Data (Journal of Statistical Software 2013)](https://www.jstatsoft.org/index.php/jss/article/view/v059i10/v59i10.pdf)With this framing,> A dataset is messy or tidy depending on how rows, columns and tables arematched with observations, variables, and types. In tidy data:1.  Each variable forms a column.2.  Each observation forms a row.3.  Each type of observational unit forms a table.The “column” and “row” terms map directly to pandas columns and rows, while the“table” maps to a pandas DataFrame.With this thinking and interpretation, it becomes essential to think throughwhat uniquely identifies an “observation” in your data.Is it a country? A year? A combination of country and year?These will become the indices of your DataFrame.For those with more of a database background, the “tidy” format matches the[3rd normal form](https://en.wikipedia.org/wiki/Third_normal_form) indatabase theory, where the referential integrity of the database is maintainedby the uniqueness of the index.When considering how to map this to the social sciences, note thatreshaping data can change what we consider to be the variable andobservation in a way that doesn’t occur within the natural sciences.For example, if the “observation” uniquely identified by a country and year andthe “variable” is GDP, you may wish to reshape it so that the “observable” is acountry, and the variables are a GDP for each year.A word of caution: The tidy approach, where there is no redundancy and eachtype of observational unit forms a table, is a good approach for storing data,but you will frequently reshape/merge/etc. in order to make graphing oranalysis easier.  This doesn’t break the tidy format since those examples areephemeral states used in analysis.  Reshaping your DataThe data you receive is not always in a “shape” that makes it easy to analyze.What do we mean by shape? The number of rows and columns in aDataFrame and how information is stored in the index and column names.This lecture will teach you the basic concepts of reshaping data.As with other topics, we recommend reviewing the [pandasdocumentation](https://pandas.pydata.org/pandas-docs/stable/reshaping.html)on this subject for additional information.We will keep our discussion here as brief and simple as possible becausethese tools will reappear in subsequent lectures.<jupyter_code>url = "https://datascience.quantecon.org/assets/data/bball.csv"
bball = pd.read_csv(url)
bball.info()

bball<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 9 entries, 0 to 8
Data columns (total 8 columns):
Year        9 non-null int64
Player      9 non-null object
Team        9 non-null object
TeamName    9 non-null object
Games       9 non-null int64
Pts         9 non-null float64
Assist      9 non-null float64
Rebound     9 non-null float64
dtypes: float64(3), int64(2), object(3)
memory usage: 704.0+ bytes<jupyter_text>Long vs WideMany of these operations change between long and wide DataFrames.What does it mean for a DataFrame to be long or wide?Here is long possible long-form representation of our basketball data.<jupyter_code># Don't worry about what this command does -- We'll see it soon
bball_long = bball.melt(id_vars=["Year", "Player", "Team", "TeamName"])

bball_long<jupyter_output><empty_output><jupyter_text>And here is a wide-form version.<jupyter_code># Again, don't worry about this command... We'll see it soon too
bball_wide = bball_long.pivot_table(
    index="Year",
    columns=["Player", "variable", "Team"],
    values="value"
)
bball_wide<jupyter_output><empty_output><jupyter_text>`set_index`, `reset_index`, and TransposeWe have already seen a few basic methods for reshaping aDataFrame.- `set_index`: Move one or more columns into the index.  - `reset_index`: Move one or more index levels out of the index and make  them either columns or drop from DataFrame.  - `T`: Swap row and column labels.  Sometimes, the simplest approach is the right approach.Let’s review them briefly.<jupyter_code>bball2 = bball.set_index(["Player", "Year"])
bball2.head()
bball3 = bball2.T
bball3.head()<jupyter_output><empty_output><jupyter_text>`stack` and `unstack`The `stack` and `unstack` methods operate directly on the indexand/or column labels.  `stack``stack` is used to move certain levels of the column labels into theindex (i.e. moving from wide to long)Let’s take `ball_wide` as an example.<jupyter_code>bball_wide<jupyter_output><empty_output><jupyter_text>Suppose that we want to be able to use the `mean` method to compute theaverage value of each stat for each player, regardless of year or team.To do that, we need two column levels: one for the player and one for the variable.We can achieve this using the `stack` method.<jupyter_code>bball_wide.stack()<jupyter_output><empty_output><jupyter_text>Now, we can compute the statistic we are after.<jupyter_code>player_stats = bball_wide.stack().mean()
player_stats<jupyter_output><empty_output><jupyter_text>Now suppose instead of that we wanted to compute the average for each team andstat, averaging over years and players.We’d need to move the `Player` level down into the index so we areleft with column levels for Team and variable.We can ask pandas do this using the `level` keyword argument.<jupyter_code>bball_wide.stack(level="Player")<jupyter_output><empty_output><jupyter_text>Now we can compute the mean.<jupyter_code>bball_wide.stack(level="Player").mean()<jupyter_output><empty_output><jupyter_text>Notice a few features of the `stack` method:- Without any arguments, the `stack` arguments move the level of column  labels closest to the data (also called inner-most or bottom level of labels)  to become the index level closest to the data (also called the inner-most or  right-most level of the index). In our example, this moved `Team` down from  columns to the index.  - When we do pass a level, that level of column labels is moved down to the  right-most level of the index and all other column labels stay in their  relative position.  Note that we can also move multiple levels at a time in one call to `stack`.<jupyter_code>bball_wide.stack(level=["Player", "Team"])<jupyter_output><empty_output><jupyter_text>In the example above, we started with one level on the index (just the year) andstacked two levels to end up with a three-level index.Notice that the two new index levels went closer to the data than the existinglevel and that their order matched the order we passed in our list argument to`level`.  `unstack`Now suppose that we wanted to see a bar chart of each player’s stats.This chart should have one “section” for each player and a different coloredbar for each variable.As we’ll learn in more detail in a later lecture,  we willneed to have the player’s name on the index and the variables as columns to do this.>**Note**>>In general, for a DataFrame, calling the `plot` method will put the indexon the horizontal (x) axis and make a new line/bar/etc. for each column.Notice that we are close to that with the `player_stats` variable.<jupyter_code>player_stats<jupyter_output><empty_output><jupyter_text>We now need to rotate the variable level of the index up to be column layers.We use the `unstack` method for this.<jupyter_code>player_stats.unstack()<jupyter_output><empty_output><jupyter_text>And we can make our plot!<jupyter_code>player_stats.unstack().plot.bar()<jupyter_output><empty_output><jupyter_text>This particular visualization would be helpful if we wanted to see which statsfor which each player is strongest.For example, we can see that <NAME> scores far more points than he doesrebound, but <NAME> is a bit more balanced.What if we wanted to be able to compare all players for each statistic?This would be easier to do if the bars were grouped by variable, with adifferent bar for each player.To plot this, we need to have the variables on the index and the playername as column names.We can get this DataFrame by setting `level="Player"` when calling `unstack`.<jupyter_code>player_stats.unstack(level="Player")
player_stats.unstack(level="Player").plot.bar()<jupyter_output><empty_output><jupyter_text>Now we can use the chart to make a number of statements about players:- Ibaka does not get many assists, compared to Curry and Durant.  - Steph and <NAME> are both high scorers.  Based on the examples above, notice a few things about `unstack`:- It is the *inverse* of `stack`; `stack` will move labels down  from columns to index, while `unstack` moves them up from index to columns.  - By default, `unstack` will move the level of the index closest to the data  and place it in the column labels closest to the data.  >**Note**>>Just as we can pass multiple levels to `stack`, we can also pass multiplelevels to `unstack`.  SummaryIn some ways `set_index`, `reset_index`, `stack`, and `unstack`are the “most fundamental” reshaping operations…The other operations we discuss can be formulated with thesefour operations (and, in fact, some of them are exactly written as theseoperations in `pandas`’s code base).*Pro tip*: We remember stack vs unstack with a mnemonic: **U**nstack moves indexlevels **U**p  `melt`The `melt` method is used to move from wide to long form.It can be used to move all of the “values” stored in your DataFrame to asingle column with all other columns being used to contain identifyinginformation.**Warning**: When you use `melt`, any index that you currently havewill be deleted.We saw used `melt` above when we constructed `bball_long`:<jupyter_code>bball
# this is how we made ``bball_long``
bball.melt(id_vars=["Year", "Player", "Team", "TeamName"])<jupyter_output><empty_output><jupyter_text>Notice that the columns we specified as `id_vars` remained columns, but allother columns were put into two new columns:1. `variable`: This has dtype string and contains the former column names.  as values  1. `value`: This has the former values.  Using this method is an effective way to get our data in *tidy* form as notedabove.  `pivot` and `pivot_table`The next two reshaping methods that we will use are closely related.Some of you might even already be familiar with these ideas because youhave previously used *pivot tables* in Excel.- If so, good news. We think this is even more powerful than Excel  and easier to use!  - If not, good news. You are about to learn a very powerful and user-friendly tool.  We will begin with `pivot`.The `pivot` method:- Takes the unique values of one column and places them along the index.  - Takes the unique values of another column and places them along the  columns.  - Takes the values that correspond to a third column and fills in the  DataFrame values that correspond to that index/column pair.  We’ll illustrate with an example.<jupyter_code># .head 8 excludes Ibaka -- will discuss why later
bball.head(6).pivot(index="Year", columns="Player", values="Pts")<jupyter_output><empty_output><jupyter_text>We can replicate `pivot` using three of the fundamental operationsfrom above:1. Call `set_index` with the `index` and `columns` arguments  1. Extract the `values` column  1. `unstack` the columns level of the new index<jupyter_code>#  1---------------------------------------  2---  3----------------------
bball.head(6).set_index(["Year", "Player"])["Pts"].unstack(level="Player")<jupyter_output><empty_output><jupyter_text>One important thing to be aware of is that in order for `pivot` towork, the index/column pairs must be *unique*!Below, we demonstrate the error that occurs when they are not unique. ```python Ibaka shows up twice in 2016 because he was traded mid-season from the Orlando Magic to the Toronto Raptorsbball.pivot(index="Year", columns="Player", values="Pts")```  `pivot_table`The `pivot_table` method is a generalization of `pivot`.It overcomes two limitations of `pivot`:1. It allows you to choose multiple columns for the index/columns/values  arguments.  1. It allows you to deal with duplicate entries by  having you choose how to combine them.<jupyter_code>bball<jupyter_output><empty_output><jupyter_text>Notice that we can replicate the functionality of `pivot` if we passthe same arguments.<jupyter_code>bball.head(6).pivot(index="Year", columns="Player", values="Pts")<jupyter_output><empty_output><jupyter_text>But we can also choose multiple columns to be used inindex/columns/values.<jupyter_code>bball.pivot_table(index=["Year", "Team"], columns="Player", values="Pts")
bball.pivot_table(index="Year", columns=["Player", "Team"], values="Pts")<jupyter_output><empty_output><jupyter_text>AND we can deal with duplicated index/column pairs.<jupyter_code># This produced an error
# bball.pivot(index="Year", columns="Player", values="Pts")

# This doesn't!
bball_pivoted = bball.pivot_table(index="Year", columns="Player", values="Pts")
bball_pivoted<jupyter_output><empty_output><jupyter_text>`pivot_table` handles duplicate index/column pairs using an aggregation.By default, the aggregation is the mean.For example, our duplicated index/column pair is `("x", 1)` and hadassociated values of 2 and 5.Notice that `bball_pivoted.loc[2016, "Ibaka"]` is `(15.1 + 14.2)/2 = 14.65`.We can choose how `pandas` aggregates all of the values.For example, here’s how we would keep the max.<jupyter_code>bball.pivot_table(index="Year", columns="Player", values="Pts", aggfunc=max)<jupyter_output><empty_output><jupyter_text>Maybe we wanted to count how many values there were.<jupyter_code>bball.pivot_table(index="Year", columns="Player", values="Pts", aggfunc=len)<jupyter_output><empty_output><jupyter_text>We can even pass multiple aggregation functions!<jupyter_code>bball.pivot_table(index="Year", columns="Player", values="Pts", aggfunc=[max, len])<jupyter_output><empty_output><jupyter_text>Visualizing ReshapingNow that you have learned the basics and had a chance to experiment,we will use some generic data to provide a visualization of what the abovereshape operations do.The data we will use is:<jupyter_code># made up
# columns A and B are "identifiers" while C, D, and E are variables.
df = pd.DataFrame({
    "A": [0, 0, 1, 1],
    "B": "x y x z".split(),
    "C": [1, 2, 1, 4],
    "D": [10, 20, 30, 20,],
    "E": [2, 1, 5, 4,]
})

df.info()
df
df2 = df.set_index(["A", "B"])
df2.head()
df3 = df2.T
df3.head()<jupyter_output><empty_output><jupyter_text>`stack` and `unstack`Below is an animation that shows how stacking works.![https://datascience.quantecon.org/assets/_static/reshape_files/stack.gif](https://datascience.quantecon.org/assets/_static/reshape_files/stack.gif)<jupyter_code>df2
df2_stack = df2.stack()
df2_stack<jupyter_output><empty_output><jupyter_text>And here is an animation that shows how unstacking works.![https://datascience.quantecon.org/assets/_static/reshape_files/unstack_level0.gif](https://datascience.quantecon.org/assets/_static/reshape_files/unstack_level0.gif)<jupyter_code>df2
df2.unstack()<jupyter_output><empty_output><jupyter_text>`melt`As noted above, the `melt` method transforms data from wide to long in form.Here’s a visualization of that operation.![https://datascience.quantecon.org/assets/_static/reshape_files/melt.gif](https://datascience.quantecon.org/assets/_static/reshape_files/melt.gif)<jupyter_code>df
df_melted = df.melt(id_vars=["A", "B"])
df_melted<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09476555246053853, 'fraction_numerical': 0.007195914577530177, 'mean_word_length': 5.392062314540059, 'pattern_counts': {'":': 5, '<': 121, '<?xml version=': 0, '>': 129, 'https://': 15, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '29', 'dir': 'jupyter-structured-clean-dedup', 'id': '104388', 'license': 'Apache-2.0', 'n_tokens_mistral': 5200, 'n_tokens_neox': 5015, 'n_words': 2038, 'path': 'Session_7/6_reshape.ipynb', 'repo_name': 'remi-sudo/Classes', 'size': '17256'}