基于列中的数据合并两个CSV文件

Question

我有两个csv文件，如下所示。

CSV版

data13      data23      d      main_data1;main_data2      data13         data23
data12      data22      d      main_data1;main_data2      data12         data22
data11      data21      d      main_data1;main_data2      data11         data21
data3       data4       d      main_data2;main_data4      data3          data4
data52      data62      d      main_data3                 data51         data62
data51      data61      d      main_data3                 main_data3     data61
data7       data8       d      main_data4                 data7          data8

CSV2型

id1      main_data1      a1      a2      a3
id2      main_data2      b1      b2      b3
id3      main_data3      c1      c2      c3
id4      main_data4      d1      d2      d3
id5      main_data5      e1      e2      e3

现在我的问题是，当两个文件中的一列完全相同时，我知道如何合并两个CSV文件。但我的问题有点不同。CSV1的第4列可能包含CSV2的第2列。我想得到一个CSV文件如下

最终的

id1      main_data1      a1      a2      a3      data13
id2      main_data2      b1      b2      b3      data3
id3      main_data3      c1      c2      c3      main_data3
id4      main_data4      d1      d2      d3      data7
id5      main_data5      e1      e2      e3

其中：
1。它匹配来自两列的数据，并从第一个匹配项获取相应的行，然后写入csv文件。
2.如果没有匹配项，它可以将FINAL_CSV中的最后一列留空，或写“NA”或类似的任何内容。
3.当CSV1的第4列和第5列中的数据完全匹配时，它将返回该行而不是第一行。

我完全不知道该怎么做。帮我做一部分也很好。如有任何建议，我们将不胜感激。
PS-我知道csv文件中的数据应该用逗号分隔，但为了清晰起见，我更喜欢使用制表符，尽管实际数据是用逗号分隔的。

编辑：实际上，“主数据”可以在CSV2的任何列中，而不仅仅是在第2列中。同样的“main_data”也可以在多行中重复，然后我想得到所有对应的行。

Answer 1

由于合并的条件似乎很复杂，因此将数据加载到数据库并使用SQL可能是值得的。在内存中使用SQLite可以这样做（假设数据是逗号分隔的）

import csv
import sqlite3

def createTable(cursor, rows, tablename):
    tableCreated = False
    for row in rows:
        if not tableCreated:
            sql = "CREATE TABLE %s(ROW INTEGER PRIMARY KEY, " + ", ".join(["c%d" % (i+1) for i in range(len(row))]) + ")"
            cur.execute(sql % tablename)
            tableCreated = True
        sql = "INSERT INTO %s VALUES(NULL, " + ", ".join(["'" + c + "'" for c in row]) + ")"
        cur.execute(sql % tablename)
    conn.commit()


conn = sqlite3.connect(":memory:")
cur = conn.cursor()

for filename, tablename in [(path_to_csv1, "CSV1"), (path_to_csv2, "CSV2")]:
    with open(filename, "r") as f:
        reader = csv.reader(f, delimiter=',')        
        rows = [row for row in reader]
    createTable(cur, rows, tablename)

然后可以在SQL中构造连接逻辑。您可以运行以下查询：

for row in cur.execute(your_sql_statement):
    print row

以下查询提供所需的输出：

WITH
MATCHES AS( -- get all matches
    SELECT      CSV2.*
                , CSV1.ROW as ROW_1                 
                , CSV1.C4 as C4_1
                , CSV1.C5 as C5_1
    FROM        CSV2 
    LEFT JOIN   CSV1 
    ON          CSV1.C4 LIKE '%' || CSV2.C2 || '%'    
),
EXACT AS( -- matches where CSV1.C4 = CSV1.C5
    SELECT      *
    FROM        MATCHES
    WHERE       C4_1 = C5_1
),
MIN_ROW AS( -- CSV1.ROW of first occurence for each CSV2.C1
    SELECT      C1
                , min(ROW_1) as ROW_1
    FROM        MATCHES
    WHERE       C1 NOT IN (SELECT C1 FROM EXACT)
    GROUP BY    C1, C2, C3, C4, C5                  
)
-- use C4=C5 first
SELECT      *
FROM        EXACT
UNION
-- if match not in exact, use first occurence
SELECT      MATCHES.*
FROM        MIN_ROW
INNER JOIN  MATCHES
ON          MIN_ROW.C1 = MATCHES.C1
AND         (MIN_ROW.ROW_1 = MATCHES.ROW_1 OR MIN_ROW.ROW_1 IS NULL)
ORDER BY    C1

Answer 2

你考虑过用pandas吗？如果您熟悉R，那么数据帧应该非常简单。以下是您想要的：

from pandas import merge, read_table

csv1 = read_table('CSV1.csv', sep=r"[;,]", header=None)
csv2 = read_table('CSV2.csv', sep=r"[,]",  header=None)

print csv1
print csv2

注意，我用逗号替换了制表符，并用分号分隔。目前的产出应该是：

        0       1   2           3           4           5       6
0  data13  data23   d  main_data1  main_data2      data13  data23
1  data12  data22   d  main_data1  main_data2      data12  data22
2  data11  data21   d  main_data1  main_data2      data11  data21
3   data3   data4   d  main_data2  main_data4       data3   data4
4  data52  data62   d  main_data3         NaN      data51  data62
5  data51  data61   d  main_data3         NaN  main_data3  data61
6   data7   data8   d  main_data4         NaN       data7   data8

[7 rows x 7 columns]
     0           1   2   3   4
0  id1  main_data1  a1  a2  a3
1  id2  main_data2  b1  b2  b3
2  id3  main_data3  c1  c2  c3
3  id4  main_data4  d1  d2  d3
4  id5  main_data5  e1  e2  e3

[5 rows x 5 columns]

使用左连接：

kw1 = dict(how='left', \
          left_on=[3,4], \
          right_on=[1,1], \
          suffixes=('l', 'r'))

df1 = merge(csv1, csv2, **kw1)
df1.drop_duplicates(cols=[3], inplace=True)

print df1[[0,7]]

给出合并的第0列和第7列：

            3       5
0  main_data1  data13
3  main_data2   data3
4  main_data3  data51
6  main_data4   data7

[4 rows x 2 columns]

要按您的需要提供输出，请使用CSV2执行另一个合并（这次是外部连接）：

kw2 = dict(how='outer', \
           left_on=[3], \
           right_on=[1], \
           suffixes=('l', 'r'))

df2 = merge(df1, csv2, **kw2)

print df2[[15,16,17,18,19,8]]

输出：

     0           1   2  3r  4r       5
0  id1  main_data1  a1  a2  a3  data13
1  id2  main_data2  b1  b2  b3   data3
2  id3  main_data3  c1  c2  c3  data51
3  id4  main_data4  d1  d2  d3   data7
4  id5  main_data5  e1  e2  e3     NaN

不必对关键字参数使用**kw。我只是用它来让所有的东西水平放置。

我让read_table和merge决定列名。如果您自己指定列名，您将获得更美观的输出。

Answer 3

用锥子的方法。

 awk -F, 'NR==FNR{a[$2]=$0;next}
         {split($4,b,";");x=b[1]}
         (x in a)&&!c[x]++{d[x]=$5}
         ($5 in a){d[$5]=$5}
         END{n=asorti(a,e);for(i=1;i<=n;i++)print a[e[i]]","d[e[i]]}'  CSV1 CSV2

输出

id1,main_data1,a1,a2,a3,data13
id2,main_data2,b1,b2,b3,data3
id3,main_data3,c1,c2,c3,main_data3
id4,main_data4,d1,d2,d3,data7
id5,main_data5,e1,e2,e3,