Difference between concurrent call scores in pandas dataframe

user_4518107 0
Viewed 101

I am trying to analyze a few things in the following data set using a modified version of code that user @Garret provided, though, I am running into a few issues.

The dataset has a column that shows whether the customer was engaged by a live agent, or an automated machine. I am trying to get the difference between concurrent calls in which the member was first connected to an agent, and then was not. The call must have the same call reason, and it must be placed after the initial call in regards to the timestamp. Also, it is okay for there to be calls for other reasons in between.

Here is the dataset:

data = [['bob13', 1, 'returns','automated',' 2019-08-18 10:12:00'],['bob13', 0, 'returns','automated',' 2019-03-18 10:12:00'],\
        ['bob13', 8, 'returns','agent',' 2019-04-18 10:15:00'],['rach2', 2, 'shipping','automated',' 2019-04-19 10:15:00'],\
        ['bob13', 0, 'returns','agent',' 2019-05-18 11:12:00'],['rach2', 0, 'shipping','agent',' 2019-04-18 11:15:00'],\
        ['bob13', 3, 'returns','agent',' 2019-02-18 10:12:00'],['rach2', 8, 'shipping','agent',' 2019-05-19 10:15:00'],\
       ['rach2', 7, 'shipping','automated',' 2019-06-19 10:15:00'],['roy', 4, 'exchange','agent','2019-03-26 17:36:00'],\
       ['roy', 5, 'exchange','automated','2019-01-28 09:48:00']]

df = pd.DataFrame(data, columns = ['member_id', 'survey_score','call_reason','connection','time_stamp']) 
df.sort_values(by=['time_stamp']).head(20)

member_id   survey_score    call_reason connection  time_stamp
6   bob13        3            returns   agent       2019-02-18 10:12:00
1   bob13        0            returns   automated   2019-03-18 10:12:00
2   bob13        8            returns   agent       2019-04-18 10:15:00
5   rach2        0            shipping  agent       2019-04-18 11:15:00
3   rach2        2            shipping  automated   2019-04-19 10:15:00
4   bob13        0            returns   agent       2019-05-18 11:12:00
7   rach2        8            shipping  agent       2019-05-19 10:15:00
8   rach2        7            shipping  automated   2019-06-19 10:15:00
0   bob13        1            returns   automated   2019-08-18 10:12:00
10  roy          5            exchange  automated   2019-01-28 09:48:00
9   roy          4            exchange  agent       2019-03-26 17:36:00

The output that I am expecting is as follows:

member_id    call_reason    automated    agent    score differential
bob13         returns           0          3            -3
bob13         returns           1          0             1
rach2         shipping          2          0             2
rach2         shipping          7          8            -1

So basically, just looking for the difference between two calls in regards to call_reason and connection. The first call being when the member is connected to an agent, the second call has to come after the first based off of the timestamp, has to be for the same reason, and has to be connected to the automated system. It is okay if there are calls placed for other reasons in between. The code I have tried is as follows:

grp = df.query('connection=="automated"').\
    groupby(['member_id', 'call_reason'])
df['OutId'] = grp.time_stamp.transform(lambda x: x.rank())
df.head(10)
grp = df.groupby(['member_id', 'call_reason'])
df['Id'] = grp.OutId.transform(lambda x: x.bfill())
df.head(10)
agent = df.query('connection=="agent"').\
    groupby(['member_id', 'call_reason', 'Id']).survey_score.last()

automated = df.query('connection=="automated"').\
    groupby(['member_id', 'call_reason', 'Id']).survey_score.last()

ddf = pd.concat([automated, agent], axis=1,
                keys=['automated', 'agent'])
ddf['score_differential'] = ddf.automated - ddf.agent

The output that I get is:

ddf.dropna().head(10)

                              automated     agent   score_differential
member_id   call_reason Id          
rach2         shipping  2.0      7           8.0          -1.0
roy           exchange  1.0      5           4.0           1.0

again, the expected output would be:

