CODEWITHSUNDEEP
pandasdataframepivot-table
PMelan
PMelan

Reputation: 23

Pivot/Crosstab/Unstack a Dataframe

There is a necessity to break out the following data into a pivot table without any need for aggregation. Given the normalization, I am trying to create a table that pivots the data so I can analyze times per incident_number.

Here is the Dataframe:

|incident|status|time|unit_num|
|123|dispatch|1/19/2019 09:04:59|ambulance1|
|123|enroute|1/19/2019 09:06:59|ambulance1|
|123|onscene|1/19/2019 09:14:59|ambulance1|
|123|available|1/19/2019 09:24:59|ambulance1|
|123|dispatch|1/19/2019 09:04:59|ambulance2|
|123|enroute|1/19/2019 09:08:59|ambulance2|
|123|onscene|1/19/2019 09:13:59|ambulance2|
|123|available|1/19/2019 09:34:59|ambulance2|

what I am trying to accomplish by either pivot/unstack is the following layout that is not producing the desired results

Incident | Unit | Dispatch | Enroute | Onscene | Available
123 | ambulance1 | associated times
123 | ambulance2 | associated times

Any help would be greatly appreciated

Thank you.

Upvotes: 1

Views: 241

Answers (1)

Nk03
Nk03

Reputation: 14949

IIUC, you can try:

df = (
    df.pivot_table(
        index=['incident', 'unit_num'],
        columns='status',
        values='time',
        aggfunc=''.join)
    # .add_suffix('_time')
    .rename_axis(columns=None)
    .reset_index()
)

OUTPUT:

   incident    unit_num           available            dispatch  \
0       123  ambulance1  1/19/2019 09:24:59  1/19/2019 09:04:59   
1       123  ambulance2  1/19/2019 09:34:59  1/19/2019 09:04:59   

              enroute             onscene  
0  1/19/2019 09:06:59  1/19/2019 09:14:59  
1  1/19/2019 09:08:59  1/19/2019 09:13:59

Upvotes: 1

Related Questions