如何基于多列条件为Pandas DataFrame的多个列赋值？

需求说明 #

需要基于email_ID和screen列的条件，为包含重复行的Pandas DataFrame生成series1和series2两列。

输入数据集示例 #

import pandas as pd

df_in = pd.DataFrame({'email_ID': {0: 'mail_1',
  1: 'mail_1',
  2: 'mail_1',
  3: 'mail_1',
  4: 'mail_1',
  5: 'mail_1',
  6: 'mail_2',
  7: 'mail_2',
  8: 'mail_2',
  9: 'mail_2',
  10: 'mail_2',
  11: 'mail_2'},
 'time_stamp': {0: '2021-09-10 09:01:56.340259',
  1: '2021-09-10 09:01:56.672814',
  2: '2021-09-10 09:01:57.471423',
  3: '2021-09-10 09:01:57.480891',
  4: '2021-09-10 09:01:57.484644',
  5: '2021-09-10 09:01:57.984644',
  6: '2021-09-10 09:01:56.340259',
  7: '2021-09-10 09:01:56.672814',
  8: '2021-09-10 09:01:57.471423',
  9: '2021-09-10 09:01:57.480891',
  10: '2021-09-10 09:01:57.484644',
  11: '2021-09-10 09:01:57.984644'},
 'screen': {0: 'a',
  1: 'b',
  2: 'c',
  3: 'd',
  4: 'c',
  5: 'b',
  6: 'a',
  7: 'b',
  8: 'c',
  9: 'b',
  10: 'c',
  11: 'd'}})

df_in['time_stamp'] = df_in['time_stamp'].astype('datetime64[ns]')

期望输出 #

import pandas as pd

df_out = pd.DataFrame({'email_ID': {0: 'mail_1',
  1: 'mail_1',
  2: 'mail_1',
  3: 'mail_1',
  4: 'mail_1',
  5: 'mail_1',
  6: 'mail_2',
  7: 'mail_2',
  8: 'mail_2',
  9: 'mail_2',
  10: 'mail_2',
  11: 'mail_2'},
 'time_stamp': {0: '2021-09-10 09:01:56.340259',
  1: '2021-09-10 09:01:56.672814',
  2: '2021-09-10 09:01:57.471423',
  3: '2021-09-10 09:01:57.480891',
  4: '2021-09-10 09:01:57.484644',
  5: '2021-09-10 09:01:57.984644',
  6: '2021-09-10 09:01:56.340259',
  7: '2021-09-10 09:01:56.672814',
  8: '2021-09-10 09:01:57.471423',
  9: '2021-09-10 09:01:57.480891',
  10: '2021-09-10 09:01:57.484644',
  11: '2021-09-10 09:01:57.984644'},
 'screen': {0: 'a',
  1: 'b',
  2: 'c',
  3: 'd',
  4: 'c',
  5: 'b',
  6: 'a',
  7: 'b',
  8: 'c',
  9: 'b',
  10: 'c',
  11: 'd'},
 'series1': {0: 0,
  1: 1,
  2: 2,
  3: 3,
  4: 0,
  5: 1,
  6: 0,
  7: 1,
  8: 2,
  9: 3,
  10: 4,
  11: 5},
 'series2': {0: 0,
  1: 0,
  2: 0,
  3: 0,
  4: 1,
  5: 1,
  6: 2,
  7: 2,
  8: 2,
  9: 2,
  10: 2,
  11: 2}})

df_out['time_stamp'] = df_out['time_stamp'].astype('datetime64[ns]')

列生成规则 #

• series1列：从0开始逐行递增，满足以下任一条件时重置为0：
  • email_ID列的值发生变化；
  • screen列的值等于'd'。
• series2列：从0开始，每当series1重置时递增1。

现有实现方案 #

# 生成series1列
series1 = [0]
x = 0
for index in df_in[1:].index:
    if ((df_in._get_value(index - 1, 'email_ID')) == df_in._get_value(index, 'email_ID')) and (df_in._get_value(index - 1, 'screen') != 'd'):
        x += 1
        series1.append(x)
    else:
        x = 0
        series1.append(x)
df_in['series1'] = series1

# 生成series2列
series2 = [0]
x = 0
for index in df_in[1:].index:
    if df_in._get_value(index, 'series1') - df_in._get_value(index - 1, 'series1') == 1:
        series2.append(x)
    else:
        x += 1
        series2.append(x)
df_in['series2'] = series2

目前该实现方案逻辑可行，将在数小时内完成测试并确认最优方案。

内容的提问来源于stack exchange，提问作者Mystic