CODEWITHSUNDEEP
pythonpandaspandas-groupby
Karun
Karun

Reputation: 621

Transform dataframe where row data is used as columns

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

Upvotes: 0

Views: 102

Answers (1)

cs95
cs95

Reputation: 402293

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.

Upvotes: 1

Related Questions