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
    

5 Answers 5

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
  • 1
    ... and it could be done in other ways, e.g. perl. Generally forget sed for arithmetics.
    – Cbhihe
    Sep 28, 2020 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, 2020 at 13:32
  • @Phillip: for output redirection, just do ` > output-file` at the end f the one liner above. See edit.
    – Cbhihe
    Sep 28, 2020 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, 2020 at 15:21
  • 3
    I can't let the sed arithmetic comment go without mentioning unix.stackexchange.com/a/36959/117549
    – Jeff Schaller
    Oct 2, 2020 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

1

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, 2020 at 4:58
1

Using Raku (formerly known as Perl_6)

perl6 -MText::CSV -e 'my @a = csv( in => $*IN ); my $date = Date.today;  \
       @a>>.[2] = @a.map: *.[2].subst(/ (\d**2) \/ (\d**2) \/ (\d**4) /, {"$2-$0-$1"} ).Date;  \
       @a>>.[3] = @a.map: (($date - *.[2])/365.2425).floor;  \
       csv( in => @a, out => $*OUT );' < file.txt

Sample Input (file.txt):

bob,wag,06/13/1958
ashley,hay,01/23/1983
evan,bert,09/11/1972
maggie,simpson,05/09/2021
lisa,simpson,05/09/2014
bart,simpson,05/09/2012

Sample Output:

bob,wag,1958-06-13,63
ashley,hay,1983-01-23,39
evan,bert,1972-09-11,49
maggie,simpson,2021-05-09,1
lisa,simpson,2014-05-09,8
bart,simpson,2012-05-09,10

The code above returns ISO 8601 dates, and uses ISO 8601 dates to compute years (floored). Briefly, the .csv file is read in and parsed by the csv(…) function, made available by loading the Text::CSV module at the command line. The data is read into array @a, and today's date is recorded as $date.

In the second line of code, subst is used to analyze the third column and return the date-of-birth in yyyy-mm-dd format, which is then converted to an ISO 8601 date by calling .Date on that string. In the third line of code, each ISO 8601 date-of-birth is subtracted from $date today's date, floored, and returned as an extra column. In the last line of code, valid .csv is output.

If mm/dd/yyyy dates are required in the output (same as input but NOT ISO 8601), then reformat the ISO 8601 Date with the following command, just before the csv(in => @a, out => $*OUT) last command:

@a>>.[2] = @a.map: *.[2].mm-dd-yyyy("/");

NOTE: "age-at-date" column is only approximate. The ISO 8601 dates when subtracted will return accurate days, but these are inaccurately converted to years by dividing by 365.2425 days/year and flooring.

[The right way to calculate "age-at-date" is to calculate the number of leap-days a person has lived, e.g. using Raku's is-leap-year function. It's not clear any answer yet posted has accurately addressed this issue--including mine].

https://en.wikipedia.org/wiki/ISO_8601
https://docs.raku.org/language/temporal#index-entry-Date_and_time_functions
https://raku.org

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

You must log in to answer this question.

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