匹配不同的列并将它们用python结合起来

Question

我有两个文本文件。

首先是空间分隔的列表：

23 dog 4 
24 cat 5 
28 cow 7 

二是'|' - 分隔列表：

?dog|parallel|numbering|position 
Dogsarebarking 
?cat|parallel|nuucers|position 
CatisBeautiful 

我想类似以下的输出文件：

?dog|paralle|numbering|position|23 
?cat|parallel|nuucers|position|24 

这是一个'|'-包含第二个文件应用程序的值的分离列表以第一个文件的第一列中的值与两个文件的第二列中的值匹配的值结束。

Answer 1

使用csv来读取第一个文件和一个字典来存储file1行。第二个文件是FASTA格式，所以我们只需要开始?行：

import csv 

with open('file1', 'rb') as file1: 
    file1_data = dict(line.split(None, 2)[1::-1] for line in file1 if line.strip()) 

with open('file2', 'rb') as file2, open('output', 'wb') as outputfile: 
    output = csv.writer(outputfile, delimiter='|') 
    for line in file2: 
     if line[:1] == '?': 
      row = line.strip().split('|') 
      key = row[0][1:] 
      if key in file1_data: 
       output.writerow(row + [file1_data[key]]) 

这将产生：

?dog|parallel|numbering|position|23 
?cat|parallel|nuucers|position|24 

您的输入例子。

Answer 2

这是哪门子的任务，在该pandas库擅长：

import pandas as pd 
df1 = pd.read_csv("c1.txt", sep="|", header=None).dropna() 
df2 = pd.read_csv("c2.txt", sep=" ", header=None) 
merged = df1.merge(df2, on=1).ix[:,:-1] 
merged.to_csv("merged.csv", sep="|", header=None, index=None) 

---

一些解释如下。首先，我们在文件中读取，为对象称为DataFrames：

>>> df1 = pd.read_csv("c1.txt", sep="|", header=None).dropna() 
>>> df1 
       0  1   2   3 
0  ?parallel dog numbering position 
3  ?parallel cat nuucers position 
6 ?non parallel honey numbering position 
>>> df2 = pd.read_csv("c2.txt", sep=" ", header=None) 
>>> df2 
    0 1 2 
0 23 dog 4 
1 24 cat 5 
2 28 cow 7 

的.dropna()跳过那里没有任何数据情况。或者，df1 = df1[df1[0].str.startswith("?")]应该是另一种方式。

然后我们把它们合并第一列：

>>> df1.merge(df2, on=1) 
     0_x 1  2_x   3 0_y 2_y 
0 ?parallel dog numbering position 23 4 
1 ?parallel cat nuucers position 24 5 

我们不需要那么最后一列，所以我们分析它：

>>> df1.merge(df2, on=1).ix[:,:-1] 
     0_x 1  2_x   3 0_y 
0 ?parallel dog numbering position 23 
1 ?parallel cat nuucers position 24 

，然后我们使用to_csv写出来，生产：

>>> !cat merged.csv 
?parallel|dog|numbering|position|23 
?parallel|cat|nuucers|position|24 

现在，对于很多简单的任务，pandas可以矫枉过正，学习如何使用csv模块等更低级别的工具也很重要。 OTOH，当你只想完成某件事时，它非常非常方便。

Answer 3

这似乎正是JOIN适用于关系数据库。

An inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

有一个看看这个例子：

import sqlite3 
conn = sqlite3.connect('example.db') 

# get hands on the database 
c = conn.cursor() 

# create and populate table1 
c.execute("DROP TABLE table1") 
c.execute("CREATE TABLE table1 (col1 text, col2 text, col3 text)") 
with open("file1") as f: 
    for line in f: 
     c.execute("INSERT INTO table1 VALUES (?, ?, ?)", line.strip().split()) 

# create table2 
c.execute("DROP TABLE table2") 
c.execute("CREATE TABLE table2 (col1 text, col2 text, col3 text, col4 text)") 
with open("file2") as f: 
    for line in f: 
     c.execute("INSERT INTO table2 VALUES (?, ?, ?, ?)", 
      line.strip().split('|')) 

# make changes persistent 
conn.commit() 

# retrieve desired data and write it to file 
with open("file3", "w+") as f: 
    for x in c.execute(
     """ 
     SELECT table2.col1 
      , table2.col2 
      , table2.col3 
      , table2.col4 
      , table1.col1 
     FROM table1 JOIN table2 ON table1.col2 = table2.col2 
     """): 
     f.write("%s\n" % "|".join(x)) 

# close connection 
conn.close() 

输出文件应该是这样的：

paralle|dog|numbering|position|23 
parallel|cat|nuucers|position|24