Fitering rows of one CSV file based on two values from another file in Bash

Question

I have two CSV files.

• One is long with columns: Chr_Name, h, j, start_pos, end_pos.
• Another is short file including columns Chr_Name and position.

I need to filter the rows from the first file based on Chr_Name first and then the position that falls between the start and end position of the first file.

How can I do that?

Example:

• File 1:

  Chrk, 10, 20,  1010, 1025
  Chrk, 20, 10, 1020, 1040
  ChrM, 10, 10, 50, 120
  

• File 2:

  Chrk, 1030
  ChrM, 70
  

• Desired output file:

  Chrk, 20, 10, 1020, 1040
  ChrM, 10, 10, 50, 120
  

Answer 1

Assuming you have two CSV files as follows:

$ cat file1
Chr_Name,h,j,start_pos,end_pos
Chrk,10,20,1010,1025
Chrk,20,10,1020,1040
ChrM,10,10,50,120

$ cat file2
Chr_Name,position
Chrk,1030
ChrM,70

You may use Miller (mlr) to join the two files on the common field Chr_Name, filter the resulting data by extracting only the records that have a position field that fall between start_pos and end_pos, and then finally cut the unwanted position field from the data.

$ mlr --csv join -f file2 -j Chr_Name then filter '$start_pos <= $position && $position <= $end_pos' then cut -x -f position file1
Chr_Name,h,j,start_pos,end_pos
Chrk,20,10,1020,1040
ChrM,10,10,50,120

The mlr command, nicely formatted:

mlr --csv \
    join -f file2 -j Chr_Name then \
    filter '$start_pos <= $position && $position <= $end_pos' then \
    cut -x -f position \
    file1

---

With the same two files as above, but using SQLite3 with an in-memory database as suggested by Marcus Müller in comments:

$ sqlite3 :memory: '.mode csv' '.headers on' '.import file1 file1' '.import file2 file2' 'SELECT file1.* FROM file1 JOIN file2 ON (file1.Chr_Name = file2.Chr_name) WHERE CAST(position AS INTEGER) BETWEEN start_pos AND end_pos'
Chr_Name,h,j,start_pos,end_pos
Chrk,20,10,1020,1040
ChrM,10,10,50,120

The SQLite3 statements:

.mode csv
.headers on
.import file1 file1
.import file2 file2

SELECT file1.* FROM file1
    JOIN file2 ON (file1.Chr_Name = file2.Chr_name)
    WHERE CAST(position AS INTEGER) BETWEEN start_pos AND end_pos

The dotted commands imports the two files into the tables file1 and file2 while the SELECT statement performs the actual query.

Answer 2

Using any awk, regardless of whether you really do have spaces after the ,s or not:

$ awk -F', *' 'NR==FNR{a[$1]=$2; next} ($1 in a) && ($4 <= a[$1]) && (a[$1] <= $5)' File2 File1
Chrk, 20, 10, 1020, 1040
ChrM, 10, 10, 50, 120