Pandas finding pairs in grouby function

user_6552371 0
Viewed 53

I have a huge dataframe in pandas that has following format:

period  from_       to_        value
2020-07 Jonny       Karl       15.00
2020-08 Matt        Jonny      5.00
2020-08 Matt        Karl       5.00
2020-08 Matt        Karl       10.00
2020-08 Jonny       Matt       10.00

Where I have values of one person need to pay to another person by a period of the year. The name of those persons repeat themselves over the dataset.

So I want to see per period of year the values one person owes to another person. For that, I can simply use:

sum_df = df.groupby([ "period", "from_", "to_"]).agg({"value": 'sum'})

But is here that lies my question, because I'm trying to figure out a performative way to do this aggregating function where it could "identify": if person A owes 5$ to person B. and person B owe 10$ to person A. It should return that person B owe for that period 5$ to person A. Resulting in the following dataframe:

period  from_       to_        value
2020-07 Jonny       Karl       15.00
2020-08 Matt        Karl       15.00
2020-08 Jonny       Matt       5.00

Someone could give me a direction in which I could follow to reach that?

pandas-groupby pandas python-3.x python
2 Answers

Let me post a solution here for you to explore. I'll add explanation later.

pairs = df[['from_','to_']]
sorted_pairs = np.sort(df[['from_','to_']].values, axis=1)

(df['value'].mul(np.where((pairs==sorted_pairs).all(1), 1, -1))
     .groupby([df['period'],sorted_pairs[:,0], sorted_pairs[:,1]])
     .sum()
     .reset_index(name='value')
)

Output:

    period level_1 level_2  value
0  2020-07   Jonny    Karl   15.0
1  2020-08   Jonny    Matt    5.0
2  2020-08    Karl    Matt  -15.0
user_42384080

My suggestion is very tricky. First merge the dataframe from the groupby by itself, but comparing the columns from_ for to_ and to_ for from_. Subtract the values from the value and value_y columns that were generated and save this in a variable. With this variable it is possible to update the column in the original DF using update

df1 = df.groupby(['period','from_','to_'])['value'].sum().reset_index()

temp = df1.reset_index().merge(df1, 
                               left_on=['period', 'from_', 'to_'], 
                               right_on=['period', 'to_', 'from_'], 
                               suffixes=['', '_y'])

temp['value'] = temp['value'] - temp['value_y']
temp = temp[['index','period', 'from_', 'to_', 'value']]

temp.set_index('index', inplace=True)
df1.update(temp)

df1.head()
    period  from_   to_     value
0   2020-07 Jonny   Karl    15.0
1   2020-08 Jonny   Matt    5.0
2   2020-08 Matt    Jonny   -5.0
3   2020-08 Matt    Karl    15.0

here you can decide what to do with the data of people who owe nothing to anyone. If they are deleted from the DF, or set the column value to zero

#remove rows where value is equal to or less than zero
df1.loc[df1['value'] > 0]
#output:
    period  from_   to_     value
0   2020-07 Jonny   Karl    15.0
1   2020-08 Jonny   Matt    5.0
3   2020-08 Matt    Karl    15.0

#setting the value column to zero where it is negative
df1.loc[df1['value'] < 0, 'value'] = 0
#output:
    period  from_   to_     value
0   2020-07 Jonny   Karl    15.0
1   2020-08 Jonny   Matt    5.0
2   2020-08 Matt    Jonny   0.0
3   2020-08 Matt    Karl    15.0
user_58478540
Related