Pandas Dataframe with two columns whose values need to alternate as values

Question

I have a pandas dataframe in the form:

name	class	#absence
name1	class1	1
name1	class2	5
name1	class3	3
name2	class1	6
name2	class2	4
name2	class3	7

I want to get it in the form:

name	class1	#absence	class2	#absence	class3	#absence
name1	class1	1	class2	5	class3	3
name2	class1	6	class2	4	class3	7

I have tried various combinations of groupby, pivot, stack etc.. but can't quite seem to get exactly what I need.

Thank you!

@mozway, he will not manage to pull in columns of #absence just by pivoting — NoobVB, Sep 26 '22 at 13:39
@NoobVB there is a bit of extra work for the exact output, but the core logic to pivot class and #absence is well described in the duplicate — mozway, Sep 26 '22 at 13:45

score -1 · Answer 1 · answered Sep 26 '22 at 13:46

You can use a core pivot, then rework the dataframe by selectively flattening the MultiIndex:

(df.assign(col=df.groupby('name').cumcount()+1)
   .pivot(index='name', columns='col')
   .sort_index(level='col', axis=1, sort_remaining=False)
   .pipe(lambda d: d.set_axis(d.columns.map(lambda x: f'{x[0]}{x[1]}'
                                            if x[0] == 'class' else x[0]),
                              axis=1))
   .reset_index()
)

output:

    name  class1  #absence  class2  #absence  class3  #absence
0  name1  class1         1  class2         5  class3         3
1  name2  class1         6  class2         4  class3         7

Wow....this is very elegant and works perfectly. It will take me an hour or so to unpack everything that you did here, but thank you...this is exactly what I was looking for : ) — Scott Gambale, Sep 26 '22 at 14:10

Pandas Dataframe with two columns whose values need to alternate as values

1 Answers1