pivot a dataframe with repeating values-CodePudding

I have a dataframe like this:

df = pd.DataFrame(id:{1,2,3,4,4},
course:{english, art, math, english, chem},
result:{a,b,c,a,b})

I want to pivot this such that:

course  English art math sci chem 
id
1         a      -    -   -    -   
2         -      b    -   -    -
3         -      -    c   -    -
4         a      -    -   -    b

Note: There are repeated values in ID, results & Courses. Due to the duplicate values, I am not able to pivot it. Kindly help

CodePudding user response：

pivot works just fine. I think the issue is with your input. You have a set for each key, so the duplicates disappear. Use a list and it works.

# use a list for values
df = pd.DataFrame({'id':[1,2,3,4,4],
                   'course':['english', 'art', 'math', 'english', 'chem'],
                   'result':['a','b','c','a','b']})
# pivot and reindex
df.pivot(*df).reindex(columns=['english', 'art', 'math', 'sci', 'chem']).fillna('-')

CodePudding user response：

Let's use set_index and unstack:

df.set_index(['id','course'])['result'].unstack(fill_value='-')

Output:

course art chem english math
id                          
1        -    -       a    -
2        b    -       -    -
3        -    -       -    c
4        -    b       a    -