How generate all pairs of values, from the result of a groupby, in a pandas dataframe
Its simple use itertools combinations inside apply and stack i.e
from itertools import combinations
ndf = df.groupby('ID')['words'].apply(lambda x : list(combinations(x.values,2)))
.apply(pd.Series).stack().reset_index(level=0,name='words')
ID words
0 1 (word1, word2)
1 1 (word1, word3)
2 1 (word2, word3)
0 2 (word4, word5)
0 3 (word6, word7)
1 3 (word6, word8)
2 3 (word6, word9)
3 3 (word7, word8)
4 3 (word7, word9)
5 3 (word8, word9)
To match you exact output further we have to do
sdf = pd.concat([ndf['ID'],ndf['words'].apply(pd.Series)],1).set_axis(['ID','WordsA','WordsB'],1,inplace=False)
ID WordsA WordsB
0 1 word1 word2
1 1 word1 word3
2 1 word2 word3
0 2 word4 word5
0 3 word6 word7
1 3 word6 word8
2 3 word6 word9
3 3 word7 word8
4 3 word7 word9
5 3 word8 word9
To convert it to a one line we can do :
combo = df.groupby('ID')['words'].apply(combinations,2)\
.apply(list).apply(pd.Series)\
.stack().apply(pd.Series)\
.set_axis(['WordsA','WordsB'],1,inplace=False)\
.reset_index(level=0)
You can use groupby
with apply
and return DataFrame
, last add reset_index
for remove second level and then for create column from index:
from itertools import combinations
f = lambda x : pd.DataFrame(list(combinations(x.values,2)),
columns=['wordA','wordB'])
df = (df.groupby('ID')['words'].apply(f)
.reset_index(level=1, drop=True)
.reset_index())
print (df)
ID wordA wordB
0 1 word1 word2
1 1 word1 word3
2 1 word2 word3
3 2 word4 word5
4 3 word6 word7
5 3 word6 word8
6 3 word6 word9
7 3 word7 word8
8 3 word7 word9
9 3 word8 word9
You can define a custom function that is applied to each group. Both input and output are a dataframe:
def combine(group):
return pd.DataFrame.from_records(itertools.combinations(group.word, 2))
df.groupby('ID').apply(combine)
Result:
0 1
ID
1 0 word1 word2
1 word1 word3
2 word2 word3
2 0 word4 word5
3 0 word6 word7
1 word6 word8
2 word6 word9
3 word7 word8
4 word7 word9
5 word8 word9