Pandas如何进行部分数据帧转换

Question

我有这样一个数据帧：

+---------+--------------+-------+-------------+
| Customer|    invoice   | Amount| invoice type|
+---------+--------------+-------+-------------+
| Frank   |  invoice 1 n°|   150 |    type1    |
| Frank   |  invoice 2 n°|   300 |    type2    |
| Peter   |  invoice 1 n°|   450 |    type2    |
+---------+--------------+-------+-------------+

其中每一行对应一张发票

我需要将其转化为：

+---------+-------------+--------+--------------+------------+--------+--------------+
| Customer|  invoice1   | Amount1| invoice1 type|  invoice2  | Amount2| invoice2 type|
+---------+-------------+--------+--------------+------------+--------+--------------+
| Frank   |  invoice1 n°|   150  |    type1     | invoice2 n°|  300   |     type2    |
| Peter   |  invoice1 n°|   450  |    type2     |            |        |              |
+---------+-------------+--------+--------------+------------+--------+--------------+

其中每一行对应一个客户，不同的发票信息被转置到列中。如果客户机的发票多于列，则需要创建新列以包含所有发票信息

我看到了如何在熊猫身上做一个简单的换位，但我完全不知道我如何能轻松做到这一点

任何帮助都将不胜感激

Answer 1

为了找到要水平展开的列数，我们在ID中找到累积值。列数用pivot_table()转换。然后，它用新的列名更新表

df['flg'] = 1
df['flg'] = df.groupby('Customer')['flg'].transform(pd.Series.cumsum)
df2 = pd.pivot_table(df, index=['Customer'], values=['invoice','Amountinvoice','type'], columns=['flg'], fill_value='', aggfunc=lambda x: x)
new_cols = ['{}_{}'.format(x,y) for x,y in df2.columns]
df2.columns = new_cols
df2.reset_index(inplace=True)
df2 = df2.loc[:,['Customer','invoice_1','Amountinvoice_1','type_1','invoice_2','Amountinvoice_2','type_2']]

df2
|    | Customer   | invoice_1    |   Amountinvoice_1 | type_1   | invoice_2    | Amountinvoice_2   | type_2   |
| -:|:     -|:      -|         :|:    -|:      -|:         |:    -|
|  0 | Frank      | invoice 1 n° |               150 | type1    | invoice 2 n° | 300.0             | type2    |
|  1 | Peter      | invoice 1 n° |               450 | type2    |              |                   |          |