python - Finding a timedelta in pandas dataframe based upon specific values in one column

Question

I have a dataframe where i wish to compute the number of days (timedelta) that a unique asset remained installed. The sample input dataframe is as follows

df =pd.DataFrame({'Date': ['2007-11-01','2012-03-02','2012-03-02','2013-04-01','2013-04-01','2017-11-15','2017-11-15'], 'action':['installed','installed','removed','installed','removed','installed','removed'], 'asset_alphnum':['A-3724','A-3534','A-3724','A2732','A-3534','A-2007','A2732']})

Outputs:

I have tried pd.crosstab

pd.crosstab(df.asset_alphnum, [df.Date, df.action])

outputs

However I cannot work out how to take it from here. Somehow need to collapse the hierarchical index and subract one date from the next.

Any guidance much appreciated.

标签： pythonpandasgroup-bypandas-groupbycrosstab

Answer 1

使用应用创建安装和删除的两列。然后使用数据框交叉表来计算频率

date=['2007-11-01', '2012-03-02', '2012-03-02',
'2013-04-01', '2013-04-01', '2017-11-15', '2017-11-15']
action=['installed', 'installed', 'removed', 'installed','removed','installed','removed']
asset_alphnum=['A-3724','A3534','A-3724','A2732','A-3534','A-2007','A2732']

df=pd.DataFrame({'date':date, 'action':action,'asset_alphnum':asset_alphnum})
df.set_index('date')
df['installed']=df['action'].apply(lambda x: 1 if x=='installed' else 0)
df['removed']=df['action'].apply(lambda x: 1 if x=='removed' else 0)
df.drop('action',axis=1)
print(df)
print(pd.crosstab(df.asset_alphnum, [df.date]))
output:
date           2007-11-01  2012-03-02  2013-04-01  2017-11-15
asset_alphnum                                                
A-2007                  0           0           0               1
A-3534                  0           0           1           0
A-3724                  1           1           0           0
A2732                   0           0           1           1
A3534                   0           1           0           0