Pandas group by result to columns
Looking at your data frame, I am thinking of pivoting the data frame, below is my approach which makes use of groupby().cumcount()
and unstack
with some column formatting to create a pivotted dataframe.
Option1:
Then you could make use of df.apply
to apply the function
m = x.assign(k=x.groupby('audio').cumcount().add(1)).set_index(['audio','k']).unstack()
m.columns=[f"{a}{b}" for a,b in m.columns]
m = m.assign(leven=m.apply(lambda x:
Levenshtein.distance(x['text1'],x['text2']),1)).reset_index()
audio text1 text2 login1 login2 leven
0 audio1 text1 text2 operator1 operator2 1
1 audio2 text3 text4 operator3 operator4 1
2 audio3 text5 text6 operator5 operator6 1
Option2: (I would prefer this)
You can also use a list comprehension to do the same , just replace the last line with:
m = x.assign(k=x.groupby('audio').cumcount().add(1)).set_index(['audio','k']).unstack()
m.columns=[f"{a}{b}" for a,b in m.columns]
m = m.assign(leven=[Levenshtein.distance(a,b) for
a,b in zip(m['text1'],m['text2'])]).reset_index()
audio text1 text2 login1 login2 leven
0 audio1 text1 text2 operator1 operator2 1
1 audio2 text3 text4 operator3 operator4 1
2 audio3 text5 text6 operator5 operator6 1
Option3:
If location of leven
column is important, you can use df.insert
:
m=x.assign(k=x.groupby('audio').cumcount().add(1)).set_index(['audio','k']).unstack()
m.columns=[f"{a}{b}" for a,b in m.columns]
m.insert(2,'leven',[Levenshtein.distance(a,b) for a,b in zip(m['text1'],m['text2'])])
m=m.reset_index()
audio text1 text2 leven login1 login2
0 audio1 text1 text2 1 operator1 operator2
1 audio2 text3 text4 1 operator3 operator4
2 audio3 text5 text6 1 operator5 operator6
Is this what you are looking for:
x1 = x.groupby('audio',)['login'].agg(
[
('operator1', lambda x : x.iat[0]),
('operator2', lambda x : x.iat[1]),
('leven', lambda x: Levenshtein.distance(x.iat[0], x.iat[1])) #some function works with grouped text
]
).reset_index()
x2 = x.groupby('audio',)['text'].agg(
[
('text1', lambda x : x.iat[0]),
('text2', lambda x : x.iat[1]),
('leven', lambda x: Levenshtein.distance(x.iat[0], x.iat[1])) #some function works with grouped text
]
).reset_index()
x1.merge(x2)
audio operator1 operator2 leven text1 text2
0 audio1 operator1 operator2 1 text1 text2
1 audio2 operator3 operator4 1 text3 text4
2 audio3 operator5 operator6 1 text5 text6