如果行满足特定范围，如何打印行

Question

我有两个大文件，如下所示：

f1：

chr1,3073253,3074322,gene_id,"ENSMUSG00000102693.1",gene_type,"TEC"
chr1,3074253,3075322,gene_id,"ENSMUSG00000102693.1",transcript_id,"ENSMUST00000193812.1"
chr1,3077253,3078322,gene_id,"ENSMUSG00000102693.1",transcript_id,"ENSMUST00000193812.1"
chr1,3102916,3103025,gene_id,"ENSMUSG00000064842.1",gene_type,"snRNA"
chr1,3105016,3106025,gene_id,"ENSMUSG00000064842.1",transcript_id,"ENSMUST00000082908.1"

f2：

chr,name,start,end
chr1,linc1320,3073300,3074300
chr3,linc2245,3077270,3078250
chr1,linc8956,4410501,4406025

我想做的是，如果file2的start和end列的范围在file1（第2列和第3列）和chr的范围内，则在file1中的单独列中打印file2的行。因此，根据我提供的虚拟示例文件，所需的输出应该是（只有linc1320的范围在文件1的第一行中）：

chr1,3073253,3074322,gene_id,"ENSMUSG00000102693.1",gene_type,"TEC",linc1320,3073300,3074300
chr1,3074253,3075322,gene_id,"ENSMUSG00000102693.1",transcript_id,"ENSMUST00000193812.1"
chr1,3077253,3078322,gene_id,"ENSMUSG00000102693.1",transcript_id,"ENSMUST00000193812.1"
chr1,3102916,3103025,gene_id,"ENSMUSG00000064842.1",gene_type,"snRNA"
chr1,3105016,3106025,gene_id,"ENSMUSG00000064842.1",transcript_id,"ENSMUST00000082908.1"

我不是专业的编码器，但我一直在使用此代码根据文件2手动更改范围：

awk -F ',' '$2<=3073300,$3>=3074300, {print $1,$2,$3,$4,$5,$6,$7}' f1.csv

我并不特别喜欢使用特定的编程语言Python和awk都会很有帮助。谢谢你的帮助

Answer 1

让我们尝试以pandas的方式解决问题，首先将csv文件读入pandas数据帧

f1 = pd.read_csv('file1.csv', header=None)
f2 = pd.read_csv('file2.csv')

>>> f1

      0        1        2        3                     4              5                     6
0  chr1  3073253  3074322  gene_id  ENSMUSG00000102693.1      gene_type                   TEC
1  chr1  3074253  3075322  gene_id  ENSMUSG00000102693.1  transcript_id  ENSMUST00000193812.1
2  chr1  3077253  3078322  gene_id  ENSMUSG00000102693.1  transcript_id  ENSMUST00000193812.1
3  chr1  3102916  3103025  gene_id  ENSMUSG00000064842.1      gene_type                 snRNA
4  chr1  3105016  3106025  gene_id  ENSMUSG00000064842.1  transcript_id  ENSMUST00000082908.1


>>> f2

    chr      name    start      end
0  chr1  linc1320  3073300  3074300
1  chr3  linc2245  3077270  3078250
2  chr1  linc8956  4410501  4406025

现在我们可以merge和filter满足给定区间包含条件的行，然后我们可以join使用文件f1过滤行

m = f1.reset_index()\
      .merge(f2, left_on=0, right_on='chr')\
      .where(lambda x: x[1].le(x['start']) & x[2].ge(x['end']))\
      .set_index('index')[['name', 'start', 'end']]

f3 = f1.join(m)

---

>>> f3

      0        1        2        3                     4              5                     6      name      start        end
0  chr1  3073253  3074322  gene_id  ENSMUSG00000102693.1      gene_type                   TEC  linc1320  3073300.0  3074300.0
1  chr1  3074253  3075322  gene_id  ENSMUSG00000102693.1  transcript_id  ENSMUST00000193812.1       NaN        NaN        NaN
2  chr1  3077253  3078322  gene_id  ENSMUSG00000102693.1  transcript_id  ENSMUST00000193812.1       NaN        NaN        NaN
3  chr1  3102916  3103025  gene_id  ENSMUSG00000064842.1      gene_type                 snRNA       NaN        NaN        NaN
4  chr1  3105016  3106025  gene_id  ENSMUSG00000064842.1  transcript_id  ENSMUST00000082908.1       NaN        NaN        NaN

PS：您还可以使用f3.to_csv('file3.csv')将生成的数据帧f3保存到csv文件