同时合并和连接

Question

我有多个数据框，每个数据框代表每个月的进展。我的任务是一步一步地将它们合并，满足两个条件。下面是三个示例数据框，我会尝试解释我想要实现的目标。

import pandas as pd

data_22_3 = {
    'id_number': ['A123', 'B456', 'C789'],
    'company': ['Insurance1', 'Insurance2', 'Insurance3'],
    'type': ['A', 'A', 'C'],
    'Income': [100, 200, 300]
}
df_22_3 = pd.DataFrame(data_22_3)

data_22_4 = {
    'id_number': ['A123', 'B456', 'D012'],
    'company': ['Insurance1', 'Insurance2', 'Insurance1'],
    'type': ['A', 'B', 'B'],
    'Income': [150, 250, 400]
}
df_22_4 = pd.DataFrame(data_22_4)

data_22_5 = {
    'id_number': ['A123', 'C789', 'E034'],
    'company': ['Insurance1', 'Insurance3', 'Insurance5'],
    'type': ['A', 'C', 'B'],
    'Income': [180, 320, 500]
}
df_22_5 = pd.DataFrame(data_22_5)

首先，我们把第一个数据框当作主数据框。将第二个数据框和第一个数据框合并的方式如下：

如果第二个数据框的 id_number 在第一个数据框中存在，那么就应该在第一个数据框中添加一个新列 Income_2，作为下个月的收入。由于下一个数据框有相同的列，除了 Income 列以外的所有列都应该被忽略。

如果第二个数据框的 id_number 在第一个数据框中不存在，那么就应该将整行添加到第一个数据框中。需要注意的是，要把 Income 的值放到 Income_2 列中，并把 Income 列的值设为0，因为这个值属于下个月。

然后，得到的数据框应该以类似的方式与下一个数据框合并，依此类推。

即使其他列的值，比如 Type 或 Company 有所不同，只要 id_number 相同，就应该保留之前数据框中的值。

我可能解释得不够清楚，但结果大概是这样的：

data_all = {
    'id_number': ['A123', 'B456', 'C789', 'D012', 'E034'],
    'company': ['Insurance1', 'Insurance2', 'Insurance3', 'Insurance1', 'Insurance5'],
    'type': ['A', 'A', 'C', 'B', 'B'],
    'Income': [100, 200, 300, 0, 0],
    'Income_2': [150, 250, 0, 400, 0],
    'Income_3': [180, 0, 320, 0, 500]
}

all_df = pd.DataFrame(data_all)

Answer 1

你可以先把所有的数据合并在一起（长格式），再加一列来标记数据来源的编号，然后根据这个编号来调整Income的排列。对于其他的列，可以单独获取第一个值，然后把它们合并在一起。

dfs = [df_22_3, df_22_4, df_22_5]
df = pd.concat([d.assign(n=n) for n, d in enumerate(dfs, start=1)])

# Columns
primary = 'id_number'
pivot = 'Income'
number = 'n'

income_pivot = (
    # Same as `df.pivot`, but with a `fill_value`
    df.set_index([primary, number])[pivot].unstack(number, fill_value=0)
    # Change labels
    .add_prefix(f'{pivot}_').rename_axis(columns=None)
)

other_cols_first = df.drop(columns=[pivot, number]).groupby(primary).first()

result = other_cols_first.join(income_pivot)

              company type  Income_1  Income_2  Income_3
id_number
A123       Insurance1    A       100       150       180
B456       Insurance2    A       200       250         0
C789       Insurance3    C       300         0       320
D012       Insurance1    B         0       400         0
E034       Insurance5    B         0         0       500

到这个时候，如果你想得到你展示的那个具体结果，只需要重命名Income_1这一列，并且重置一下索引就可以了。

result.rename(columns={'Income_1': 'Income'}).reset_index()

  id_number     company type  Income  Income_2  Income_3
0      A123  Insurance1    A     100       150       180
1      B456  Insurance2    A     200       250         0
2      C789  Insurance3    C     300         0       320
3      D012  Insurance1    B       0       400         0
4      E034  Insurance5    B       0         0       500

Answer 2

你可以试试：

out = pd.concat(
    [
        df_22_3.set_index("id_number"),
        df_22_4.set_index("id_number")[["company", "type", "Income"]].add_suffix("_2"),
        df_22_5.set_index("id_number")[["company", "type", "Income"]].add_suffix("_3"),
    ],
    axis=1,
).reset_index()

out["company"] = out[["company", "company_2", "company_3"]].apply(
    lambda x: x[x.first_valid_index()], axis=1
)

out["type"] = out[["type", "type_2", "type_3"]].apply(
    lambda x: x[x.first_valid_index()], axis=1
)

out = out.drop(columns=["type_2", "type_3", "company_2", "company_3"]).fillna(0)

print(out)

输出结果是：

  id_number     company type  Income  Income_2  Income_3
0      A123  Insurance1    A   100.0     150.0     180.0
1      B456  Insurance2    B   200.0     250.0       0.0
2      C789  Insurance3    C   300.0       0.0     320.0
3      D012  Insurance1    B     0.0     400.0       0.0
4      E034  Insurance5    B     0.0       0.0     500.0

Answer 3

不要同时进行操作，先把不包含Income的部分合并在一起，并去掉重复的项，然后再把每个Income合并进来。

dfs = [df_22_3, df_22_4, df_22_5]
result = (
    pd.concat(dfs)
    .drop(columns='Income')
    .drop_duplicates('id_number')
    .reset_index(drop=True)
)

在合并的时候，手动重新索引是我发现的最简单的方法，可以用来填补缺失的值：

for i, df in enumerate(dfs, start=1):
    result[f'Income_{i}'] = (
        df
        .set_index('id_number')
        ['Income']
        .reindex(result['id_number'], fill_value=0)
        .reset_index(drop=True)
    )

到这个时候，我的结果中的Income列被标记为Income_1，这样做是为了保持一致性，但你可以给它重新命名：

result.rename(columns={'Income_1': 'Income'})

  id_number     company type  Income  Income_2  Income_3
0      A123  Insurance1    A     100       150       180
1      B456  Insurance2    A     200       250         0
2      C789  Insurance3    C     300         0       320
3      D012  Insurance1    B       0       400         0
4      E034  Insurance5    B       0         0       500