0

I have a file that looks like this:

FILE1:

0/28
7200/11
14400/11
21584/28
21600/11
28800/28
36000/11
36000/28
43200/11
43200/28
50400/11
57600/11
79200/28

In the left part (before / ) I have the time in seconds, and in the right part I have a value for a parameter at that respective second.

Now I have another file that looks like this:

FILE2:

0 14
0 15
0 20
0 28
7200 11
7200 14
7200 15

Now, I want to remove from the second file the common values from FILE1. For example I should remove from FILE2:

0 28
7200 11

and keep the rest of the lines as they are.

I was thinking to use a for loop in a bash script for each line in FILE1 and then search for that in FILE2, but I can't manage to recognize the pattern. If I try to use substr from awk it won't work because the time does not have the same digits (0 has 1 digit and 7200 has 4).

To read FILE1 I am doing something like this:

IFS=$'\n' read -d '' -r -a X < ./FILE1.csv

And to write the for loop I am doing something like this:

for x in "${X[@]}"
do
    gawk -i inplace -v var=${x} '{...}' FILE2.csv
done

I am also thinking to transform FILE1 in something like this:

0 28
7200 11
14400 11
21584 28
21600 11
28800 28
36000 11
36000 28
43200 11
43200 28
50400 11
57600 11
79200 28

to have basically 2 columns, but using the for as I used it above and var, won't work if I have more than 1 column. I think this second approach is better, but I have no idea how to make it process each column individually.

EDIT:

How would I do that if FILE1 is:

0 28
7200 11
14400 11
21584 28
21600 11
28800 28
36000 11
36000 28
43200 11
43200 28
50400 11
57600 11
79200 28

and FILE2 is:

0 14 2 19
0 15 157 67
0 20 28 57
0 28 25 67
7200 11 88 14
7200 14 34 247
7200 15 364 14
1

4 Answers 4

4

Using awk:

awk 'NR==FNR { sec[$1, $2]; next } !($1, $2) in sec' FS='/' file1 FS=' ' file2
0 14
0 15
0 20
7200 14
7200 15

The FS (Field Seperator) before each input file defines that file's field seperator.

12
  • Why are you using SUBSEP instead of just , here? You're defining the array and using ,, so it seems that SUBSEP only makes this more confusing and longer to type. What am I missing?
    – terdon
    Apr 8, 2022 at 11:52
  • 3
    @terdon that way it will gives you false positive output, for example file1 have 1/22 comparison with file2 have 12 2, those are not the same but then it will be deleted wrongly from file2. Apr 8, 2022 at 12:21
  • 1
    Ah-ha! Yes indeed, there's what I was missing!
    – terdon
    Apr 8, 2022 at 12:38
  • 1
    You can use ($1,$2) in sec instead of $1 SUBSEP $2 in sec but consider awk '{key=$1 RS $2} NR==FNR { sec[key]; next } !(key in sec)' instead so you don't have to define the key construction twice. Use SUBSEP instead of RS as the separator if you prefer.
    – Ed Morton
    Apr 8, 2022 at 16:37
  • 1
    @αғsнιη You're welcome. I couldn't give you chapter and verse, sorry, but in my mind at least you can only use , as an array subscript separator when it's clear you're using it in the context of an array and key=(1,2) doesn't have an array anywhere so it's ambiguous if you're trying to use the comma as part of a subscript, range, interval, arg list or something else while arr[1,2] is clearly an array context since [] is an array operator and so is (1,2) in arr since in is an array operator.
    – Ed Morton
    Apr 9, 2022 at 12:23
2

Answering the added question at the end:

$ join -v 2 <(sed 's/ /:/' file1) <(sed 's/ /:/' file2) | sed 's/:/ /'
0 14 2 19
0 15 157 67
0 20 28 57
7200 14 34 247
7200 15 364 14

As with the other join variation further down in this answer (which provides the answer to the original question), this makes sure that the join key is a single string with no whitespace, and then picks out the lines from the second file for which the join key does not match any entries in the first file.

This makes the same assumption about the files having to be sorted in the same way. As join only ever keeps two lines in memory at once, we still have the same benefit over grep and any other solution that needs to keep all the entries from one file in memory.


Using the original file1 and file2 from your question, transforming the first file with tr on the fly to the same format as the second file, and using the reformatted data as a set of lines to remove from the second file.

$ grep -v -x -F -f <(tr '/' ' ' <file1) file2
0 14
0 15
0 20
7200 14
7200 15

The grep utility is used here to filter out (remove, exclude) lines from file2 that are the same as the transformed lines from file1.

The -x option forces a full line match (not substring match as usual), and -F makes grep use the patterns as fixed strings rather than regular expressions. The -f option tells the utility to read the patterns from the named file (the process substitution), and -v inverts the usual sense of the matching so that lines that does not match are outputted.

Also relevant to some of the text in your question:


A more efficient way of doing this is to use join. This may be a good idea if your file1 is large. On large inputs, this is expected to be considerably faster than using grep.

The below assumes that your two files are both sorted the same way, and transforms the second file into the same format as the first file (with spaces replaced by slashes) to generate lines without whitespace. We do the transformation in this way as join use whitespace as delimiters by default, and we need to consider the whole line, not just the first space-delimited field.

$ join -v 2 file1 <(tr ' ' '/' <file2) | tr '/' ' ' 
0 14
0 15
0 20
7200 14
7200 15

This performs a relational JOIN operation between the two datasets and returns the lines that are not matched in the second input to join (the transformed second file). Since we want to have space-delimited data as the final result, we replace the slashes with spaces at the end.

This will never hold more than two lines of data in memory at any one time, whereas the grep variation would need to keep the entirety of the first file in memory, and also would need to test each line of that file against every line of the second file.

3
  • Thank you! I also have another question. If in FILE2 I have more than 2 columns ( let's say another 2 parameters) and these columns are not part of FILE1 (FILE1 just has 2 columns), but I still want to eliminate the values in FILE1 from FILE2, based just on the first 2 columns, how would I do that? Are grep or join still a valid option?
    – IceCode
    Apr 8, 2022 at 10:25
  • @user I don't believe this was part of your question.
    – Kusalananda
    Apr 8, 2022 at 11:19
  • @user See updated answer. I'm sorry for the delay; my day job takes precedence over things here.
    – Kusalananda
    Apr 8, 2022 at 15:01
-1

I will solve this problem by using a shell loop.

cat FILE2 | tr " " / | \
while read i;do
  cat -n FILE1| grep -w "$i" | awk '{print $1}' | \
  while read j;do
    sed -i "${j}d" FILE1
  done
done
1
-2

Here's a solution that doesn't require fudging with SUBSEP, looping through the fields, having the files being pre-sorted, or have a pre-set number of columns/fields :

 mawk -v \_=testfile_001.txt -F/ '
 BEGIN { 
    while(getline<_) {
          __[$!(NF=NF)] 
    }
    _*=close(_)*(FS="^$") } _^($_ in __)' testfile_002.txt 
         
0 14
0 15
0 20
7200 14
7200 15
  • just realized setting FS="^$" for 2nd file is much faster since we're doing line-wide matching, so splitting fields is a waste of time.

Tested and proven working on gawk 5.1.1 (including flags -c/-P), mawk 1.3.4, mawk 1.9.9.6, and macos nawk

-- The 4Chan Teller

You must log in to answer this question.

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