CODEWITHSUNDEEP
pythonpandas
Andre
Andre

Reputation: 247

I need to unstack or pivot keeping duplicated index

I would like to unstack a column but also keep the duplicated index.

df = pd.DataFrame( data={'Order': ['123', '123','123'],\
'Ticket': ['987', '987','987'],'Status': ['Added', 'Removed', 'Added'], \
'Dates': ['2016-02-15', '2017-12-24', '2018-01-24']})

I tried this:

df.pivot_table(index=['Order', 'Ticket' ], columns='Status', values='Dates', aggfunc= 'first')

and also tried this:

df.insert(0, 'count', df.groupby(['Order', 'Ticket']).cumcount())
df.pivot(index='count', columns='Status', values='Dates')

Looking to get result like this:
1

 # Order    Ticket      Added        Removed
 # 123      987        2016-02-15   2017-12-24
 # 123      987        2018-01-24    NaT

and later calculate the duration.

Any help would be appreciated. Thank you.

Upvotes: 1

Views: 68

Answers (1)

sammywemmy
sammywemmy

Reputation: 28709

Try this :

df.insert(0, "count", df.groupby(["Order", "Ticket", "Status"]).cumcount())
df.set_index(["count", "Order", "Ticket", "Status"]).unstack()


                                    Dates
              Status       Added    Removed
count   Order   Ticket      
0       123      987    2016-02-15  2017-12-24
1       123      987    2018-01-24  NaN

Upvotes: 2

Related Questions