member_id    call_reason    automated    agent    score differential
bob13         returns           0          3            -3
bob13         returns           1          0             1
rach2         shipping          2          0             2
rach2         shipping          7          8            -1

Note: I would love if the solution could be flexible so that I can analyze a few different scenarios such as:

  1. the difference between calls that are only automated

  2. the difference between calls that are only connected to agents

  3. difference between calls when initial call is connected to an agent, and in the second call it doesnt matter which connection type


Additional help with this would be much appreciated!

pandas dataframe python
1 Answers

you can do this by creating a function, and then applying that function over the groups in a groupby.

Set up initial dataframe:

import pandas as pd

data = [['bob13', 1, 'returns','automated',' 2019-08-18 10:12:00'],['bob13', 0, 'returns','automated',' 2019-03-18 10:12:00'],\
        ['bob13', 8, 'returns','agent',' 2019-04-18 10:15:00'],['rach2', 2, 'shipping','automated',' 2019-04-19 10:15:00'],\
        ['bob13', 0, 'returns','agent',' 2019-05-18 11:12:00'],['rach2', 0, 'shipping','agent',' 2019-04-18 11:15:00'],\
        ['bob13', 3, 'returns','agent',' 2019-02-18 10:12:00'],['rach2', 8, 'shipping','agent',' 2019-05-19 10:15:00'],\
       ['rach2', 7, 'shipping','automated',' 2019-06-19 10:15:00'],['roy', 4, 'exchange','agent','2019-03-26 17:36:00'],\
       ['roy', 5, 'exchange','automated','2019-01-28 09:48:00']]

df = pd.DataFrame(data, columns = ['member_id', 'survey_score','call_reason','connection','time_stamp']) 
df.sort_values(by=['time_stamp']).head(20)
df['time_stamp'] = pd.to_datetime(df['time_stamp'])

df
   member_id  survey_score call_reason connection          time_stamp
0      bob13             1     returns  automated 2019-08-18 10:12:00
1      bob13             0     returns  automated 2019-03-18 10:12:00
2      bob13             8     returns      agent 2019-04-18 10:15:00
3      rach2             2    shipping  automated 2019-04-19 10:15:00
4      bob13             0     returns      agent 2019-05-18 11:12:00
5      rach2             0    shipping      agent 2019-04-18 11:15:00
6      bob13             3     returns      agent 2019-02-18 10:12:00
7      rach2             8    shipping      agent 2019-05-19 10:15:00
8      rach2             7    shipping  automated 2019-06-19 10:15:00
9        roy             4    exchange      agent 2019-03-26 17:36:00
10       roy             5    exchange  automated 2019-01-28 09:48:00

Whenever I try to solve a problem like this, I break out one specific group. So I just isolated bob13, and tried to replicate arriving at what we wanted for bob. that led me to a specific series of steps that I then put into a function:

We sort the dataframe by time, and then create new columns called next_connection and 'next_score'. These shift the results from the next result, so that we have it within that row. We drop any missing (the last one of the group since there is no next), we isolate any rows where the connection is agent and the next_connection is automated. we rename the columns to match what your output is and we calculate the score differential. 

def function_(df):
    df = df.sort_values('time_stamp')
    df['next_connection'] = df.connection.shift(-1)
    df['next_score'] = df.survey_score.shift(-1)
    df = df.dropna()
    df = df[(df.connection == 'agent') & (df.next_connection == 'automated')]
    df = df.rename(columns={'survey_score':'agent', 'next_score':'automated'})
    df['score differential'] = df['automated'] - df['agent']
    return df

now we apply that to the dataframe grouped by member_id and call_reason.

g = df.groupby(['member_id', 'call_reason']).apply(function_)

g[['member_id','call_reason','automated','agent','score differential']].reset_index(drop=True)

  member_id call_reason  automated  agent  score differential
0     bob13     returns        0.0      3                -3.0
1     bob13     returns        1.0      0                 1.0
2     rach2    shipping        2.0      0                 2.0
3     rach2    shipping        7.0      8                -1.0
user_72379970
Related