How to add rows with identical items in different columns in Pandas together

user_12228905 0
Viewed 58

I have a sample dataframe that looks like below. I'd like to eventually group row 1 and row 3 together, since they contain identical items in different columns.

x    y      count
a,b  b,a      5
a,c  c,a      2
b,a  a,b      1

I've spent a lot of time trying to solve this, but have not encountered a good solution yet. What steps should I take to reach the below final dataframe?

x    y      count
a,b  b,a      5+1
a,c  c,a      2
pandas python
2 Answers

You can try:

df.groupby((df.x + df.y).str.replace(',', '').apply(lambda x: ''.join(sorted(x)))
           ).agg({'x': 'first', 'y': 'first', 'count': sum}).reset_index(drop=True)

OUTPUT:

     x    y  count
0  a,b  b,a      6
1  a,c  c,a      2
user_154380330

Slightly different approach.

Sort x and y rowwise using np.sort on axis=1:

cols = ['x', 'y']
df[cols] = np.sort(df[cols].values, axis=1)

     x    y  count
0  a,b  b,a      5
1  a,c  c,a      2
2  a,b  b,a      1

Then a standard groupby aggregate:

df = df.groupby(cols, as_index=False).aggregate(count=('count', 'sum'))

     x    y  count
0  a,b  b,a      6
1  a,c  c,a      2

Complete Code:

import numpy as np
import pandas as pd

df = pd.DataFrame({
    'x': ['a,b', 'a,c', 'b,a'],
    'y': ['b,a', 'c,a', 'a,b'],
    'count': [5, 2, 1]
})

cols = ['x', 'y']
df[cols] = np.sort(df[cols].values, axis=1)
df = df.groupby(cols, as_index=False).aggregate(count=('count', 'sum'))
user_154978880
Related