How to Pivot table in Pandas-CodePudding

I'm trying to write code for pivot table using pandas,

Here is my sample excel file.

Name    Owner   Role    Can Edit    Can Read    Can Delete  Can download
John    Julan   Role1   Yes               No          No          No
Ricard  Julan   Role2   No                Yes         No          No
Sam     Hannah  Role2   No                 No         Yes         No
Julia   Hannah  Role1   No                 No         No          Yes
Katie   Julan   Role2   No                 Yes        No          Yes

and output should be like this:

Desired output

Here's my code

import pandas as pd
import numpy as np
df = pd.read_excel('pivot.xlsx')
table = pd.pivot_table(df, values=['Can Edit','Can Read','Can Delete','Can download'], index=['Owner'],columns=['Role'], aggfunc=np.sum)

But i don't get desired results

CodePudding user response：

You almost had it but you forgot "Name" in the index:

pd.pivot_table(df, index=['Name', 'Owner'], columns=['Role'],
               values=['Can Edit','Can Read','Can Delete','Can download'],
               aggfunc=np.sum)

Note that you do not need to specify the values if all the other columns are used:

pd.pivot_table(df, index=['Name', 'Owner'], columns=['Role'], aggfunc=np.sum)

output:

              Can Delete       Can Edit       Can Read       Can download      
Role               Role1 Role2    Role1 Role2    Role1 Role2        Role1 Role2
Name   Owner                                                                   
John   Julan          No   NaN      Yes   NaN       No   NaN           No   NaN
Julia  Hannah         No   NaN       No   NaN       No   NaN          Yes   NaN
Katie  Julan         NaN    No      NaN    No      NaN   Yes          NaN   Yes
Ricard Julan         NaN    No      NaN    No      NaN   Yes          NaN    No
Sam    Hannah        NaN   Yes      NaN    No      NaN    No          NaN    No