0

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.

Karl Knechtel
  • 62,466
  • 11
  • 102
  • 153
Steve Gutierrez
  • 111
  • 1
  • 6
  • 1
    Does this answer your question? [How can I pivot a dataframe?](https://stackoverflow.com/questions/47152691/how-can-i-pivot-a-dataframe) – Karl Knechtel Feb 02 '23 at 02:56
  • [Please try](https://meta.stackoverflow.com/questions/261592) to look for existing questions before posting on Stack Overflow. I can literally [copy and paste your title into a search engine](https://duckduckgo.com/?q=Can+pandas+be+used+to+pivot%2Ftranspose+a+data+frame) and get relevant results right off the top. – Karl Knechtel Feb 02 '23 at 02:57
  • Thank you for your response and thank you for editing my pasted data frame mess. I was searching on SO for how to get those to show up formatted properly. I did look at that Q but failed to see a match to my DF example, I'm sure it is there but I don't parse it. Would love to know how to get the formatting right on the copy and pasted output. – Steve Gutierrez Feb 02 '23 at 03:19
  • Now that you have received an answer, can you see how it relates to the answers at the other question? – Karl Knechtel Feb 02 '23 at 15:00

2 Answers2

1

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
Corralien
  • 109,409
  • 8
  • 28
  • 52
1

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
Jamiu S.
  • 5,257
  • 5
  • 12
  • 34