CODEWITHSUNDEEP
pythonpandaspivot-table
Tahsin Alam
Tahsin Alam

Reputation: 157

How to create a pivot table on pandas DATE column and calculate time difference?

I have the following Dataframe,

D_DATE       BIN Number   Disposition    Unit Assigned        
2018-01-04    10005      SWO Issued      PLUMBING DIVISION     
2016-06-23    10005      SWO Issued      SCAFFOLD UNIT         
2016-06-23    10005      SWO Rescinded   SCAFFOLD UNIT         
2018-01-17    10005      SWO Rescinded   PLUMBING DIVISION  
2019-01-04    10006      SWO Rescinded   BEST SQUAD 
2018-12-21    10006      SWO Issued      BEST SQUAD 
2020-02-10    10006      SWO Issued      BEST SQUAD
2020-02-25    10006      SWO Rescinded   BEST SQUAD

df = pd.DataFrame({'D_DATE':['2018-01-04','2016-06-23','2016-06-23','2018-01-17','2019-01-04','2018-12-21','2020-02-10','2020-02-25'],
                    'BIN Number': ['10005', '10005', '10005', '10005', '10006','10006','10006','10006] ,
                   'Disposition': ['SWO Issued', 'SWO Issued', 'SWO Rescinded', 'SWO Rescinded','SWO Rescinded','SWO Issued','SWO Issued','SWO Rescinded'] ,
                   'Unit Assigned': ['PLUMBING DIVISION', 'SCAFFOLD UNIT', 'SCAFFOLD UNIT', 'PLUMBING DIVISION','BEST SQUAD','BEST SQUAD','BEST SQUAD','BEST SQUAD']})

I want to create a pivot table if possible, so that I have two columns for the date, one column for the issue data and another for rescinded date, but in the pivot I need to maintain the unit, so I should end up with three columns:

Unit, Issue Date, Rescinded Date

Then next I want to calculate time difference between Issue Date and Rescinded date.

Output:

Unit Assigned      SWO Issued     SWO Rescinded    Time Difference
PLUMBING DIVISION  2018-01-04     2018-01-17        13 days
SCAFFOLD UNIT      2016-06-23     2016-06-23        0 days
BEST SQUAD         2018-12-21     2019-01-04        14 days
BEST SQUAD         2020-02-10     2020-02-25        15 days

Appreciate any help. Thanks.

Upvotes: 0

Views: 365

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150735

I believe this is pivot/pivot_table:

# convert to datetime if not already is
df['D_DATE'] = pd.to_datetime(df['D_DATE'])

(df.assign(idx=df.groupby(['BIN Number', 'Disposition','Unit Assigned']).cumcount())
   .pivot_table(index=['idx','BIN Number', 'Unit Assigned'], 
                columns='Disposition', 
                values='D_DATE',
                aggfunc='first')
   .reset_index()
   .assign(Time_Different=lambda x: x['SWO Rescinded'] - x['SWO Issued'])
   .drop('idx',axis=1)

)

Output:

Disposition     BIN Number  Unit Assigned       SWO Issued  SWO Rescinded   Time_Different
0               10005       PLUMBING DIVISION   2018-01-04  2018-01-17      13 days 
1               10005       SCAFFOLD UNIT       2016-06-23  2016-06-23      0 days
2               10006       BEST SQUAD          2018-12-21  2019-01-04      14 days
3               10006       BEST SQUAD          2020-02-10  2020-02-25      15 days

Upvotes: 1

Related Questions