Reshaping pandas data frame into as many columns as there are repeating rows

Question

I have this data frame:

>> df = pd.DataFrame({'Place' : ['A', 'A', 'B', 'B', 'C', 'C'], 'Var' : ['All', 'French', 'All', 'German', 'All', 'Spanish'], 'Values' : [250, 30, 120, 12, 200, 112]})

>> df
  Place  Values      Var
0     A     250      All
1     A      30   French
2     B     120      All
3     B      12   German
4     C     200      All
5     C     112  Spanish

It has a repeating pattern of two rows for every Place. I want to reshape it so it's one row per Place and the Var column becomes two columns, one for "All" and one for the other value.

Like so:

Place   All   Language   Value
    A   250     French      30
    B   120     German      12
    C   200     Spanish    112

A pivot table would make a column for each unique value, and I don't want that.

What's the reshaping method for this?

Answer 1

Because the data appears in alternating pattern, we can conceptualize the transformation in 2 steps.

Step 1:

Go from

a,a,a
b,b,b

To

a,a,a,b,b,b

Step 2: drop redundant columns.

The following solution applies reshape to the values of the DataFrame; the arguments to reshape are (-1, df.shape[1] * 2), which says 'give me a frame that has twice as many columns and as many rows as you can manage.

Then, I hardwired the column indexes for the filter: [0, 1, 4, 5] based on your data layout. Resulting numpy array has 4 columns, so we pass it into a DataFrame constructor along with the correct column names.

It is an unreadable solution that depends on the df layout and produces columns in the wrong order;

import pandas as pd

df = pd.DataFrame({'Place' : ['A', 'A', 'B', 'B', 'C', 'C'], 'Var' : ['All', 'French', 'All', 'German', 'All', 'Spanish'], 'Values' : [250, 30, 120, 12, 200, 112]})

df = pd.DataFrame(df.values.reshape(-1, df.shape[1] * 2)[:,[0,1,4,5]],
    columns = ['Place', 'All', 'Value', 'Language'])