1

I'm having some trouble creating a bash/awk/sed script that would take a comma-separated CSV file of three columns (firstname, lastname, date of birth), and outputs another CSV file that has the same columns from input with an additional column that shows the difference between the current date and the date of birth in years.

$ yourscript <input CSV file> <output CSV file>

input.csv may look like this:

bob,wag,06/13/1958
ashley,hay,01/23/1983
evan,bert,09/11/1972

output.csv should look like this:

bob,wag,06/13/1958,62
ashley,hay,01/23/1983,37
evan,bert,09/11/1972,48
3
$ cat data
bob,wag,06/13/1958
ashley,hay,01/23/1983
evan,bert,09/11/1972

To output in a file named output-file and display to STDOUT at the same time:

$ awk -v year=$(\date +%Y) 'BEGIN{FS="/"} {print $0 "," year-$3}' data | tee output-file
bob,wag,06/13/1958,62
ashley,hay,01/23/1983,37
evan,bert,09/11/1972,48

Or to just output to the same file:

$ awk -v year=$(\date +%Y) 'BEGIN{FS="/"} {print $0 "," year-$3}' data > output-file
9
  • ... and it could be done in other ways, e.g. perl. Generally forget sed for arithmetics. – Cbhihe Sep 28 '20 at 9:08
  • Thank you for this! I would need to append this data as a new column in an output CSV file, how could I do that in the command line format I mentioned after the $ sign? – Phillip Sep 28 '20 at 13:32
  • @Phillip: for output redirection, just do ` > output-file` at the end f the one liner above. See edit. – Cbhihe Sep 28 '20 at 13:51
  • 1
    @Cbhihe You can actually incorporate calculation of the "current year" into the awk call via the strftime() function: awk 'BEGIN{year=strftime("%Y"); FS="/"} ..... '. – AdminBee Sep 28 '20 at 15:21
  • 2
    I can't let the sed arithmetic comment go without mentioning unix.stackexchange.com/a/36959/117549 – Jeff Schaller Oct 2 '20 at 0:26
2

To perform more accurate time computations, you can use gawk's time and string functions (per @AdminBee's suggestion). Using input data as:

$ cat data
bob,wag,06/13/1958
ashley,hay,01/23/1983
evan,bert,09/11/1972

You can get a time difference in days between now and the date shown on each line, with:

$ awk -F, 'BEGIN{today=systime()} 
           {print $0 "," int((today-mktime(substr($3,7,4)" "substr($3,1,2)" "substr($3,4,2)" "00" "00" "00))/(3600*24))}' \
           data | tee output-file

 bob,wag,06/13/1958,22755
 ashley,hay,01/23/1983,13765
 evan,bert,09/11/1972,17551

The snippet:

int((today-mktime(substr($3,7,4)" "substr($3,1,2)" "substr($3,4,2)" "00" "00" "00))/(3600*24))

does three basic things for each line of the input file:

  • it calculates the time elapsed (in seconds) since 1970-01-01 00:00:00 UTC (on POSIX systems), not counting leap seconds, with mktime(substr($3,7,4)" "substr($3,1,2)" "substr($3,4,2)" "00" "00" "00)
  • it calculates the time difference between the above quantity and the variable today, which contains the number of seconds elapsed at time of execution since 1970-01-01 00:00:00 UTC.
  • it divides the time difference in seconds by 3600*24 to get the same in days, and only consider the integer part of the result, to get whole days with int().

You could play with that to get your time difference in seconds, minutes, hours per your need. HTH

0

To get the date difference, you can use this small bash function

Use following numbers for your needs

  • #To get Days difference: 86400
  • #To get Years difference: 31536000

Bash Function

datediff() {
  current_date=$(date -d "$1" +%s)
  birth_date=$(date -d "$2" +%s)
  echo $(( (current_date - birth_date) / 31536000)) Years
}

Usage:

datediff '9/28/2020' '1/1/1999'

Output:

 21 years
1
  • Thank you for this, I'm just not sure how to add a new column to the output CSV file. – Phillip Sep 28 '20 at 4:58
0

Depending on what you really want:

years difference:

(echo "firstname,lastname,d"; cat input.csv) | csv-sqlite \
 "select *, strftime('%Y', 'now') - substr(d, 7, 4) as year_diff
  from input" | csv-header --remove

age:

(echo "firstname,lastname,d"; cat input.csv) | csv-sqlite
  "select *, strftime('%Y', 'now') - substr(d, 7, 4) -
             case when strftime('%m%d', 'now') >= (substr(d, 1, 2) || substr(d, 4, 2))
             then 0 else 1 end as age
  from input" | csv-header --remove

csv-sqlite and csv-header are 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.