英文:
Problem in concatenating 3 datasets with same column names and values being replaced for the same key column
问题
我正在尝试连接具有相同4个列名的3个数据集，但结果集不是我预期的样子。数据集如下：
数据集 A
| Col_A | Col_B | Col_C | Col_D ||-------|-------|-------|-------|| a10 | b10 | | || a11 | b11 | | || a12 | b12 | 10 | 11 || a13 | b13 | | |
数据集 B
| Col_A | Col_B | Col_C | Col_D ||-------|-------|-------|-------|| a10 | b10 | 12 | 13 || a11 | b11 | 15 | 16 || a12 | b12 | | || a13 | b13 | | |
数据集 C
| Col_A | Col_B | Col_C | Col_D ||-------|-------|-------|-------|| a10 | b10 | | || a11 | b11 | | || a12 | b12 | 17 | 18 || a13 | b13 | 20 | 21 |
我需要的最终数据集看起来应该像这样，即连接数据集的顺序：
最终数据集
| Col_A | Col_B | Col_C | Col_D ||-------|-------|-------|-------|| a10 | b10 | 12 | 13 || a11 | b11 | 15 | 16 || a12 | b12 | 17 | 18 || a13 | b13 | 20 | 21 |
您尝试了常规的concat()方法，但无法解决连接后值被替换的问题。
我注意到您在创建数据集时似乎遗漏了数据帧（DataFrame）的变量名。在重现数据集时，请使用以下代码：
import pandas as pddf_A = pd.DataFrame({'Col_A': ['a10', 'a11', 'a12', 'a13'],'Col_B': ['b10', 'b11', 'b12', 'b13'],'Col_C': ['', '', '10', ''],'Col_D': ['', '', '11', '']})df_B = pd.DataFrame({'Col_A': ['a10', 'a11', 'a12', 'a13'],'Col_B': ['b10', 'b11', 'b12', 'b13'],'Col_C': ['12', '15', '', ''],'Col_D': ['13', '16', '', '']})df_C = pd.DataFrame({'Col_A': ['a10', 'a11', 'a12', 'a13'],'Col_B': ['b10', 'b11', 'b12', 'b13'],'Col_C': ['', '', '17', '20'],'Col_D': ['', '', '18', '21']})
然后，您可以使用以下代码将它们连接起来以获得所需的最终数据集：
concat_all = pd.concat([df_A, df_B, df_C], axis=0, ignore_index=True)
这将创建所需的最终数据集。希望这对您有所帮助！
英文:
I am trying to concatenate 3 datasets with same 4 column names and the resultant set is not what I am expecting. The datasets look like
**dataset A **
| Col_A | Col_B |Col_C | Col_D|| ------ | ------|------|------|| a10 | b10 | | || a11 | b11 | | || a12 | b12 | 10 | 11 || a13 | b13 | | |
**dataset B **
| Col_A | Col_B |Col_C | Col_D|| ------| ----- |------|------|| a10 | b10 | 12 | 13 || a11 | b11 | 15 | 16 || a12 | b12 | | || a13 | b13 | | |
**dataset C **
| Col_A | Col_B |Col_C | Col_D|| ----- | ----- |------|------|| a10 | b10 | | || a11 | b11 | | || a12 | b12 |17 | 18 || a13 | b13 |20 | 21 |
Resultant dataset I need to look like is after concatenating the datasets in the sequence:
**dataset final **
| Col_A | Col_B |Col_C | Col_D|| ----- | ----- |------|------|| a10 | b10 |12 | 13 || a11 | b11 | 15 | 16 || a12 | b12 | 17 | 18 || a13 | b13 | 20 | 21 |
I tried the normal concat() method but I am not able to solve the part where the values are being replaced when concatenation done in the sequence.
concat_all = pd.concat([df_A,df_B,df_C], axis =0)
Recreating the datasets:
import pandas as pddfA = pd.DataFrame({'Col_A': ['a10', 'a11', 'a12', 'a13'],'Col_B': ['b10', 'b11', 'b12', 'b13'],'Col_C': ['', '', '10', ''],'Col_D': ['', '', '11', '']})dfB = pd.DataFrame({'Col_A': ['a10', 'a11', 'a12', 'a13'],'Col_B': ['b10', 'b11', 'b12', 'b13'],'Col_C': ['12', '15', '', ''],'Col_D': ['13', '16', '', '']})dfC = pd.DataFrame({'Col_A': ['a10', 'a11', 'a12', 'a13'],'Col_B': ['b10', 'b11', 'b12', 'b13'],'Col_C': ['', '', '17', '20'],'Col_D': ['', '', '18', '21']})
Any leads would be appreciated
答案1
得分: 0
你可以像这样做：
df = (pd.concat([dfA, dfB, dfC], axis=0).query('Col_C != ""').groupby(['Col_A', 'Col_B']).agg({'Col_C': 'last', 'Col_D': 'last'}))Col_C Col_DCol_A Col_Ba10 b10 12 13a11 b11 15 16a12 b12 17 18a13 b13 20 21
英文:
You can do something like this:
df = (pd.concat([dfA, dfB, dfC], axis=0).query('Col_C != ""').groupby(['Col_A', 'Col_B']).agg({'Col_C': 'last', 'Col_D': 'last'}))Col_C Col_DCol_A Col_Ba10 b10 12 13a11 b11 15 16a12 b12 17 18a13 b13 20 21
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论