Merge dataframe rows if 2 columns are equal (but different order)-CodePudding

In a padas dataframe, I want to check if I have rows like this:

Var1   Var2   Value1   Value2
a      b      1       NaN
b      a      NaN     2

And if it's the case, merge them like this:

Var1   Var2   Value1   Value2
a      b      1       2

Some idea?

If Var1 & Var2 == Var2 & Var1...?

CodePudding user response：

You can groupby on sets created from Var1 and Var2 -- sets are useful as set(a,b) is the same as set(b,a). But sets by themselves cannot be used in indexing as they are not hashable, so we need to use frozenset. Once grouped we can add the Value's and take first element for Var's. In short, you can do this:

grouper = [frozenset(t) for t in df[['Var1','Var2']].values]
df.groupby(grouper, as_index = False).agg({'Var1':'first', 'Var2':'first', 'Value1':'sum', 'Value2':'sum'})

output


    Var1    Var2    Value1  Value2
0   a       b       1.0     2.0