Access Risk ID User ID
SOD05 144
SOD05A 74
SOD06 140
SOD07A 50
SOD08A 30
... ...
SOD77B 30
SOD78 30
SOD78A 30
SOD78B 30
SOD80 66
Hi all,
I have the above pivot. Instead of the count of User IDs I want each User ID related to the column Access Risk ID to be listed (so 144 rows for SOD05)..........
I have not found an answer to this issue on Stackoverflow. If one of you could help me that would be extremely helpful Please help~! Code I used:
pivot1 = pd.pivot_table(dfpivot, index='Access Risk ID', values = ['User ID'], aggfunc='count')
pivot1
Input:
| User ID | Access Risk ID | Executed within the Q? |
|--------------|----------------|:----------------------:|
| ACHINE | SOD05 | TRUE |
| ACHINE | SOD05 | FALSE |
| AHOFMA | SOD05 | TRUE |
| AHOFMA | SOD05 | TRUE |
| AZILZ | SOD05 | TRUE |
| AZILZ | SOD05 | TRUE |
| BKACZM | SOD05 | TRUE |
| BKACZM | SOD05 | TRUE |
| CAFERR | SOD05 | TRUE |
| CAFERR | SOD05 | FALSE |
| CAUTRE | SOD05 | FALSE |
| CAUTRE | SOD05 | FALSE |
| CDIERKES | SOD05 | TRUE |
| CDIERKES | SOD05 | FALSE |
| CHEISE | SOD05 | TRUE |
| CHEISE | SOD05 | FALSE |
| CMOLDO | SOD05 | TRUE |
| CMOLDO | SOD05 | FALSE |
| DAYBEK | SOD05 | TRUE |
| DAYBEK | SOD05 | FALSE |
| DCHLUDOVA | SOD05 | TRUE |
| DCHLUDOVA | SOD05 | FALSE |
| DDIC | SOD05 | FALSE |
| DDIC | SOD05 | FALSE |
| DVDSTRAETEN | SOD05 | TRUE |
| DVDSTRAETEN | SOD05 | FALSE |
| EGAVRY | SOD05 | TRUE |
| EGAVRY | SOD05 | TRUE |
| ETROCH | SOD05 | TRUE |
| ETROCH | SOD05 | TRUE |
| EVDHAEGEN | SOD05 | TRUE |
| EVDHAEGEN | SOD05 | FALSE |
| FF_BASIS | SOD05 | TRUE |
| FF_BASIS | SOD05 | FALSE |
| FF_BASIS_CON | SOD05 | FALSE |
| FF_BASIS_CON | SOD05 | FALSE |
| FF_CTAC | SOD05 | FALSE |
| FF_CTAC | SOD05 | FALSE |
| FF_DEBUG | SOD05 | FALSE |
| FF_DEBUG | SOD05 | FALSE |
| FF_DEBUG_01 | SOD05 | FALSE |
| FF_DEBUG_01 | SOD05 | FALSE |
| FF_DEBUG_02 | SOD05 | TRUE |
| FF_DEBUG_02 | SOD05 | FALSE |
| FF_DEBUG_FIN | SOD05 | TRUE |
| FF_DEBUG_FIN | SOD05 | FALSE |
| FF_DEBUG_PRD | SOD05 | FALSE |
| FF_DEBUG_PRD | SOD05 | FALSE |
| FF_DEBUG_RET | SOD05 | FALSE |
| FF_DEBUG_RET | SOD05 | FALSE |
| FF_DEBUG_SRC | SOD05 | FALSE |
| FF_DEBUG_SRC | SOD05 | FALSE |
| FF_DEBUGT | SOD05 | FALSE |
| FF_DEBUGT | SOD05 | FALSE |
| FF_FIN | SOD05 | FALSE |
| FF_FIN | SOD05 | FALSE |
| FF_FINT | SOD05 | FALSE |
| FF_FINT | SOD05 | FALSE |
| FPHILIPS | SOD05 | TRUE |
| FPHILIPS | SOD05 | FALSE |
| FSOYLU | SOD05 | TRUE |
| FSOYLU | SOD05 | TRUE |
| FVDVAEREN | SOD05 | TRUE |
| FVDVAEREN | SOD05 | FALSE |
| GANGAROVA | SOD05 | TRUE |
| GANGAROVA | SOD05 | FALSE |
| JDELANG | SOD05 | TRUE |
| JDELANG | SOD05 | TRUE |
| JDHONDT | SOD05 | TRUE |
| JDHONDT | SOD05 | FALSE |
| JKIMML | SOD05 | TRUE |
| JKIMML | SOD05 | TRUE |
| KAGRAS | SOD05 | TRUE |
| KAGRAS | SOD05 | FALSE |
| KFOUCA | SOD05 | TRUE |
| KFOUCA | SOD05 | FALSE |
| KMUELL | SOD05 | TRUE |
| KMUELL | SOD05 | TRUE |
| KREGIN | SOD05 | TRUE |
| KREGIN | SOD05 | FALSE |
| LBUGGENHOUT | SOD05 | TRUE |
| LBUGGENHOUT | SOD05 | FALSE |
| LBUYCK | SOD05 | TRUE |
| LBUYCK | SOD05 | FALSE |
| LCROMBRUGGE | SOD05 | TRUE |
| LCROMBRUGGE | SOD05 | FALSE |
| LLAMER | SOD05 | TRUE |
| LLAMER | SOD05 | FALSE |
| LPUTMANS | SOD05 | TRUE |
| LPUTMANS | SOD05 | FALSE |
| LSTANI | SOD05 | TRUE |
| LSTANI | SOD05 | TRUE |
| MDEMETSER | SOD05 | TRUE |
| MDEMETSER | SOD05 | FALSE |
| MIGNACEK | SOD05 | FALSE |
| MIGNACEK | SOD05 | FALSE |
| MINDEN | SOD05 | TRUE |
| MINDEN | SOD05 | TRUE |
| MMARKO | SOD05 | FALSE |
| MMARKO | SOD05 | FALSE |
| MVERVR | SOD05 | TRUE |
| MVERVR | SOD05 | TRUE |
| MVINCK | SOD05 | TRUE |
| MVINCK | SOD05 | TRUE |
| NAGARC | SOD05 | FALSE |
| NAGARC | SOD05 | FALSE |
| OSS | SOD05 | FALSE |
| OSS | SOD05 | FALSE |
| PAUGUS | SOD05 | TRUE |
| PAUGUS | SOD05 | FALSE |
| PCRAEN | SOD05 | TRUE |
| PCRAEN | SOD05 | FALSE |
| PCUMAL | SOD05 | TRUE |
| PCUMAL | SOD05 | TRUE |
| PSTEPPE | SOD05 | TRUE |
| PSTEPPE | SOD05 | FALSE |
| RPEREZ15 | SOD05 | TRUE |
| RPEREZ15 | SOD05 | FALSE |
| RWILLE | SOD05 | TRUE |
| RWILLE | SOD05 | FALSE |
| SCOLSON | SOD05 | TRUE |
| SCOLSON | SOD05 | TRUE |
| SEYLEN | SOD05 | FALSE |
| SEYLEN | SOD05 | FALSE |
| SKOENN | SOD05 | TRUE |
| SKOENN | SOD05 | TRUE |
| SLECLERE | SOD05 | TRUE |
| SLECLERE | SOD05 | FALSE |
| SMARTE | SOD05 | TRUE |
| SMARTE | SOD05 | FALSE |
| SPOSSE | SOD05 | TRUE |
| SPOSSE | SOD05 | FALSE |
| SPULLI | SOD05 | TRUE |
| SPULLI | SOD05 | FALSE |
| STHUR | SOD05 | TRUE |
| STHUR | SOD05 | TRUE |
| STWYNS | SOD05 | TRUE |
| STWYNS | SOD05 | TRUE |
| SWOLF | SOD05 | TRUE |
| SWOLF | SOD05 | TRUE |
| VCUBIA | SOD05 | TRUE |
| VCUBIA | SOD05 | FALSE |
| VINBOM | SOD05 | TRUE |
| VINBOM | SOD05 | FALSE |
I want a pivot like this:
Access Risk ID User ID
SOD05 AHOFMA
AZILZ
BKACZM
EGAVRY
ETROCH
FSOYLU
JDELANG
JKIMML
KMUELL
LSTANI
MINDEN
MVERVR
MVINCK
PCUMAL
SCOLSON
SKOENN
STHUR
STWYNS
SWOLF
CodePudding user response：
Like I suggested in the comment, you can use groupby:
print(df.groupby(['Access Risk ID', 'User ID']).sum())
The sum in this example sums the remaining columns (in your case "Executed within the Q?") and you'll get:
Executed within the Q?
Access Risk ID User ID
SOD05 ACHINE 1
AHOFMA 2
AZILZ 2
BKACZM 2
CAFERR 1
... ...
STHUR 2
STWYNS 2
SWOLF 2
VCUBIA 1
VINBOM 1