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
                    Subb.loc[row,'retour'] = False       
                ### IF not then no Return
                Subb.loc[row,'retour'] = False


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.

  • \$\begingroup\$ Your indentation seems very off. \$\endgroup\$ – vnp Jun 14 '17 at 3:57
  • \$\begingroup\$ It is not maybe some error occurred when I copy then code \$\endgroup\$ – Nico Coallier Jun 14 '17 at 14:37
  • \$\begingroup\$ Ill edit later today \$\endgroup\$ – Nico Coallier Jun 14 '17 at 14:39
  • 1
    \$\begingroup\$ The fastest way to insert code into a post is to copy it from your editor, paste it verbatim in the post, select it and hit Ctrl+K (or click on the "code" button in the question editor). As indentation is very important in Python, please correct it or we will close the question as containing broken code. \$\endgroup\$ – 301_Moved_Permanently Jun 14 '17 at 15:01
  • \$\begingroup\$ I will fix tonight sorry again \$\endgroup\$ – Nico Coallier Jun 14 '17 at 22:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.