三行代码  ›  专栏  ›  技术社区  ›  superccccc

将列中的唯一值转换为新列[重复]

  •  0
  • superccccc  · 技术社区  · 1 月前

    我有以下数据框:

                        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
    

    我想要:

      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 
    

    我该怎么做?

    我尝试了pivot,但得到了以下结果:

       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
    

    我相信我以前需要按数据分组,但我没有成功地做到这一点。

    1 回复  |  直到 1 月前
        1
  •  1
  •   Jon Clements Abhi    1 月前

    首先分配某种唯一的id-我猜我们可以这样做,因为c1每次出现的样本数据都等于“交易日期”,然后透视:

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

    这将为您提供:

    |   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 |