Search code examples
pythonpandaspivotunpivotmelt

How to change ColumnB to headers and headers to ColumnB

I don't know if this is a called a melt or unmelt, or pivot or unpivot, but I want to know if I can change my ColumnB to headers, starting in ColumnF (and data points under these headers), and likewise, change the headers, starting in ColumnF, to ColumnB.

Here is a screen shot of what I'm looking at.

enter image description here

Here is my data.

    qyear   Line_Item                              IDRSSD   schedule_code   RCON3543    RCON3545    RCON3547    RCON3548
97  2011Q1  TRDG ASTS ASTBCKD SCTY AUTO LN         1004368  RCD             7360        7360        7583        $7,583 
98  2012Q1  TRDG ASTS ASTBCKD SCTY OTHR CNSMR LN   1004368  RCD             17807       17807       18092       18092
99  2013Q1  TRDG ASTS ASTBCKD SCTY CMRL  IND LN    1004368  RCD             20073       20073       20971       20971
100 2011Q1  TRDG ASTS ASTBCKD SCTY OTHR LN         1004470  RCD             0       0       0       0
101 2012Q1  TRDG ASTS RTND BNFCL INT SCTY          1004470  RCD             0       0       0       0
102 2013Q1  TRDG ASTS EQT SCTY RDLY DET FV         1004470  RCD             0       0       0       0
103 2012Q1  TRDG ASTS EQT SCTY OTHR                1005075  RCD             0       0       0       0
104 2013Q1  TRDG ASTS LN PEND SCTY                 1005075  RCD             0       0       0       0
105 2011Q1  1ST ITEMIZED AMT FOR OTHR TRDG ASTS    1005440  RCD             0       0       0       0
106 2012Q1  2ND ITEMIZED AMT FOR OTHR TRDG ASTS    1005440  RCD             0       0       0       0
107 2013Q1  3RD ITEMIZED AMT FOR OTHR TRDG ASTS    1005440  RCD             0       0       0       0
108 2011Q1  1ST ITEMIZED AMT FOR OTHR TRDG ASTS    1005459  RCD             0       0       0       0
109 2012Q1  2ND ITEMIZED AMT FOR OTHR TRDG ASTS    1005459  RCD             0       0       0       0
110 2011Q1  3RD ITEMIZED AMT FOR OTHR TRDG ASTS    100553   RCD             0       0       0       0
111 2012Q1  SFP TPS ISS FNCL INSTITUTION           100553   RCD             0       0       0       0
112 2011Q1  SFP TPS ISS RE INV TR                  1005552  RCD             0       0       0       0
113 2012Q1  SFP CORP SIMILAR LN 1                  005552   RCD             0       0       0       0
114 2013Q1  SFP 1T4 FMLY RES MBS ISS US GSE        1005552  RCD             0       0       0       0
115 2011Q1  SFP 1T4 FMLY RES MBS NOT ISS GSE       100562   RCD             0       0       0       0
116 2012Q1  TRDG MBS PT SCTY ISS GUAR FFG          100562   RCD             0       0       0       0
117 2013Q1  TRDG OTHR MBS ISS GUAR FFG             100562   RCD             0       0       0       0
118 2011Q1  TRDG ASSET MBS OTHR RES MBS            1005655  RCD             0       0       0       0
119 2012Q1  TRDG ASSET STRD FNC PROD CASH          1005655  RCD             0       0       0       0
120 2013Q1  TRDG OTHR STRD FNC PROD SYN            1005655  RCD             0       0       0       0
121 2011Q1  TRDG OTHR STRD FNC PROD HYBRID         1005851  RCD             0       0       0       0
122 2012Q1  TRDG ASSET OTHR DEBT SCTY CNSL         1005851  RCD             0       0       0       0

I would like to have the final view look like this.

enter image description here I noticed that a transpose almost works, but that will flip ColumnC and ColumnD, and I don't want to touch those two columns. I want to keep ColumnC and ColumnD where they are, move ColumnB to headers, and move the headers to ColumnB. Is it possible to do this? Thanks a lot!

Solution

  • I think you are looking at a combination of stack and unstack

    (df.set_index(['qyear','Line_Item','IDRSSD','schedule_code'])
   .stack()
   .unstack('qyear')
   .reset_index()
)

    Or unstack then transpose:

    (df.set_index(['qyear','Line_Item','IDRSSD','schedule_code'])
   .unstack(level=['Line_Item','IDRSSD','schedule_code'])
   .T.reset_index()
)

    Or melt then pivot:

    (df.melt(id_vars=['qyear','Line_Item','IDRSSD','schedule_code'])
   .pivot_table(index=['Line_Item','IDRSSD','schedule_code', 'variable'], 
                columns='qyear', values='value', aggfunc='first')
   .reset_index()
)