基于另一个列值从列中提取值

Question

我有一个Dataframe df，如下所示：

                    Warehouse        Date                Count
0     Delhivery Goa Warehouse     2022-05-12                83
1     Delhivery Goa Warehouse     2022-05-15                 1
2     Delhivery Goa Warehouse     2022-05-18               100
3     Delhivery Tauru Warehouse   2022-05-19               100
4     Delhivery Tauru Warehouse   2022-05-20               100

另一个dataframe df_orig，如下所示：

              index                          Goa    Tauru    
0     2022-05-12Delhivery Goa Warehouse     100.0     0.0   
1     2022-05-15Delhivery Goa Warehouse     100.0     0.0   
2     2022-05-18Delhivery Goa Warehouse     100.0     0.0   
3     2022-05-20Delhivery Tauru Warehouse    0.0     50.0   
4     2022-05-19Delhivery Tauru Warehouse    0.0     70.0   

如何根据df_orig的仓库列和日期列的组合从df列中选择值

预期产出：

                    Warehouse        Date                Count      original
0     Delhivery Goa Warehouse     2022-05-12                83       100
1     Delhivery Goa Warehouse     2022-05-15                 1       100
2     Delhivery Goa Warehouse     2022-05-18               100       100
3     Delhivery Tauru Warehouse   2022-05-19               100       70
4     Delhivery Tauru Warehouse   2022-05-20               100       50

我的方法：

df['index1'] = str(df['Date']) + str(df['Warehouse'])
original = []
for index, row in df.iterrows():
    if row['index1'] == df_orig['index']:
        original.append(????)

Answer 1

将Date和Warehouse列串连在第一个dataframe中，并计算除首先使用iloc[:,1:]之外的所有列的值之和，然后合并两个数据格式，最后只取感兴趣的列：

(df
.assign(index=df['Date'] + df['Warehouse'])
.merge(df_orig.assign(original=df_orig.iloc[:,1:].sum(1)))
)[['Warehouse', 'Date', 'Count', 'original']]

输出：

                   Warehouse        Date  Count  original
0    Delhivery Goa Warehouse  2022-05-12     83     100.0
1    Delhivery Goa Warehouse  2022-05-15      1     100.0
2    Delhivery Goa Warehouse  2022-05-18    100     100.0
3  Delhivery Tauru Warehouse  2022-05-19    100      70.0
4  Delhivery Tauru Warehouse  2022-05-20    100      50.0

Answer 2

merge()在这里工作。还可以使用df_orig方法将df行中的值之和映射到map()行。由于index列在df_orig中与df中的Date和Warehouse列的连接相同，所以首先将这些列连接起来，以使映射键匹配。

# map the sum of the values in df_orig to df.Warehouse via df_orig.index
df['original'] = (df['Date'].astype(str)+df['Warehouse']).map(df_orig.set_index('index').sum(1))

​

​