Swasti
Swasti

Reputation: 287

New dataframe column for count of rows with certain value in a column , with same customer ID column and less than date and time

EDITED

I want to add a new column called prev_message_left which counts the no. of messages_left per ID less than the date prior the given time. Basically I want to have a column which says how many times we had left message on call to that customer prior to the current time and date. This is how my data frame looks like

date       ID   call_time   message_left  
20191101    1        8:00              0
20191102    2        9:00              1
20191030    1       16:00              1
20191103    2       10:30              1
20191105    2       14:00              0
20191030    1       15:30              0

I want to add an additional column called prev_message_left_count

date       ID   call_time   message_left  prev_message_left_count
20191101    1        8:00              0                        1
20191102    2        9:00              1                        0
20191030    1       16:00              1                        0
20191103    2       10:30              1                        1
20191105    2       14:00              0                        2
20191030    1       15:30              0                        0

My dataframe has 15 columns and 90k rows.
I have various other columns in this dataframe and there are columns like 'No Message Left', 'Responded' for which I will have to compute additional columns called 'Previous_no_message_left' and 'prev_responded' similar to 'prev_message_left'

Upvotes: 0

Views: 59

Answers (1)

BENY
BENY

Reputation: 323236

IIUC cumcount after sort_values

df['pervious']=df.sort_values(['date','call_time']).groupby('ID').cumcount()
df
       date  ID call_time  message_left  pervious
0  20191101   1      8:00             0         2
1  20191102   2      9:00             1         0
2  20191030   1     16:00             1         1
3  20191103   2     10:30             1         1
4  20191105   2     14:00             0         2
5  20191030   1     15:30             0         0

Upvotes: 1

Related Questions