时间戳

首页 > 解决方案 > pandas 根据条件组合行

python - pandas 根据条件组合行

问题描述

嗨，伙计们，我正在研究包含以下示例的数据集：

在此处输入图像描述

数据包含 start_time、end_time、id 和 url。对于一个 id 和 url 组，我有不同的输入和输出值问题是输入和输出值在不同的行中，我想填充缺少的 end_time/start_time 值。为此，我必须使用以下逻辑：

  1. 如果我在 start_time 中有值并且结束时间为空，那么我必须考虑 end_time >= start_time 用最接近的 end_time 填充 end_time 并删除使用/匹配的行
  2. 在填充了所有具有star_time 的行并删除了已使用/匹配的行之后，仍然保留了一些空start_time 的行，然后我必须用与end_time 相同的值填充start_time。
  3. 如果没有为给定的 start_time 找到匹配的 end_time 值，那么我必须用相同的 start_time 值填充 end_time 值。

考虑到上述情况，预期结果应该类似于以下内容，我分两个阶段给出输出，以便于理解

  1. 用 start_time 填充匹配的 end_times 并删除已使用/匹配的行： 在此处输入图像描述

  2. 最终输出填充剩余的 start_time/end_time 值： 在此处输入图像描述

目前我正在使用以下方式来实现这一点，但我觉得它没有优化：

 def process(self, param, context):
    df = context['data']
    # df = df.drop_duplicates()
    key_cols = param['keys_cols']
    start_time_col = param['start_time_col']
    end_time_col = param['end_time_col']
    guid_col = param.get('guid_col','guid')
    df_groupby = df.groupby(key_cols).size().reset_index()
    final_dfs = []
    condition = ''
    for key in key_cols:
        if condition == '':
            condition = '(df[\''+str(key)+"\']==row[\'"+str(key)+"\'])"
        else:
            condition = condition + ' & ' +'(df[\'' + str(key) + "\']==row[\'" + str(key) + "\'])"
    for index, row in df_groupby.iterrows():
        sub_df = df[eval(condition)]
        if sub_df[start_time_col].isnull().sum() != len(sub_df[start_time_col]) and (sub_df[end_time_col].isnull().sum() != len(sub_df[end_time_col])):
            sub_df = sub_df.sort_values([start_time_col, end_time_col], ascending=True)
            subdf_start_time_not_null = sub_df[sub_df[start_time_col].notnull()]
            subdf_end_time_not_null = sub_df[sub_df[end_time_col].notnull()]
            subdf_end_time_not_null['combined'] = subdf_end_time_not_null[end_time_col] +"__"+ subdf_end_time_not_null[guid_col]
            end_time_values = subdf_end_time_not_null['combined'].values.tolist()
            for row_number, (stime_index, stime_row) in enumerate(subdf_start_time_not_null.iterrows()):
                delete_index = row_number
                if row_number < len(end_time_values):
                    end_time_value = np.nan
                    if int(str(subdf_start_time_not_null.at[stime_index,start_time_col]).replace(":","").replace(" ","").replace("-","")) <= int(str(end_time_values[row_number]).split("__")[0].replace(":","").replace(" ","").replace("-","")):
                        end_time_value = end_time_values[row_number]
                        subdf_start_time_not_null.at[stime_index,end_time_col] = str(end_time_values[row_number]).split("__")[0]
                    else:
                        prev_index = end_time_values.index(end_time_values[row_number])
                        for end_time in end_time_values:
                            current_index = end_time_values.index(end_time)
                            if current_index > prev_index:
                                if int(str(subdf_start_time_not_null.at[stime_index,start_time_col]).replace(":","").replace(" ","").replace("-","")) <= int(str(end_time_values[current_index]).split("__")[0].replace(":","").replace(" ","").replace("-","")):
                                    subdf_start_time_not_null.at[stime_index, end_time_col] = end_time_values[current_index]
                                    delete_index = current_index
                                    end_time_value = end_time_values.pop(delete_index)
                                    break
                    subdf_end_time_not_null = subdf_end_time_not_null[subdf_end_time_not_null[guid_col]!=end_time_value.split("__")[1]]
                else:
                    subdf_start_time_not_null.at[stime_index,end_time_col] = subdf_start_time_not_null.at[stime_index,start_time_col]
            subdf_end_time_not_null.drop('combined', axis=1, inplace=True)
            sub_df = pd.concat([subdf_start_time_not_null,subdf_end_time_not_null])
        sub_df[start_time_col] = np.where(sub_df[start_time_col].isnull(),sub_df[end_time_col],sub_df[start_time_col])
        sub_df[end_time_col] = np.where(sub_df[end_time_col].isnull(),sub_df[start_time_col],sub_df[end_time_col])
        final_dfs.append(sub_df)
        # LOGGER.info('do something' +str(index))
    df = pd.concat(final_dfs)
    context['data'] = df
    context['continue'] = True
    return context

