gpt4 book ai didi

python - Pandas dataframe : merge three dataframes by two columns, 忽略大多数列?

转载 作者:太空宇宙 更新时间:2023-11-03 15:21:47 26 4
gpt4 key购买 nike

我有以下三个数据帧,它们由两列“索引”:有一个分组 item1item2item3 等.以及该组中的数字位置148, 179, 188, 267, ...

import pandas as pd

data1 = {"grouping": ["item1", "item1", "item1", "item2", "item2", "item2", "item2", ...],
"positions": [148, 179, 188, 267, 693, 963, 979, ...],
"metadata": [5, 1, 8, 3, 731, 189, 9, ...],
"unique_column1" : ['QLZX9J', 'L3FPRU', '69TVKF', 'N096NQ', 'JM89V5', 'W7JBOL', '63KKZZ', ...] }




data2 = {"grouping": ["item1", "item1", "item1", "item1", "item1", "item1", "item2", ...],
"positions": [118, 241, 431, 448, 455, 677, 740, ...],
"metadata": [5, 1, 8, 3, 731, 189, 9, ...],
"unique_column2" : [4714, 1178, 235, 47, 54, 89, 12, ...] }

data3 = {"grouping": ["item1", "item1", "item1", "item1", "item1", "item1", "item1", ...],
"positions": [118, 148, 179, 188, 241, 431, 448,...],
"metadata": [5, 1, 8, 3, 731, 189, 9, ...],
"unique_column3" : ['a', 'a', 'a', 'a', 'a', 'a', 'a', ...] }


df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)
df3 = pd.DataFrame(data3)

df1
>>> grouping positions metadata unique_column1
0 item1 148 5 QLZX9J
1 item1 179 1 L3FPRU
2 item1 188 8 69TVKF
3 item2 267 3 N096NQ
4 item2 693 731 JM89V5
5 item2 963 189 W7JBOL
6 item2 979 9 63KKZZ
7 .... ... ... ...

df2
>>> grouping positions metadata unique_column2
0 item1 118 5 4714
1 item1 241 1 1178
2 item1 431 8 235
3 item1 448 3 47
4 item1 455 731 54
5 item1 677 189 89
6 item2 740 9 12

df3
>>> grouping positions metadata unique_column3
0 item1 118 5 a
1 item1 148 1 a
2 item1 179 8 a
3 item1 188 3 a
4 item1 241 731 a
5 item1 431 189 a
6 item1 448 9 a

我想通过分组位置合并这三个数据帧,以便分组的行= item1<df2 中的/code>、positions = 118df3 中的同一行合并。这些数据帧之间有许多相同的列,不应复制。事实上,df1 中最终合并数据帧中要合并的唯一唯一列是 unique_column1,而在 df2 中是 unique_column2

如何将三个数据帧中的一列合并在一起,仅使用两列作为索引?这看起来比 pandas.merge() 稍微复杂一点

如果某个 tem 不存在,则它应该为 0。合并后的表应如下所示:

merged 
grouping positions metadata unique_column1 unique_column2 unique_column3
item1 118 5 0 4714 'a'
item1 148 1 'QLZX9J' 0 'a'
item1 179 8 'L3FPRU' 0 'a'
item1 188 3 '69TVKF' 0 'a'
item1 241 731 0 1178 'a'
.........

最佳答案

dfs = [df1, df2, df3]

jcols = ['grouping', 'positions']
ucols = ['unique_column1','unique_column2','unique_column3']

pd.concat([df.set_index(jcols)[df.columns.intersection(ucols)]
for df in dfs],
axis=1) \
.reset_index() \
.fillna(0)

结果:

   grouping  positions unique_column1  unique_column2 unique_column3
0 item1 118 0 4714.0 a
1 item1 148 QLZX9J 0.0 a
2 item1 179 L3FPRU 0.0 a
3 item1 188 69TVKF 0.0 a
4 item1 241 0 1178.0 a
5 item1 431 0 235.0 a
6 item1 448 0 47.0 a
7 item1 455 0 54.0 0
8 item1 677 0 89.0 0
9 item2 267 N096NQ 0.0 0
10 item2 693 JM89V5 0.0 0
11 item2 740 0 12.0 0
12 item2 963 W7JBOL 0.0 0
13 item2 979 63KKZZ 0.0 0

关于python - Pandas dataframe : merge three dataframes by two columns, 忽略大多数列?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/43478457/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com