0

Using a bash shell script on Ubuntu on a Raspberry Pi, I am trying to remove lines from a (comma separated) CSV list where { Field 1 matches AND Field 3 is less than 5 mins (300 secs) from the first match of Field 1 }.

Here's a sample input file. I have annotated the desired output with # to explain why a line is kept or deleted. What I want is not annotation, but simply to remove the lines that say "delete". The actual input and filtered output file would be something like this:

A11EEA,@N171WT,2021/03/06 12:37:25,700,0.1
A0FC0A,@N1624K,2021/03/06 13:37:33,1975,2.0
...et cetera

Input file with annotated desired output:

A11EEA,@N171WT,2021/03/06 12:37:25,700,0.1     # Keep - 1st occurrence of Field-1
A0FC0A,@N1624K,2021/03/06 13:37:33,1975,2.2    # Keep - 1st occurrence of Field-1
AB8C37,@AAL2386,2021/03/06 13:45:43,4500,1.3   # Keep - 1st occurrence of Field-1
A55325,@N442MG,2021/03/06 15:28:06,600,0.4     # Keep - 1st occurrence of Field-1
AB8C37,@AAL2386,2021/03/06 13:50:46,4500,1.5   # Keep - more than 5 mins from line 3
AB0ED6,@UAL1470,2021/03/06 13:51:23,4925,1.6   # Keep - 1st occurrence of Field-1
AB8C37,@AAL2386,2021/03/06 13:52:48,4500,1.7   # Delete - less than than 5 mins from line 5
AB0ED6,@UAL1470,2021/03/06 13:56:30,4925,1.8   # Keep - more than 5 mins from line 6
AB0ED6,@UAL1470,2021/03/06 13:56:40,4925,1.9   # Delete - less than than 5 mins from line 8
AB8C37,@AAL2386,2021/03/06 13:56:49,4500,1.0   # Delete - less than than 5 mins from line 5**

** Line 7 of the original record is not considered because it is slated for deletion

Ideally I'd like a solution that uses awk/sed/sort/uniq rather than recursively doing something like this:

while IFS= read -r line
do
   IFS=, read -ra record <<< "$line"
   # ... do a bunch of stuff
done < "inputfile.csv"

I tried this with awk but I got stuck quickly because of the complexity and potential recursion of the task.

Help? Pretty please?

4
  • 1
    So the line order is important - the first occurrence is never deleted, and when a line is deleted, it is not taken into account for all subsequent lines. Correct? Commented Mar 7, 2021 at 4:47
  • awk: An array with fields 1 and 3. For each line, check if the array contains occurrences of field 1 already and calculate the time differences. If any of the timestamps is less than 5 min apart, don't print the current line. Otherwise print it and store its fields in the array. Not quite trivial. The time difference function requires a little work, though not rocket science. Commented Mar 7, 2021 at 4:52
  • @berndtbausch both of your comments is what I'm looking, but somehow I'm stuck. Tnx
    – kx1t
    Commented Mar 7, 2021 at 19:47
  • Awk seems to be a better tool for this job. Which implementation of Awk are you using? Can we use Gawk extensions like mktime()? If you're not sure, you can find out by typing realpath "$(which awk)" into your Raspberry Pi's shell. If the result is /usr/bin/gawk or something like that, it's Gawk. Commented Mar 8, 2021 at 19:00

1 Answer 1

1

You can create a function in awk to get the difference in seconds between two dates, and then you just store the last 'valid' date in an awk array indexed by the first field, so you can use it in the comparison, for example:

awk '
function getDateDifference(a,b) {
    gsub(/[:/]/, " ", a)
    startDate = mktime(a)
    gsub(/[:/]/, " ", b)
    endDate = mktime(b)
    return int(endDate - startDate)   
}

BEGIN { FS=OFS="," } 

dates[$1]=="" || (dates[$1]!="" && getDateDifference(dates [$1],$3) > 300){
    print $0;
    dates[$1] = $3
}' input.txt

Please note that before doing the comparison between dates you have to check if the indexed array value exists for a specific first field, just to assure the first occurrences are printed.

1
  • thank you - this does exactly what I needed it to do. And my apologies for the slow answer, your contribution is appreciated
    – kx1t
    Commented Apr 19, 2021 at 20:40

You must log in to answer this question.

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