Max
Reputation: 471
Is there a possibility to restructure a pivot table?
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
Upvotes: 0
Views: 49
Answers (1)
Tranbi
Reputation: 12731
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
Upvotes: 1
Related Questions
- How to reshape a Pivot Table?
- Pandas pivot_table force column structure?
- 'restructure' dataframe in python
- How to simplify the use of pivot in pandas?
- Reshape pivot table in pandas
- Reordering columns/rows of a pivot_table?
- alternative way to construct pivot table
- Pandas pivot table with multiple columns at once
- pandas pivot table - rearrange
- Un-pivoting/Stacking a pivot table with Python Pandas