How can I pivot dataframe in a specific way in pandas

Question

I have a small question about pandas dataframe. How can I pivot my dataframe from this

AGE       | GROUP-A | ... | GROUP-Q 
----------|---------|-----|---------
00-04     |77       |     |133
05-17     |117      |     |106
18-25     |64       |     |110

to this

GROUP | 00-04 | 05-17 | 18-25 
------|-------|-------|-------
A     |77     |117    |64
...   |       |       | 
Q     |133    |106    |110

Sorry I don't get - is it a single line including both column names and values that you want to convert to a table with column names int the first row, and values in the following rows? — famargar
– famargar, Commented May 9, 2018 at 10:05
Sorry I don't know why i can't put it in a tabular form. I'm trying to resolve the problem. — Ismael Benacha
– Ismael Benacha, Commented May 9, 2018 at 10:08
Can you post say 3 rows of your original text, and 3 rows of how you would like the output to look like? That may help understanding better what you need? — famargar
– famargar, Commented May 9, 2018 at 10:09
I don't think you are after a pivot table. I think what you really want to do is df.transpose(). You will need to then do some more work to change the column/row names as you have them in the question. The answer here may help you with that. — PyPingu
– PyPingu, Commented May 9, 2018 at 10:39

jpp · Accepted Answer · 2018-05-09 11:58:47Z

3

You can achieve your desired result by setting 'AGE' as your index, followed by pd.DataFrame.transpose:

df = pd.DataFrame({'AGE': ['00-04', '05-17', '18-25'],
                   'GROUP-A': [77, 117, 64],
                   'GROUP-Q': [133, 106, 110]})

res = df.set_index('AGE')\
        .transpose()

print(res)

AGE      00-04  05-17  18-25
GROUP-A     77    117     64
GROUP-Q    133    106    110

answered May 9, 2018 at 11:58

jpp

166k37 gold badges301 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scott Boston · Accepted Answer · 2018-05-09 12:48:29Z

0

Using @JPP setup, you can use the following to get your exact expected output in your question.

df.set_index('AGE').rename(columns=lambda x: x[-1])\
  .T.rename_axis('GROUP').rename_axis([None], axis=1)\
  .reset_index()

Output:

  GROUP  00-04  05-17  18-25
0     A     77    117     64
1     Q    133    106    110

Note: .T is the same as .transpose, just easier to type.

answered May 9, 2018 at 12:48

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Collectives™ on Stack Overflow

How can I pivot dataframe in a specific way in pandas

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related