Can pandas be used to pivot/transpose a data frame-CodePudding

I am currently have a table that I need to transform. The data set looks like this:

  Period   Course Teacher
0      1      Sci  Teach1
1      2      Art  Teach1
2      H     Span  Teach1
3      1       PE  Teach2
4      2       CS  Teach2
5      H     Conf  Teach2
6      1   AP Art  Teach3
7      2  Dig Art  Teach3
8      H   Health  Teach3

I would like it to look like the following:

    Teacher       H      P1       P2
0  Teacher1    Span     Sci      Art
1  Teacher2    Conf      PE       CS
2  Teacher3  Health  AP Art  Dig Art

Not sure how to go about this, or if it is something pandas would handle.

I have tried df.transpose which results in:

                    0        1        2  ...        6         7        8
Period          1        2        H  ...        1         2        H
 Course       Sci      Art     Span  ...   AP Art   Dig Art   Health
 Teacher   Teach1   Teach1   Teach1  ...   Teach3    Teach3   Teach3

I have tried using df.groupby(['Teacher']) which returns an error grouper, exclusions, obj = get_grouper

I have searched for pivot, melt, transform. Not sure of the language needed to search for examples of this type.

CodePudding user response：

Here is a simple approach using df.pivot():

df = df.pivot(index="Teacher", columns="Period", values="Course").reset_index()
df.columns = ["Teacher", "P1", "P2", "H"]
df = df[["Teacher", "H", "P1", "P2"]]

print(df)

  Teacher       H      P1       P2
0  Teach1    Span     Sci      Art
1  Teach2    Conf      PE       CS
2  Teach3  Health  AP Art  Dig Art

CodePudding user response：

pivot_table is definitively what you are looking for:

>>> (df.assign(Period=df['Period'].mask(df['Period'].str.isdigit(), other='P' df['Period']))
       .pivot_table(index='Teacher', columns='Period', values='Course', aggfunc='first')
       .rename_axis(columns=None).reset_index())

  Teacher       H      P1       P2
0  Teach1    Span     Sci      Art
1  Teach2    Conf      PE       CS
2  Teach3  Health  AP Art  Dig Art