5

Given a dataframe:

    id  value
0    1     a
1    2     b
2    3     c

I want to get a new dataframe that is basically the cartesian product of each row with each other row excluding itself:

    id  value id_2 value_2
0    1     a     2    b
1    1     a     3    c
2    2     b     1    a
3    2     b     3    c
4    3     c     1    a
5    3     c     2    b

This is my approach as of now. I use itertools to get the product and then use pd.concat with df.loc to get the new dataframe.

from itertools import product

ids = df.index.values
ids_1, ids_2 = list(zip(*filter(lambda x: x[0] != x[1], product(ids, ids))))

df_new = pd.concat([df.loc[ids_1, :].reset_index(), df.loc[ids_2, :].reset_index()], 1).drop('index', 1)

df_new

   id value  id value
0   1     a   2     b
1   1     a   3     c
2   2     b   1     a
3   2     b   3     c
4   3     c   1     a
5   3     c   2     b

Is there a simpler way?

cs95
  • 379,657
  • 97
  • 704
  • 746

3 Answers3

7

We want to get the indices for the upper and lower triangles of a square matrix. Or in other words, where the identity matrix is zero

np.eye(len(df))

array([[ 1.,  0.,  0.],
       [ 0.,  1.,  0.],
       [ 0.,  0.,  1.]])

So I subtract it from 1 and 

array([[ 0.,  1.,  1.],
       [ 1.,  0.,  1.],
       [ 1.,  1.,  0.]])

In a boolean context and passed to np.where I get exactly the upper and lower triangle indices.

i, j = np.where(1 - np.eye(len(df)))
df.iloc[i].reset_index(drop=True).join(
    df.iloc[j].reset_index(drop=True), rsuffix='_2')

   id value  id_2 value_2
0   1     a     2       b
1   1     a     3       c
2   2     b     1       a
3   2     b     3       c
4   3     c     1       a
5   3     c     2       b
piRSquared
  • 285,575
  • 57
  • 475
  • 624
3

This can be done entirely in pandas:

df.loc[:, 'key_col'] = 1 # create a join column that will give us the Cartesian Product

(df.merge(df, df, on='key_col', suffixes=('', '_2'))
 .query('id != id_2') # filter out joins on the same row
 .drop('key_col', axis=1)
 .reset_index(drop=True))

Or if you don't want to have to drop the dummy column, you can temporarily create it when calling df.merge:

(df.merge(df, on=df.assign(key_col=1)['key_col'], suffixes=('', '_2'))
 .query('id != id_2') # filter out joins on the same row
 .reset_index(drop=True))
Gulzar
  • 23,452
  • 27
  • 113
  • 201
KevinH
  • 586
  • 1
  • 8
  • 12
  • 1
    An interesting solution. But I wonder about creating a dummy column just for the merge... – cs95 Aug 01 '17 at 00:10
3

I had this problem before , this is my solution ..

import itertools
import pandas as pd 
c = list(itertools.product(DF.id.tolist(), DF.id.tolist()))
Dic=dict(zip(DF.id, DF.value))
df = pd.DataFrame(c , columns=['id', 'id_2'])
df[['value','value_2']]=df.apply(lambda x:x.map(Dic))
df.loc[df.id!=df.id_2,:]


Out[125]: 
   id  id_2 value value_2
1   1     2     a       b
2   1     3     a       c
3   2     1     b       a
5   2     3     b       c
6   3     1     c       a
7   3     2     c       b
BENY
  • 317,841
  • 20
  • 164
  • 234