0

I have 2 CSV files which look like follows:

file1.csv

col1,col2,col3,col4,date,time,col7
1,2,3,4,2019-07-20,12:40:00,0
1,2,3,4,2019-07-20,12:43:00,0
1,2,3,4,2019-07-20,12:44:00,0
1,2,3,4,2019-07-20,12:45:00,0
1,2,3,4,2019-07-20,12:46:00,0
1,2,3,4,2019-07-20,12:47:00,0

file2.csv

date,time,col3
2019-07-20,12:40:00,1
2019-07-20,12:41:00,2
2019-07-20,12:42:00,3
2019-07-20,12:43:00,4
2019-07-20,12:44:00,5
2019-07-20,12:45:00,6
2019-07-20,12:46:00,7
2019-07-20,12:47:00,8

As one can see in file2.csv, for for rows 2 and 3 with timestamps 2019-07-20,12:41:00 and 2019-07-20,12:42:00 respectively, no corresponding rows exist with the same timestamp in file1.csv. I would like to output file2.csv with those specific rows removed so that only rows with matching timestamps in file1.csv will remain.

Is it possible to perform this with a simple command in command line? I have a hunch it might involve regex matching but I am not sure about the most efficient way to perform this operation.

Thank you!

1
4

Here's one way:

$ awk -F, 'NR==FNR {a[$5 FS $6]; next} ($1 FS $2) in a' file1 file2  
date,time,col3
2019-07-20,12:40:00,1
2019-07-20,12:43:00,4
2019-07-20,12:44:00,5
2019-07-20,12:45:00,6
2019-07-20,12:46:00,7
2019-07-20,12:47:00,8
6
  • @thanasisp why would a[$5 FS $6] be any different than a[$5$6]? And surely FS isn't the right choice! FS can be an expression. Did you mean OFS? – terdon Nov 26 '20 at 11:05
  • 1
    FS is the right choice, because it cannot exist into a field after splitting. OFS would be good if we had set it first here, now it is space and FS is comma. The reason is demonstrated by this example pair test1,test2 and test1t,est2 both fields compinations would have the same $1$2. – thanasisp Nov 26 '20 at 11:08
  • 1
    @thanasisp ah! Yes indeed, I see. I still don't think using FS is a good idea though. Consider cases where FS is [a : *] or any other character class. It's absolutely the right thing here, but not in more complex cases. – terdon Nov 26 '20 at 11:10
  • @thanasisp yes, that does make sense. Good suggestion, thanks! – terdon Nov 26 '20 at 11:25
  • What speaks against the awk standard mechanism of "fake" two-dimensional arrays, which uses a special character stored in SUBSEP (by default 0x1c). You could then use a[$5,$6] for two-dimensional arrays even without GNU awk (btw, AFAIK this means that $5,$6 as array index will NOT concatenate using FS),. – AdminBee Nov 26 '20 at 11:26
0
csv-sqlite -i file1.csv -i file2.csv \
  'select * from input2 where date || time in (select date || time from input1) |
  csv-header --remove-types'

csv-sqlite is from csv-nix-tools.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.