其中参数如下：

param = {"keys_cols":['id', 'url'], "start_time_col":"start_time","end_time_col":"end_time"}

“df”是数据。

请帮助审查并建议如何使其更优化，我在一个文件中有超过 70000 行数据和超过 12000 对 id 和 url

期待你们。

谢谢

标签： pythonpandasdataframepandas-groupbyrows

解决方案

如果我正确理解要求，我们可以在pandas. 这里基本上有两个步骤：

  1. 用于pandas.merge_asof填写最近的end_date
  2. 用于drop_duplicates删除out我们在步骤 1 中使用的记录
text = StringIO(
    """
             id                url type          start_time            end_time
o6FlbuA_5565423  https://vaa.66new  out                 NaT 2021-08-25T15:23:28
o6FlbuA_5565423  https://vaa.66new  out                 NaT 2021-08-25T15:27:34
o6FlbuA_5565423  https://vaa.66new  out                 NaT 2021-08-25T15:23:52
o6FlbuA_5565423  https://vaa.66new   in 2021-08-25T15:23:37                 NaT
o6FlbuA_5565423  https://vaa.66new   in 2021-08-25T15:43:56                 NaT  # note: no record with `end_time` after this records `start_time`
o6FlbuA_5565423  https://vaa.66new  out                 NaT 2021-08-25T15:10:29
o6FlbuA_5565423  https://vaa.66new  out                 NaT 2021-08-25T15:25:00
o6FlbuA_5565423  https://vaa.66new  out                 NaT 2021-08-25T15:15:49
o6FlbuA_5565423  https://vaa.66new   in 2021-08-25T15:33:37 2021-08-25T15:34:37  # additional already complete record
"""
)
df = pd.read_csv(text, delim_whitespace=True, parse_dates=["start_time", "end_time"], comment="#")

# separate out unmatched `in` records and unmatched `out` records
df_in_unmatched = (
    df[(df.type == "in") & ~df.start_time.isna() & df.end_time.isna()]
    .drop(columns=["end_time"])
    .sort_values("start_time")
)
df_out_unmatched = (
    df[(df.type == "out") & df.start_time.isna() & ~df.end_time.isna()]
    .drop(columns=["type", "start_time"])
    .sort_values("end_time")
)

# match `in` records to closest `out` record with `out.end_time` >= `in.start_time`
df_in_matched = pd.merge_asof(
    df_in_unmatched,
    df_out_unmatched,
    by=["id", "url"],
    left_on="start_time",
    right_on="end_time",
    direction="forward",
    allow_exact_matches=True,
)

# fill in missing `end_time` for records with only `start_time`
df_in_matched["end_time"] = df_in_matched["end_time"].combine_first(
    df_in_matched["start_time"]
)

# combine matched records with remaining unmatched and deduplicate
# in order to remove "used" records
df_matched = (
    pd.concat([df_in_matched, df_out_unmatched], ignore_index=True)
    .drop_duplicates(subset=["id", "url", "end_time"], keep="first")
    .dropna(subset=["end_time"])
    .fillna({"type": "out"})
)

# fill in missing `start_time` for records with only `end_time`
df_matched["start_time"] = df_matched["start_time"].combine_first(
    df_matched["end_time"]
)

# combine matched records with unprocessed records: i.e. records
# that had both `start_time` and `end_time` (if extant)
df_final = pd.concat(
    [df_matched, df.dropna(subset=["start_time", "end_time"])], ignore_index=True
)

结果：

               id               url type         start_time             end_time
0 o6FlbuA_5565423 https://vaa.66new   in 2021-08-25 15:23:37 2021-08-25 15:23:52
1 o6FlbuA_5565423 https://vaa.66new   in 2021-08-25 15:43:56 2021-08-25 15:43:56
2 o6FlbuA_5565423 https://vaa.66new  out 2021-08-25 15:10:29 2021-08-25 15:10:29
3 o6FlbuA_5565423 https://vaa.66new  out 2021-08-25 15:15:49 2021-08-25 15:15:49
4 o6FlbuA_5565423 https://vaa.66new  out 2021-08-25 15:23:28 2021-08-25 15:23:28
5 o6FlbuA_5565423 https://vaa.66new  out 2021-08-25 15:25:00 2021-08-25 15:25:00
6 o6FlbuA_5565423 https://vaa.66new  out 2021-08-25 15:27:34 2021-08-25 15:27:34
7 o6FlbuA_5565423 https://vaa.66new   in 2021-08-25 15:33:37 2021-08-25 15:34:37

推荐阅读