0

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
    
9
  • 2
    Also, why bash and awk? Neither seems to be an appropriate tool here. Simply loading the two CSV files into an in-memory or temporary database using sqlite3 would make this significantly easier, and if your data is large, faster Commented Sep 26, 2023 at 22:22
  • 2
    please update the question with the code you've tried and the (wrong) results generated by your code
    – markp-fuso
    Commented Sep 26, 2023 at 23:43
  • 1
    Your CSV file has whitespace between columns? Commented Sep 27, 2023 at 10:08
  • 1
    @MarcusMüller maybe some people find using a DB easier, but I cannot imagine how the complication of actually loading into a DB could possibly be easier than a simple awk script! If you think it is, please post an answer explaining the approach, I would be interested. But presumably it requires at least two steps (create the DB, then run the relevant query) so I really don't see how it could be considered simpler.
    – terdon
    Commented Sep 27, 2023 at 11:18
  • 1
    Ah, yes, well, that seems very far from simple to me. I thought you might have a simpler approach.
    – terdon
    Commented Sep 27, 2023 at 11:42

2 Answers 2

2

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.

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .