减去连接到多个列值的两个Pandas DataFrame [英] Subtract two Pandas DataFrames joined on multiple column values

问题描述

我试图从DataFrame B的一列的值中减去DataFrame A的一列的值，但前提是多个列的值彼此相等.

I am trying to subtract the values of a column in DataFrame A, from the values from a column in DataFrame B, but only if multiple column values are equal to each other.

假设(虚构):

DataFrame A:
Index    Department  Speciality   TargetMonth Capacity
1        Sales       Cars         2019-1      150
2        Sales       Cars         2019-2      120
3        Sales       Furniture    2019-1      110
4        IT          Servers      2019-1      100

DataFrame B:
Index    Department  Speciality   TargetMonth Required
1        Sales       Cars         2019-1      100
2        Sales       Cars         2019-2      120
3        IT          Servers      2019-1      50
4        Sales       Furniture    2019-1      50

我故意将DataFrame B中的索引3和4的顺序与A进行了交换.我的目标是从DataFrame A的Capacity列中减去DataFrame B的Required列，将其作为必需的容量小时数，并生成另一个(不一定要进行排序)列表:

I swapped the order of Index 3 and 4 in DataFrame B compared to A on purpose. My goal is to subtract DataFrame B its Required column as being required capacity hours from DataFrame A's Capacity column and resulting in another, not necessarily required to be sorted, list:

Index    Department  Speciality   TargetMonth Result
1        Sales       Cars         2019-1      50
2        Sales       Cars         2019-2      0
3        Sales       Furniture    2019-1      60
4        IT          Servers      2019-1      50

因此，从技术上讲，仅在所有列值彼此匹配且不基于顺序的情况下仅相减，因为一个列表或另一列表中可能缺少某些行.

So, technically, subtract only, and only if all column values match each other and not based on order, as some rows may be missing in one list or the other.

我可以用一些for循环和条件来解决这个问题，但是我想有一种干净利落的熊猫方法用.subtract解决这个问题，尽管这是我目前坚持的连接"部分.

I could solve this with some for loops and conditions but I suppose there's a clean and neat Pandas way to solve this with .subtract although it's the "joining" part on which I am currently stuck.

感谢您的时间.

推荐答案

这就是为什么Index如此有用的原因，减法将在索引(行和列)上对齐.

This is why the Index is so useful, subtraction will be aligned on the indices (both rows and columns).

dfA = dfA.set_index(['Department', 'Speciality', 'TargetMonth'])
dfB = dfB.set_index(['Department', 'Speciality', 'TargetMonth'])

dfA.sub(dfB.rename(columns={'Required': 'Capacity'}), fill_value=0)

                                   Capacity
Department Speciality TargetMonth          
IT         Servers    2019-1             50
Sales      Cars       2019-1             50
                      2019-2              0
           Furniture  2019-1             60

这篇关于减去连接到多个列值的两个Pandas DataFrame的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！