In [46]:
import numpy as np
In [47]:
import pandas as pd
In [48]:
df = pd.read_csv('df_summary.csv', index_col='Index')
In [49]:
df['Agent'] = df['Agent'] + '@bank.ca'
In [50]:
df_agent_desc = pd.read_csv('Agent Description.csv', encoding='latin1')
In [51]:
df_agent_desc = df_agent_desc[['Username', 'FullName', 'Position', 'EMP_ID']].copy()
In [52]:
df_agent_desc = df_agent_desc.dropna(how='any')
In [53]:
df_agent_desc['Username'] = df_agent_desc['Username'] + '@bank.ca'
In [54]:
df_output = pd.merge(df, df_agent_desc, left_on='Agent', right_on='Username')
In [55]:
df_output.index.name = 'Index'
In [56]:
df_output = df_output.drop(['Username'], axis=1)
In [57]:
#df_output =  df_output.rename(columns={'F Name': 'Full Name'}).copy()
In [58]:
#df_output['Expert Count'][df_output.Position == 'CRS - Mortgage'] = 0
In [59]:
df_output.loc[df_output['Position'] == 'Position', 'Expert Count'] = 0
In [60]:
df_output.EMP_ID = df_output.EMP_ID.apply(np.int64)
In [61]:
df_output =  df_output.rename(columns={'Agent': 'Email'}).copy()
In [66]:
df_output['Username'] = df_output.Email.apply(lambda x: x.split('@')[0])
In [68]:
df_output.to_csv('esc-expert-count-keyed.csv')