How to reshape my dataset in specific way?

user_12314164 0
Viewed 42

I have a dataset:

name    val
a       a1
a       a2
b       b1
b       b2
b       b3
c       c1

I want to make all possible permutations "names" which are not same. So desired result is:

name1    val1    name2   val2
a        a1      b       b1
a        a1      b       b2
a        a1      b       b3
a        a2      b       b1
a        a2      b       b2
a        a2      b       b3
a        a1      c       c1
a        a2      c       c2
b        b1      c       c1
b        b2      c       c1
b        b3      c       c1

How to do that? Id like to write a function that would make same operation with bigger table with same structure.

I would like to make it efficiently, since original data has several thousands rows

pandas dataframe function python
1 Answers

Easiest is to cross merge and query, if you have enough memory for few million rows, which is not too bad:

df.merge(df, how='cross', suffixes=['1','2']).query('name1 < name2')

Output:

   name1 val1 name2 val2
2      a   a1     b   b1
3      a   a1     b   b2
4      a   a1     b   b3
5      a   a1     c   c1
8      a   a2     b   b1
9      a   a2     b   b2
10     a   a2     b   b3
11     a   a2     c   c1
17     b   b1     c   c1
23     b   b2     c   c1
29     b   b3     c   c1
user_42384080
Related