I successfully create a for loop to generate a new column in my data. This column access if there is retention of the customer on a Radio station.
The data looks like this:
ID Date Time Station 1 10-06-2017 8:45 SuperRadio 1 10-06-2017 8:56 SuperRadio 1 10-06-2017 9:10 AmazingRadio 1 10-06-2017 10:23 SuperRadio
AS you can see a new line is created when a new program start on the radio and the listener is still listening. What I want to do is to access the events of leaving and return. Like at the fourth line here. So I want to add the following:
ID Date Time Station retour quit 1 10-06-2017 8:45 SuperRadio 0 0 1 10-06-2017 8:56 SuperRadio 0 0 1 10-06-2017 9:10 AmazingRadio 0 1 1 10-06-2017 10:23 SuperRadio 1 1
I have succeed doing that with a for loop but it is slow and not very pretty.
result =  ### Subsetting on date for date in set(DF.loc[:,'Date']): Sub = DF.loc[ DF.Date == date,:] ### Subsetting on ID for mem in set(DF.loc[:,'ID']): Subb = Sub.loc[Sub.ID == mem,:] ### Creating a Station +1 column sta1 = Subb.loc[:,'Station'].shift(1) Subb.loc[:,'Sta+1'] = sta1 ### Taking out Nan and Add Not Listening Subb.loc[:,'Sta+1'] = Subb['Sta+1'].fillna('Not listening') ### Creating the quit column q = Subb['Sta+1'] != Subb['Station'] Subb.loc[:,'quit'] = q ### Looping over the Subset to get the return column for row in Subb.index[1:,]: row_last = row -1 if Subb.loc[row, 'quit'] == True: Stat= Subb.loc[row,'Station'] if Stat in list(Subb.loc[:row_last,'Station']): ### If the row quit is true and the station is present in the previous row ### Return is then True Subb.loc[row,'retour'] = True else: Subb.loc[row,'retour'] = False else: ### IF not then no Return Subb.loc[row,'retour'] = False result.append(Subb)
I transform into Dataframe for analysis
dff = pd.concat(result) dff['quit'] = dff['quit'].astype(int) dff['retour'] = dff['retour'].replace(np.nan,'Not Listening') dff['retour'] = dff['retour'].replace([True,False],[1,0])
Note that my data is large and I have multiple ID and Date.