我正在尝试从CSV中读取、合并和附加大量的内容。所有的基础工作都很正常。但是,我正在覆盖我的结果集,并且无法更正它。
两个文件中的数据都非常简单:
# Small data set
A,B,C
1,2,101
3,4,102
9,10,103
# Large data set(used in chunk below)
A,B,C
1,2,1000
3,4,2000
9,10,3000
示例代码
import pandas as pd
# Read CSVs
inventory_1 = pd.read_csv("file1.csv")
# Create new DF to hold the merge results
bucket = pd.DataFrame(columns=list("ABC"))
# Use chunk to read in the large file, merge and append the data
for chunk in pd.read_csv("file2.csv",chunksize=2):
chunk_merge = pd.merge(
inventory_1, chunk,
left_on=['A'],
right_on=['A'],
how='left')
result = bucket.append(chunk_merge)
print(result)
发生的情况是,合并将在区块中的数据上正确工作,但以前的结果在结果中被覆盖。因此,在上面的示例中,我得到了:
# 1st Loop
A B B_x B_y C C_x C_y
0 1 NaN 2.0 2.0 NaN 1000.0 101.0
1 3 NaN 4.0 4.0 NaN 2000.0 102.0
2 9 NaN 10.0 NaN NaN 3000.0 NaN
# 2nd Loop
A B B_x B_y C C_x C_y
0 1 NaN 2.0 NaN NaN 1000.0 NaN
1 3 NaN 4.0 NaN NaN 2000.0 NaN
2 9 NaN 10.0 10.0 NaN 3000.0 103.0
我需要的答案是:
A B_x C_x B_y C_y
0 1 2 1000 2 101
1 3 4 2000 4 102
2 9 10 3000 10 103
我觉得答案就在我面前,但我看不到。任何帮助都将不胜感激。
发布于 2018-06-19 04:46:07
正如我在评论中所说,覆盖的问题来自于您在数据帧上使用append
的方式,当您重新分配result
时,数据会丢失。使用您提供的示例,您可以在每个循环中将chunk_merge附加到列表中,然后使用pd.concat
。
inventory_1 = pd.read_csv("file1.csv")
list_to_concat = [] #empty list you will append with chunk_merge
for chunk in pd.read_csv("file2.csv",chunksize=2):
list_to_concat.append( pd.merge(
inventory_1, chunk,
on='A', #simple on as both column have the same name
how='inner')) # this will help for concat, if you want to keep left, then a dropna is necessary
result = pd.concat(list_to_concat) #add .dropna() if left above
使用你的数据,我人为地将你的“大数据集”分成2行的df和1行的df,以重新创建想法,最后,我得到:
result
Out[366]:
A B_x C_x B_y C_y
0 1 2 101 2 1000
1 3 4 102 4 2000
0 9 10 103 10 3000
请注意,C_x和C_y是交换的(B也是,但您看不到数据),因为您首先在inventory_1
上合并,但在其他情况下它是您想要的
发布于 2018-06-19 05:11:50
>>> df1=pd.DataFrame({'A': [1,3,9], 'B': [2,4,10], 'C': [101,102,103]})
>>> df2=pd.DataFrame({'A': [1,3,9], 'B': [2,4,10], 'C': [1000, 2000, 3000]})
>>>
>>> df2.merge(df1, on='A')
A B_x C_x B_y C_y
0 1 2 1000 2 101
1 3 4 2000 4 102
2 9 10 3000 10 103
>>>
https://stackoverflow.com/questions/50916860
复制相似问题