基于第二个csv过滤一个csv

2021-08-02 16:08:09 发布

您现在位置:Python中文网/ 问答频道 /正文

在读入两个CSV文件A和B之后,我想删除B中的所有行 其前两列与表中任何行的前两列不匹配。 然后将B保存到磁盘。你知道吗

到目前为止,我掌握的情况如下:

 A =  pd.read_csv('A.csv', header=None)
 B = pd.read_csv('B.csv', header = None)
 for row_A in A.iterrows():
     foundMatch = False
     for row_B in B.iterrows():
         if (row_A[0] == row_B[0] and row_A[1] == row_B[1]) :
             foundMatch = True
             break
     if (!foundMatch)
         # delete row from B
  B.to_csv('B_filtered.csv', index=False, header=None)
2条回答
网友
1楼 ·

IIUC,给定两个数据帧:

dfa = pd.DataFrame({"col1": ["str{}".format(i) for i in range(10)], 
                   "col2": ["str{}".format(chr(i)) for i in range(97,107)]})

    col1    col2
0   str0    stra
1   str1    strb
2   str2    strc
3   str3    strd
4   str4    stre
5   str5    strf
6   str6    strg
7   str7    strh
8   str8    stri
9   str9    strj

dfb = pd.DataFrame({"col1": ["str0", "str1", "str1000"],
                    "col2": ["stra", "strb", "strc"]})

    col1    col2
0   str0    stra
1   str1    strb
2   str1000 strc

您可以首先transform将前两列变成tuples(从而成为可散列对象),然后使用isin检查dfa中是否存在这样的对,即

>>> dfa.transform(tuple, 1)
0    (str0, stra)
1    (str1, strb)
2    (str2, strc)
3    (str3, strd)
4    (str4, stre)
5    (str5, strf)
6    (str6, strg)
7    (str7, strh)
8    (str8, stri)
9    (str9, strj)

所以呢

df_final = dfb.loc[dfb.transform(tuple, 1).isin(dfa.transform(tuple, 1))]

这给了

    col1    col2
0   str0    stra
1   str1    strb

那就保存它to_csv

df_final.to_csv("result.csv")

(旁注:只要可能,尽量不要在数据帧中使用迭代。性能将很差,您将失去此库的真正功能)

网友
2楼 ·

您还可以尝试使用^{}库的纯python解决方案:

from csv import reader
from csv import writer

with open('A.csv') as fileA, \
     open('B.csv') as fileB, \
     open('B_filtered.csv', 'w') as outB:

    csv_writer = writer(outB)

    readerA = reader(fileA)
    readerB = reader(fileB)

    # store first two columns of every row in file A
    A_map = {(col1, col2) for col1, col2, *_ in readerA}

    for col1, col2, *rest in readerB:

        # only write to csv if columns exist in map
        if (col1, col2) in A_map:
            csv_writer.writerow([col1, col2, *rest])

相关问题