基于多个文件中的三列使用数据帧进行合并

Question

我想用pd.merge合并几乎10个文件每个文件都有这样的数据

chrom   start   end name    score   strand  splice_site acceptors_skipped   exons_skipped   donors_skipped  anchor  known_donor known_acceptor  known_junction  genes   transcripts
4      3487839 3491240  JUNC00148541    101 -   GT-AG   2   1   3   DA  1   1   1   Tmem68  ENSMUST00000029891,ENSMUST00000108388,ENSMUST00000154922
4      3489293 3491240  JUNC00148543    1   -   GT-AG   1   0   1   DA  1   1   1   Tmem68  ENSMUST00000029891,ENSMUST00000108388,ENSMUST00000154922

我以前使用过merge，使用pd.merge(df_a, df_b, on='gene', how='outer')只传递一列，这里我希望根据chrom、start和end以及strand来合并它们。你知道吗

我的新df看起来像

chrm:start-end(strand) score_file1 score_file2 ...file10 gene_name splice_site acceptores exon_skipped donors_skipped...transcripts 

如果没有匹配项how='outer'，我假定将输入一个NaN值。用更少的内存来解决这个问题，最好的方法是什么？你知道吗

path = r'/Users/PycharmProjects/'
all_files = glob.glob(os.path.join(path, "*_bed.txt"))
print(all_files)
df1 = pd.read_table(all_files[0])
df2= pd.read_table(all_files[1])

concatnated_df = pd.merge(df1,df2, on=['genes','chrom','start','end'], how='outer')
print(concatnated_df.head(n=5))

感谢您的帮助！你知道吗

更新的简化问题：

chr start end score strand gene
1   20    30  50    -      abc1
2   40    50  50    +      cdf1

10个csv文件，根据chr、start-end和gene进行合并（精确匹配） 新df

chr start end score_file1 score_file2..file10 strand gene
1   20    30  50  20 40   -      abc1
2   40    50  50  30 50   +      cdf1

Answer 1

dfs = [df1[['chr','gene','start','end','score']],
       df2[['chr','gene','start','end','score']],
       df3[['chr','gene','start','end','score']],
       df10[['chr','gene','start','end','score']]] 
df_final = reduce(lambda left,right: pd.merge(left,right,on=
                  ['gene','chr','start','end'], how='outer'),dfs)