0

I have been stuck on this for a while, have tried various permutation. I have a large dataset that needs to be transformed from image [Left] to image [Right]

I believe it requires unstacking, stacking and groupby...but can't seem to figure it out.

enter image description here

print(df.head(200))
                                  epic_fullname        key            status
0     [XTA-10261] - EQD Delta One and EMD EPIC   XTBOW-970            Closed
17    [XTA-10261] - EQD Delta One and EMD EPIC   XTA-12114         Scheduled
49    [XTA-10261] - EQD Delta One and EMD EPIC   XTA-12044          Resolved
54    [XTA-10261] - EQD Delta One and EMD EPIC   XTA-12036     Awaiting Test
99    [XTA-10261] - EQD Delta One and EMD EPIC   XTA-11849          Resolved
100   [XTA-10261] - EQD Delta One and EMD EPIC   XTA-11840          Resolved
102   [XTA-10261] - EQD Delta One and EMD EPIC   XTA-11832          Resolved
113   [XTA-10261] - EQD Delta One and EMD EPIC   XTA-11794          Resolved
116   [XTA-10261] - EQD Delta One and EMD EPIC   XTA-11783          Resolved
294   [XTA-10261] - EQD Delta One and EMD EPIC   XTA-10739          Resolved
302   [XTA-10261] - EQD Delta One and EMD EPIC   XTA-10258          Resolved
303   [XTA-10261] - EQD Delta One and EMD EPIC   XTA-10257          Resolved
304   [XTA-10261] - EQD Delta One and EMD EPIC   XTA-10256          Resolved
374  [XTA-11427] - IDX MIGTN - Valuation & Risk  XTA-11770          Resolved
376  [XTA-11427] - IDX MIGTN - Valuation & Risk  XTA-11703          Resolved
394  [XTA-11427] - IDX MIGTN - Valuation & Risk  XTA-11529          Resolved
397  [XTA-11427] - IDX MIGTN - Valuation & Risk  XTA-11504          Resolved
426  [XTA-11427] - IDX MIGTN - Valuation & Risk  XTA-11265          Resolved
427  [XTA-11427] - IDX MIGTN - Valuation & Risk  XTA-11264    In Development
428  [XTA-11427] - IDX MIGTN - Valuation & Risk  XTA-11263          Resolved
429  [XTA-11427] - IDX MIGTN - Valuation & Risk  XTA-11262          Resolved
438  [XTA-11427] - IDX MIGTN - Valuation & Risk  XTA-11089          Resolved
482  [XTA-11427] - IDX MIGTN - Valuation & Risk  XTA-10858  Awaiting Release
516  [XTA-11427] - IDX MIGTN - Valuation & Risk  XTA-10691          Resolved
519  [XTA-11427] - IDX MIGTN - Valuation & Risk  XTA-10686          Resolved
Karun
  • 561
  • 7
  • 23

1 Answers1

1

You'll need an unstack operation:

v = df.set_index(['epic_fullname', 'status'])

df = v.set_index(
         v.groupby(level=[0, 1]).cumcount(), append=True
     ).key\
      .unstack(-2)\
      .fillna('')

enter image description here You'll also need an extra index column so that you can unstack. The groupby + cumcount operation is for that purpose.

cs95
  • 379,657
  • 97
  • 704
  • 746
  • That is exactly What I need. However I get the following error: df = v.set_index(v.groupby(level=[0, 1].cumcount(), append=True).key.unstack(-2).fillna('')) AttributeError: 'list' object has no attribute 'cumcount' – Karun Mar 08 '18 at 16:52
  • @Karun That's weird, I forgot a bracket when copying my code here. Check again please. – cs95 Mar 08 '18 at 16:55