Pandas 文档 give an example of concat通过沿列 (axis=1) 连接来合并索引 (axis=0):
In [1]: df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],
...: 'B': ['B0', 'B1', 'B2', 'B3'],
...: 'C': ['C0', 'C1', 'C2', 'C3'],
...: 'D': ['D0', 'D1', 'D2', 'D3']},
...: index=[0, 1, 2, 3])
...:
In [2]: df4 = pd.DataFrame({'B': ['B2', 'B3', 'B6', 'B7'],
...: 'D': ['D2', 'D3', 'D6', 'D7'],
...: 'F': ['F2', 'F3', 'F6', 'F7']},
...: index=[2, 3, 6, 7])
...:
In [3]: result = pd.concat([df1, df4], axis=1)
请注意,df1 和 df4 共享索引 2 和 3,以及 B 列code> 和 D。
concat 没有复制共享索引,但复制了列。
我怎样才能避免重复列?
也就是说,我希望结果具有:
- 指数
0,1,2,3,6,7,和 - 列
A、B、C、D、F。 (没有重复的列!)
如果有任何数据冲突,我希望引发异常。
请您参考如下方法:
我实际上是在请求“upsert”(插入、更新)操作。所以这是一种可行的方法:
首先,df1 中当前不存在的行的“插入”:
# Add all rows from df4 that don't currently exist in df1
result = pd.concat([df1, df4[~df4.index.isin(df1.index)]])
然后,检查两个 DataFrame 共有的行中是否存在冲突,因此必须更新:
# Obtain a sliced version of df1, showing only
# the columns and rows shared with the df4
df1_sliced = \
result.loc[result.index.isin(df4.index),
result.columns.isin(df4.columns)]
df4_sliced = \
df4.loc[df4.index.isin(df1.index),
df4.columns.isin(df1.columns)]
# Obtain a mask of the conflicts in the current segment
# as compared with all previously loaded data. That is:
# NaN NaN = False
# NaN 2 = False
# 2 2 = False
# 2 3 = True
# 2 NaN = True
data_conflicts = (pd.notnull(df1_sliced) &
(df1_sliced != df4_sliced))
if data_conflicts.any().any():
raise AssertionError("Data from this segment conflicted "
"with previously loaded data:\n",
data_conflicts)
最后,执行更新:
# Replace any rows that do exist with the cur_df version
result.update(df4)
结果和Happy001的回答一样。不确定哪个更有效率。来自 SQL 背景,我的回答对我来说更容易理解。
print(result)
A B C D F
0 A0 B0 C0 D0 NaN
1 A1 B1 C1 D1 NaN
2 A2 B2 C2 D2 F2
3 A3 B3 C3 D3 F3
6 NaN B6 NaN D6 F6
7 NaN B7 NaN D7 F7
