CODEWITHSUNDEEP
python-2.7pandaspivotreshape
As3adTintin
As3adTintin

Reputation: 2476

Reshape/pivot pandas dataframe

I have a dataframe with variables: id, 2001a, 2001b, 2002a, 2002b, 2003a, 2003b, etc.

I am trying to figure out a way to pivot the data so the variables are: id, year, a, b

The 16.2 documentation refers to some reshaping and pivoting, but that seemed to speak more towards hierarchical columns.

Any suggestions?

I am thinking about creating a hierarchical dataframe, but am not sure how to map the year in the original variable names to a created hierarchical column

sample df:

id 2001a 2001b 2002a 2002b 2003a etc.
1  242   235   5735  23    1521
2  124   168   135   1361  1
3  436   754   1     24    5124
etc.

Upvotes: 0

Views: 143

Answers (1)

Romain
Romain

Reputation: 21878

Here is a way to create hierarchical columns.

df = pd.DataFrame({'2001a': [242,124,236], 
                   '2001b':[242,124,236],
                   '2002a': [242,124,236], 
                   '2002b': [242,124,236], 
                   '2003a': [242,124,236]})
df.columns = df.columns.str.split('(\d+)', expand=True)
df

  2001      2002      2003
     a    b    a    b    a
0  242  242  242  242  242
1  124  124  124  124  124
2  236  236  236  236  236

Upvotes: 1

Related Questions