Reshaping Data in Python

I really enjoyed Jean-Nicholas Hould’s article on Tidy Data in Python, which in turn is based on this paper on Tidy Data by Hadley Wickham. In a sense, the conclusions presented are intuitive and obvious when you think about them. But data analysis can be abstract. Finding the right vocabulary for what you’re doing isn’t always easy.

In this post, I want to focus exclusively on the process of reshaping data, i.e. converting or transforming data from one format to another. There will be some repetition from Hould’s article, but the goal is to outline the various data formats that we frequently encounter, name them and name the operations we use to transform the data.

The long format

Let’s begin with looking at a table where the data is tidy. We will be referring to this as long format data (although other naming conventions exist, see below). Borrowing Wickham’s definition, in this format a) each variable forms a column, b) each observation forms a row, and c) each type of observational unit forms a table.

An example of long format data is this made-up table of three individual’s cash balance on certain dates.

In : dfOut :** date person dollars**0 2000-01-03 Michael 2001 2000-01-03 George 5002 2000-01-03 Lisa 4503 2000-01-04 Michael 180.54 2000-01-04 George 4505 2000-01-04 Lisa 4486 2000-01-05 Michael 1777 2000-01-05 George 4208 2000-01-05 Lisa 4479 2000-01-06 Michael 15010 2000-01-06 George 30011 2000-01-06 Lisa 344.6

The format of this table can be referred to as the:

• stacked format, because the individual observations are stacked on top of each other.
• record format, because each row is a single record, i.e. a single observation.
• long format, because this format will be long in the vertical direction as opposed to wide in the horizontal direction.

Pivoting data

If this table is already tidy, why would we want to reshape it to another format? Well, we might be interested in visually comparing the balance of the individuals by date. Or we might be interested in plotting the data as a time series, where each horizontal line represents one individual.

In pandas, we can accomplish just that by using the pivot method of the dataframe. This produces a “pivot table”, which will be familiar to Excel users.

In : df.pivot(index='date', columns='name', values='dollars')Out :name George Lisa Michaeldate2000-01-03 500.0 450.0 200.02000-01-04 450.0 448.0 180.52000-01-05 420.0 447.0 177.02000-01-06 300.0 344.6 150.0

Whatever column you specify as the columns argument will be used to create new columns (each unique entry will form a new column). The column you specify as the values argument will form the values of those columns, and the index will be made up of… you guessed it, the column you specify as the index argument.

The format of this table can be referred to as:

• wide format, because the table is now wider rather than longer.
• unstacked format, because the individual observations (one person/one date) are no longer stacked on top of each other.

A bit confusingly, pandas dataframes also come with a pivot_table method, which is a generalization of the pivot method. Whenever you have duplicate values for one index/column pair, you need to use the pivot_table. Let’s look at one example.

Let’s say we have data of the number of cookies that George, Lisa, and Michael have sold. Each row in our table represents one sale occasion, which means that there could be multiple rows with the same seller for a given date.

In : df_cookiesOut :cookies_sold date name0 1 2000–01–01 George1 3 2000–01–01 Michael2 3 2000–01–01 Lisa3 2 2000–01–01 George4 4 2000–01–01 Lisa

If we try to pivot this dataframe, we get a ValueError.

In : df_cookies.pivot(index='date',columns='name',values='cookies_sold')Out :...ValueError: Index contains duplicate entries, cannot reshape

Unlike our previous balance dataframe, the values in the cookies dataframe must be aggregated in order to be pivoted since George and Lisa sold cookies on multiple occasions in one single day. In other words, when we ask pandas to pivot our data, it can’t find a single value to return for duplicate pairs of dates/names. Pandas can, however, give us the sum, or the mean, or any other aggregated value for each date/name pair.

In : df_cookies.pivot_table(index='date',columns='name',values='cookies_sold')Out :name George Lisa Michaeldate2000-01-01 1.5 3.5 3.0

The default aggregation function that pandas uses is the mean, but we can easily change that using the aggfunc argument.

In : df_cookies.pivot_table(index='date',columns='name',values='cookies_sold'aggfunc='sum')Out :name George Lisa Michaeldate2000-01-01 3 7 3

Stacking and unstacking data

In addition to the pivoting methods, pandas also has the two related concepts of stacking and unstacking data. These are primarily designed to operate on multi-indexed dataframes.

Let’s create a multi-indexed dataframe of our original balance dataframe.

In : df_multi = df.set_index(['date', 'name'])In : df_multiOut :dollarsdate name2000–01–03 Michael 200.0** George** 500.0** Lisa** 450.02000–01–04 Michael 180.5** George** 450.0** Lisa** 448.02000–01–05 Michael 177.0** George** 420.0** Lisa** 447.02000–01–06 Michael 150.0** George** 300.0** Lisa** 344.6

Remember, this is stacked data. Each row corresponds to one row. With DataFrame.stack and DataFrame.unstack, we can toggle between hierarchical indices and hierarchical columns. In this case, we have a hierarchical index, so let’s see what unstack does.

In : df_multi.unstack()Out :dollarsname George Lisa Michaeldate2000-01-03 500.0 450.0 200.02000-01-04 450.0 448.0 180.52000-01-05 420.0 447.0 177.02000-01-06 300.0 344.6 150.0

As you can see, the operation moved one level of our hierarchical index to form a new level of columns in the dataframe. To move back to a stacked format, we simple use stack.

In : df_multi.unstack().stack()Out :dollarsdate name2000–01–03 Michael 200.0** George** 500.0** Lisa** 450.02000–01–04 Michael 180.5** George** 450.0** Lisa** 448.02000–01–05 Michael 177.0** George** 420.0** Lisa** 447.02000–01–06 Michael 150.0** George** 300.0** Lisa** 344.6

Transposing data

Pandas has an easy way of rotating dataframes, i.e. switching the locations of columns and indices. Simply use the T attribute to rotate the data.

In : df = pd.DataFrame({'a': [1, 2],'b': [3, 4]}, index=[‘one’, ‘two’])In : dfOut :a bone 1 3two 2 4

In : df.TOut :one twoa 1 2b 3 4

Unpivoting data with melt

Discovering pandas’ melt function was a game-changer for me. It basically allows you to unpivot data however you want. In other words, we use melt to transform wide data to long data.

Say we have a wide format dataframe like this one.

country 2010 2011 20120 Canada 55 55 861 Iraq 56 32 222 Italy 3 56 11

To reshape this data to a long format, where each row represents one country/year pair, we use melt (which is not a dataframe method, but a top-level import from pandas).

In : pd.melt(df, id_vars='country', value_vars=[2010, 2011, 2012])Out :country year value0 Canada 2010 551 Iraq 2010 562 Italy 2010 33 Canada 2011 554 Iraq 2011 325 Italy 2011 566 Canada 2012 867 Iraq 2012 228 Italy 2012 11

Hopefully this overview makes the pandas tools for reshaping data a bit clearer. Happy data wrangling!

Hacker Noon is how hackers start their afternoons. We’re a part of the @AMIfamily. We are now accepting submissions and happy to discuss advertising &sponsorship opportunities.

To learn more, read our about page, like/message us on Facebook, or simply, tweet/DM @HackerNoon.

If you enjoyed this story, we recommend reading our latest tech stories and trending tech stories. Until next time, don’t take the realities of the world for granted!