Is there a way to transpose this table without a for loop

Question

I have the following pandas dataframe:

import pandas as pd

df = pd.DataFrame([[1, 100, 300, 250], 
                   [2, 475, 200, 0], 
                   [3, 125, 365, 100]], columns=['Client_ID', 'Purchase 1', 'Purchase 2', 'Purchase 3'])

╔═══════════╦════════════╦════════════╦════════════╗
║ Client_ID ║ Purchase 1 ║ Purchase 2 ║ Purchase 3 ║
╠═══════════╬════════════╬════════════╬════════════╣
║         1 ║        100 ║        300 ║        250 ║
║         2 ║        475 ║        200 ║          0 ║
║         3 ║        125 ║        365 ║        100 ║
╚═══════════╩════════════╩════════════╩════════════╝

I want to transpose that table as follows:

╔═══════════╦═══════════╗
║ Client_ID ║ Purchases ║
╠═══════════╬═══════════╣
║         1 ║       100 ║
║         1 ║       300 ║
║         1 ║       250 ║
║         2 ║       475 ║
║         2 ║       200 ║
║         2 ║         0 ║
║         3 ║       125 ║
║         3 ║       365 ║
║         3 ║       100 ║
╚═══════════╩═══════════╝

I've tried df.T but didn't work and I don't want to use a for loop. Is there a way to acchieve this?

Does this answer your question? [pandas, melt, unmelt preserve index](https://stackoverflow.com/questions/50529022/pandas-melt-unmelt-preserve-index) — RichieV, Sep 01 '20 at 15:00

Tom · Accepted Answer · 2020-09-01T15:03:22.347

2

You can do:

df.melt('Client_ID', value_name='Purchases').set_index('Client_ID').sort_index().drop('variable', axis=1)

Giving:

           Purchases
Client_ID           
1                100
1                300
1                250
2                475
2                200
2                  0
3                125
3                365
3                100

edited Sep 01 '20 at 15:03

answered Sep 01 '20 at 14:55

Tom

8,310
2
16
36

Is there a way to transpose this table without a for loop

1 Answers1