循环遍历数据文件列表以动态创建新列

Question

我正在开发一个数据集，这将使我能够评估各种客户行为。为此，我在将几个excel文件读入一个列表中，然后将它们连接到一个单一的dataframe中。不过，在此之前，我想在每个专栏中创建几个新的专栏，根据年份和产品总结收入，如下所示：

输入数据

| |Year     |Customer |Product  |PO       |Revenue  |
| |:--------|:--------|:--------|:--------|:--------|
|0| 2019    | Cust 1  | DIGITAL |  P1     |  100    |
|1| 2020    | Cust 1  | DIGITAL |  P2     |  120    |
|2| 2019    | Cust 2  | STORE   |  P3     |  240    |
|3| 2019    | Cust 1  | DIGITAL |  P4     |  200    |
|4| 2019    | Cust 2  | DIGITAL |  P5     |  110    |
|5| 2020    | Cust 2  | STORE   |  P6     |  100    |
|6| 2020    | Cust 3  | DIGITAL |  P7     |  120    |
|7| 2020    | Cust 3  | STORE   |  P8     |  180    |

期望输出

| |Year     |Customer |Product  |PO       |Revenue  |19 Total |20 Total |19 Dig   |20 Dig   |19 Store |20 Store 
| |:--------|:--------|:--------|:--------|:--------|:--------|:--------|:--------|:--------|:--------|:--------
|0| 2019    | Cust 1  | DIGITAL |  P1     |  100    |  100    |         |  100    |         |         |  
|1| 2020    | Cust 1  | DIGITAL |  P2     |  120    |         |  120    |         |  120    |         |         
|2| 2019    | Cust 2  | STORE   |  P3     |  240    |  240    |         |         |         |  240    |  
|3| 2019    | Cust 1  | DIGITAL |  P4     |  200    |  200    |         |  200    |         |         |  
|4| 2019    | Cust 2  | DIGITAL |  P5     |  110    |  110    |         |  110    |         |         |
|5| 2020    | Cust 2  | STORE   |  P6     |  100    |         |  100    |         |         |         |  100    
|6| 2020    | Cust 3  | DIGITAL |  P7     |  120    |         |  120    |         |  120    |         |  
|7| 2020    | Cust 3  | STORE   |  P8     |  180    |         |  180    |         |         |         |  180    |

因此，基本上每年都会有它的年度总数，以及产品类别下的收入。注意，现在需要保持列或行的顺序。

你所能给予的任何帮助都将是伟大的--如果有什么不合理的话请告诉我。

在通过编辑的几个选项中，我发现下面的代码是有效的，但是我有信心有一个更干净的方法来编写它，如果有人能帮上忙的话？

df_2019 = df.loc[df['Year'] == 2019]
df_2020 = df.loc[df['Year'] == 2020]

df_list = [df_2019, df_2020]

for i in df_list:
  i[str(i['Year'].min())+' Total Rev'] = i['Revenue']
  i[str(i['Year'].min())+' Dig Rev'] = i.loc[i['Product'] == 'DIGITAL', 'Revenue']
  i[str(i['Year'].min())+' Store Rev'] = i.loc[i['Product'] == 'STORE', 'Revenue']

df_combined = pd.concat(df_list).sort_index()
             
df_combined

Answer 1

我们可以使用pivot_table两次，然后使用列表理解将MultiIndex压平，最后使用pd.concat创建最终数据：

piv1 = df.pivot_table(
    index=["PO"], 
    columns='Year', 
    values="Revenue"
).reset_index(drop=True).add_suffix(" Total")

piv2 = df.pivot_table(
    index=["PO"], 
    columns=["Year", "Product"], 
    values="Revenue"
).reset_index(drop=True)
piv2.columns = [f"{c1} {c2}" for c1, c2 in piv2.columns]

df = pd.concat([df, piv1, piv2], axis=1)

   Year Customer  Product  PO  Revenue  2019 Total  2020 Total  2019 DIGITAL  \
0  2019   Cust 1  DIGITAL  P1      100       100.0         NaN         100.0   
1  2020   Cust 1  DIGITAL  P2      120         NaN       120.0           NaN   
2  2019   Cust 2    STORE  P3      240       240.0         NaN           NaN   
3  2019   Cust 1  DIGITAL  P4      200       200.0         NaN         200.0   
4  2019   Cust 2  DIGITAL  P5      110       110.0         NaN         110.0   
5  2020   Cust 2    STORE  P6      100         NaN       100.0           NaN   
6  2020   Cust 3  DIGITAL  P7      120         NaN       120.0           NaN   
7  2020   Cust 3    STORE  P8      180         NaN       180.0           NaN   

   2019 STORE  2020 DIGITAL  2020 STORE  
0         NaN           NaN         NaN  
1         NaN         120.0         NaN  
2       240.0           NaN         NaN  
3         NaN           NaN         NaN  
4         NaN           NaN         NaN  
5         NaN           NaN       100.0  
6         NaN         120.0         NaN  
7         NaN           NaN       180.0