Create a df with simple combination from group by an existent dataframe in pandas

user_14243063 0
Viewed 87

I'm trying to build a pandas df crossing rows by couple. I will explain in a better way:

in my df, there are several groups which have two values for each row, so that the df is build like:

Group Car Value
  A    x    0.1
  A    y    0.5
  A    z    0.12
  B    x    1.1
  B    w    1.26
  B    y    0.7

Whay I'm trying to do is build a new df in which each car belonging to a group is in the same row of another car in the same group, for all the possible combinations within a group (in a sort of simple combination).

Note: cars have different values in different group.

The df that I would like is:

Car1 Car2 Value1 Value2
 x    y     0.1    0.5
 x    z     0.1    0.12
 y    z     0.5    0.12
 x    w     1.1    1.26
 x    y     1.1    0.7
 w    y     1.26   0.7

I've tried using something like:

 pd.DataFrame([[np.hstack([g.car[prev],g.car[cur],g.value[prev],g.value[cur]]) 
                for _,g in df.groupby(['group'])] 
                for prev, cur in zip(g.index, g.index[1:])],
                columns=['car1','car2','value1','value2']
              )

but it doesn't solve my issue.

Ask me if my question isn't clear.

pandas combinations dataframe group-by python
2 Answers

Let us groupby the df on column Group then for each grouped frame map it to function f which computes all the combinations of length 2 of cars in that group:

from itertools import combinations

def f(g):
    for c in combinations(g.index, r=2):
        yield np.hstack(g.loc[c, ['Car', 'Value']].T.values)

a = np.vstack([*f(g)] for _, g in df.groupby('Group'))
d = pd.DataFrame(a, columns=['Car1', 'Car2', 'Value1', 'Value2'])

  Car1 Car2 Value1 Value2
0    x    y    0.1    0.5
1    x    z    0.1   0.12
2    y    z    0.5   0.12
3    x    w    1.1   1.26
4    x    y    1.1    0.7
5    w    y   1.26    0.7
user_128331660

You use a self join and filter results:

dfn=df.assign(row=df.groupby('Group').cumcount())

dfn.merge(dfn, on=['Group'], suffixes=('1','2')).query('row1 < row2').drop(['row1','row2'], axis=1)

Output:

   Group Car1  Value1 Car2  Value2
1      A    x    0.10    y    0.50
2      A    x    0.10    z    0.12
5      A    y    0.50    z    0.12
10     B    x    1.10    w    1.26
11     B    x    1.10    y    0.70
14     B    w    1.26    y    0.70
user_63615310
Related