Reshaping Data

The way a dataset is arranged into rows and columns is referred to as the shape of the data. Reshaping data means changing that shape — rearranging the dataset — to make it suitable for particular analysis problems. The worked example in the sub-section below shows exactly why reshaping is useful: the same query that takes several statements on the original layout becomes a one-liner once the data is reshaped. …

The pivot function is used to reshape the data and create a new DataFrame from the original one. The textbook builds the idea through Example 3.1 — sales and profit data of four stores (S1, S2, S3 and S4) for the years 2016, 2017 and 2018:

>>> import pandas as pd
>>> data = {'Store':['S1','S4','S3','S1','S2','S3','S1','S2','S3'],
...         'Year':[2016,2016,2016,2017,2017,2017,2018,2018,2018],
...         'Total_sales(Rs)':[12000,330000,420000,20000,10000,
...                            450000,30000,11000,89000],
...         'Total_profit(Rs)':[1100,5500,21000,32000,9000,45000,
...                             3000,1900,23000]}
>>> df = pd.DataFrame(data)
>>> print(df)

Each row of this "long" layout is one store-year record. Now try answering some queries on it.

1) What was the total sale of store S1 in all the years? On the original layout we must first slice out S1's rows, then total the sales column:

>>> S1df = df[df.Store=='S1']          # data related to store S1
>>> S1df['Total_sales(Rs)'].sum()      # total of sales for S1
62000

2) What is the maximum sale value by store S3 in any year? Again: slice, then aggregate:

>>> S3df = df[df.Store=='S3']          # data related to store S3
>>> S3df['Total_sales(Rs)'].max()      # maximum sale for S3
450000

3) Which store had the maximum total sale in all the years? This time we must slice out every store separately, total each one, and compare:

>>> S1df = df[df.Store=='S1']
>>> S2df = df[df.Store=='S2']
>>> S3df = df[df.Store=='S3']
>>> S4df = df[df.Store=='S4']
>>> S1total = S1df['Total_sales(Rs)'].sum()
>>> S2total = S2df['Total_sales(Rs)'].sum()
>>> S3total = S3df['Total_sales(Rs)'].sum()
>>> S4total = S4df['Total_sales(Rs)'].sum()
>>> max(S1total, S2total, S3total, S4total)
959000

Notice the pattern: for every query we have to slice the data corresponding to a particular store first, and only then answer the question. Now let us reshape the data using pivot and see the difference:

>>> pivot1 = df.pivot(index='Store', columns='Year',
...                   values='Total_sales(Rs)')

The three parameters do the following:

• index — the column whose values will act as the index (row labels) of the pivot table; here the store names.
• columns — the column whose values become the new column headers; here the years.
• values — the column whose values will be displayed in the body of the pivot table; here the sales figures.

>>> print(pivot1)

The value of Total_sales(Rs) from every row of the original table has been transferred into the new table pivot1, where each row now holds all the data of one store and each column holds all the data of one year. Cells in the pivot table that have no matching entry in the original data are filled with NaN — for instance, there was no record of store S2's sales in 2016, so that cell of pivot1 is NaN (and similarly S4 has no 2017 or 2018 record).

With the reshaped data, the same three queries collapse to single statements:

# 1) Total sale of store S1 in all the years
>>> pivot1.loc['S1'].sum()

# 2) Maximum sale value by store S3 in any year
>>> pivot1.loc['S3'].max()

# 3) Which store had the maximum total sale?
>>> S1total = pivot1.loc['S1'].sum()
>>> S2total = pivot1.loc['S2'].sum()
>>> S3total = pivot1.loc['S3'].sum() …

To pivot on more than one value column at a time, pass a list of column names to the values parameter of the pivot() function. And if the values parameter is omitted altogether, pivot() reshapes all the numeric columns of the DataFrame.

Continuing with the store-wise sales DataFrame used in the previous sub-section, the following statement pivots both Total_sales(Rs) and Total_profit(Rs) together, with Store as the index and Year supplying the column labels:

>>> pivot2 = df.pivot(index='Store', columns='Year',
                      values=['Total_sales(Rs)', 'Total_profit(Rs)'])
>>> print(pivot2)

Notice the two-level column structure of the result: the top level names the value column (Total_sales(Rs) or Total_profit(Rs)), and under each of these the years 2016, 2017 and 2018 appear as sub-columns. Wherever a store has no record for a particular year, pandas fills that cell with NaN — exactly as in single-column pivoting.

When pivot() fails — duplicate entries. Now consider a different example. Suppose we have stock data corresponding to a store, built as a DataFrame from a dictionary:

>>> data = {'Item':['Pen','Pen','Pencil','Pencil','Pen','Pen'],
            'Color':['Red','Red','Black','Black','Blue','Blue'],
            'Price(Rs)':[10,25,7,5,50,20],
            'Units_in_stock':[50,10,47,34,55,14]}
>>> df = pd.DataFrame(data)
>>> print(df)

Suppose we now have to reshape this table with Item as the index and Color as the columns:

>>> pivot3 = df.pivot(index='Item', columns='Color', values='Units_in_stock')

This statement does not work — it results in an error:

ValueError: Index contains duplicate entries, cannot reshape
``` …

The pivot_table() function works like the pivot() function, but with one crucial difference: when several rows share the same values for the specified index/column combination, it aggregates those rows into a single entry instead of raising an error. In other words, wherever we have duplicate entries we can apply aggregate functions like min, max, mean, etc. If we do not choose one, the default aggregate function is mean.

Syntax:

pandas.pivot_table(data, values=None, index=None, columns=None, aggfunc='mean')

The parameter aggfunc can have values among sum, max, min, len, np.mean and np.median.

Multiple columns as the index. If the data have no single unique column that can act as an index, we can apply the index to multiple columns. Using the stock DataFrame from the previous sub-section (the one pivot() refused to reshape):

>>> df1 = df.pivot_table(index=['Item','Color'])
>>> print(df1)

Note that mean has been used as the default aggregate function. In the original data the blue pen appears twice, priced 50 and 20 — so the pivot table reports the mean of the two, 35.0. The same collapsing happened to every duplicated (Item, Color) pair: red pens average to 17.5, black pencils to 6.0.

Multiple aggregate functions at once. We can also apply several aggregate functions to the same data by passing a list to aggfunc. The example below uses sum, max and np.mean together:

>>> pivot_table1 = df.pivot_table(index='Item', columns='Color',
                                  values='Units_in_stock',
                                  aggfunc=[sum, max, np.mean])
>>> pivot_table1

Each aggregate gets its own block of colour columns. Reading the pen row: the two blue-pen stock figures (55 and 14) give a sum of 69.0, a maximum of 55.0 and a mean of 34.5; the two red-pen figures (50 and 10) give 60.0, 50.0 and 30.0. Cells with no matching data (there is no black pen, and no blue or red pencil) hold NaN.

Different aggregates on different columns. Pivoting can also be done on multiple value columns, and — further — a different aggregate function can be applied to each column, by passing a dictionary to aggfunc. The following example pivots on two columns, Price(Rs) and Units_in_stock, applying the len() function to Price(Rs) and the mean() function to Units_in_stock. Note that the aggregate function len returns the number of rows corresponding to that entry:

>>> pivot_table1 = df.pivot_table(index='Item', columns='Color',
                                  values=['Price(Rs)','Units_in_stock'],
                                  aggfunc={"Price(Rs)": len,
                                           "Units_in_stock": np.mean})
>>> pivot_table1

Under Price(Rs) the table now shows counts (there are 2 blue-pen rows, 2 red-pen rows and 2 black-pencil rows), while under Units_in_stock it shows the mean stock for each combination. …