python - 计算一个旋转的数据框列并加入python中的另一个数据框

Question

我有这两个与调用中心相关的 Pandas 数据框:

> my_call
  id call_id            result code_result
1  1     200 answering machine          am
2  2     200         call back          cb
3  3     200         call_back          cb
4  4     300    still workable          sw
5  5     300     transfer call          tc
6  6     300       do not call          dc

> my_lead
  lead_id lead_source
1     200        bpos
2     300        zeta

我想加入这两个数据集，来自 call_id和 lead id ，但我想要 code_result以广泛的方式旋转，以便计算每个 id 的结果，并有一个额外的列来计算 lead_id s。这是预期的结果:

  lead_id lead_source am cb sw tc dc count
1     200        bpos 1  2  0  0  0  3
2     300        zeta 0  0  1  1  1  3

我认为来自 my_lead 的加入可能没问题，但我被困在如何做，如果我必须输入所有结果 (am, cb, sw, tc, dc)或者如果 python 可以自动完成。任何帮助将不胜感激。

Answer 1

通过 crosstab 的选项+ join :

import pandas as pd

my_call = pd.DataFrame({
    'id': {1: 1, 2: 2, 3: 3, 4: 4, 5: 5, 6: 6},
    'call_id': {1: 200, 2: 200, 3: 200, 4: 300, 5: 300, 6: 300},
    'result': {1: 'answering machine ', 2: 'call back', 3: 'call_back',
               4: 'still workable', 5: 'transfer call', 6: 'do not call'},
    'code_result': {1: 'am', 2: 'cb', 3: 'cb', 4: 'sw', 5: 'tc', 6: 'dc'}
})
my_lead = pd.DataFrame({
    'lead_id': {1: 200, 2: 300}, 'lead_source': {1: 'bpos', 2: 'zeta'}
})

new_df = my_lead.join(
    pd.crosstab(my_call['call_id'], my_call['code_result'],
                margins=True,
                margins_name='count'),
    on='lead_id'
)

print(new_df)

new_df :

  lead_id lead_source  am  cb  dc  sw  tc  count
1     200        bpos   1   2   0   0   0      3
2     300        zeta   0   0   1   1   1      3

通过 set_index 的选项+ str.get_dummies + join :

import pandas as pd

my_call = pd.DataFrame({
    'id': {1: 1, 2: 2, 3: 3, 4: 4, 5: 5, 6: 6},
    'call_id': {1: 200, 2: 200, 3: 200, 4: 300, 5: 300, 6: 300},
    'result': {1: 'answering machine ', 2: 'call back', 3: 'call_back',
               4: 'still workable', 5: 'transfer call', 6: 'do not call'},
    'code_result': {1: 'am', 2: 'cb', 3: 'cb', 4: 'sw', 5: 'tc', 6: 'dc'}
})
my_lead = pd.DataFrame({
    'lead_id': {1: 200, 2: 300}, 'lead_source': {1: 'bpos', 2: 'zeta'}
})

new_df = my_call.set_index('call_id')['code_result'].str.get_dummies().sum(level=0)
new_df['count'] = new_df.sum(axis=1)
new_df = my_lead.join(new_df, on='lead_id')
print(new_df)

new_df :

   lead_id lead_source  am  cb  dc  sw  tc  count
1      200        bpos   1   2   0   0   0      3
2      300        zeta   0   0   1   1   1      3