CODEWITHSUNDEEP
pythonpandasdataframe
tgtt
tgtt

Reputation: 35

How to get value of last valid index of a different column for each row

I have the following table in pandas.

>>> df
     view_time  click_time  username  ad_id
250  07:00      07:05       a         abc
251  07:10                  a         def
252  07:20      07:35       a         ghi
253  07:30      07:41       a         jkl
254  07:40                  a         mno
255  07:50                  a         pqr

I would like to add a column that shows the last clicked ad for each point in time (view_time). Note that click_time may be of a bigger value than the next view_time. 

     view_time  click_time  username  ad_id    last_clicked_ad
250  07:00      07:05       a         abc 
251  07:10                  a         def      abc
252  07:20      07:35       a         ghi      abc
253  07:30      07:41       a         jkl      abc
254  07:40                  a         mno      ghi
255  07:50                  a         pqr      jkl

I have tried something like this:

>>> i = df[['click_time']].apply(pd.Series.last_valid_index)
>>> df.loc[i, 'ad_id']
253    jkl
Name: ad_id, dtype: object

which gives me the value of ad_id for the last row where click_time is a non-null value.

However, I want to use this logic for each point in time (view_time) instead of for the whole table.

To reproduce simplified example:

>>> df = pd.DataFrame({'view_time': ['07:00','07:10','07:20','07:30','07:40','07:50'],
              'click_time': ['07:05', '', '07:35', '07:41', '', ''],
              'username': ['a','a','a','a','a','a'],
              'ad_id': ['abc', 'def','ghi','jkl','mno','pqr']
             })
>>> df.index += 250
>>> df['view_time'] = pd.to_datetime(df['view_time'])
>>> df['click_time'] = pd.to_datetime(df['click_time'])

Upvotes: 2

Views: 258

Answers (1)

jezrael
jezrael

Reputation: 863531

Use merge_asof:

df2 = (df[['click_time','username', 'ad_id']]
              .dropna(subset=['click_time'])
              .rename(columns={'ad_id':'last_clicked_ad', 'click_time':'new_time'}))

df = (pd.merge_asof(df, df2, left_on='view_time', right_on='new_time', by='username')
        .drop('new_time', 1))
print (df)
            view_time          click_time username ad_id last_clicked_ad
0 2019-11-08 07:00:00 2019-11-08 07:05:00        a   abc             NaN
1 2019-11-08 07:10:00                 NaT        a   def             abc
2 2019-11-08 07:20:00 2019-11-08 07:35:00        a   ghi             abc
3 2019-11-08 07:30:00 2019-11-08 07:41:00        a   jkl             abc
4 2019-11-08 07:40:00                 NaT        a   mno             ghi
5 2019-11-08 07:50:00                 NaT        a   pqr             jkl

Upvotes: 1

Related Questions