transposing unique values in column as new columns

Question

I have the following dataframe:

                    c1   c2
0  Date of transaction   2021-04-26
1       Deposit amount   10,000
2    Withdrawal amount   NaN
3              Balance   10,000
4             Comments   store_a
5  Date of transaction   2021-04-26
6       Deposit amount   20,000
7    Withdrawal amount   NaN
8              Balance   21,000
9             Comments   store_b

I want:

  Date of transaction   Deposit amount Withdrawal amount Balance Comments
0 2021-04-26            10,000         NaN               10,000  store_a 
5 2021-04-26            20,000         NaN               21,000  store_b

How can I do it?

I tried a pivot but I got this result:

   c2                                                               
c1 Balance  Comments Date of transaction Deposit amount Withdrawal amount
0  NaN      NaN      2021-04-26          NaN            NaN
1  NaN      NaN      NaN                 10,000         NaN
2  NaN      NaN      NaN                 NaN            NaN
3  10,000   NaN      NaN                 NaN            NaN
4  NaN      store_a  NaN                 NaN            NaN
5  NaN      NaN      2021-04-26          NaN            NaN
6  NaN      NaN      NaN                 20,000         NaN
7  NaN      NaN      NaN                 NaN            NaN
8  21,000   NaN      NaN                 NaN            NaN
9  NaN      store_b  NaN                 NaN            NaN

I believe I need to groupby my data before, but I didn't manage to do it successfully.

score 1 · Accepted Answer · answered Jun 26 '22 at 06:24

First assign some kind of unique id - I'm guessing we can do that given the sample data on each occurrence of c1 being equal to "Date of transaction", then pivot:

df['transaction_no'] = df['c1'].eq('Date of transaction').cumsum()
df2 = df.pivot('transaction_no', 'c1', 'c2')

This gives you:

|   transaction_no | Balance   | Comments   | Date of transaction   | Deposit amount   |   Withdrawal amount |
|-----------------:|:----------|:-----------|:----------------------|:-----------------|--------------------:|
|                1 | 10,000    | store_a    | 2021-04-26            | 10,000           |                 nan |
|                2 | 21,000    | store_b    | 2021-04-26            | 20,000           |                 nan |

transposing unique values in column as new columns

1 Answers1