Pandas group by result to columns

Question

I have dataframe like this:

x = pd.DataFrame({
    'audio': ['audio1', 'audio1', 'audio2', 'audio2', 'audio3', 'audio3'],
    'text': ['text1', 'text2', 'text3', 'text4', 'text5', 'text6'],
    'login': ['operator1', 'operator2', 'operator3', 'operator4', 'operator5', 'operator6'] 
})

i'm trying to aggregate it like this:

x1 = 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()

and it works but i also need to add grouped logins to row, to make row like this:

audio, text1, text2, leven, login1, login2

I tried something like lambda x : x.ait[0, 1] but it doesnt work

Answer 1

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

Answer 2

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