CODEWITHSUNDEEP
linuxbashdateawksed
sandra16
sandra16

Reputation: 73

Transform an entire column using "date" command

Here is a dummy CSV file with 3 rows. The actual file has 7 million rows.

testdates.csv:

y_m_d
1997-01-01
1985-06-09
1943-07-14

The date tool can usually be formatted as such , to get the 'day' :

date -d "25 JUN 2011" +%A

=> output: Saturday

Query: How to provide an entire column as input for the date +%A transformation?

The resulting output should be appended to the end of the input file.

Intended output:

y_m_d, Day
1997-01-01, Thursday
1985-06-09, Sunday
1943-07-14, Tuesday

Upvotes: 1

Views: 238

Answers (3)

glenn jackman
glenn jackman

Reputation: 247042

Hard to beat that date answer.

GNU awk would be OK too:

gawk -v OFS=', ' '
    NR == 1 {$2 = "Day"}
    NR >  1 {$2 = strftime("%A", mktime(gensub(/-/, " ", "g", $1) " 0 0 0"))}
    1
' testdates.csv

y_m_d, Day
1997-01-01, Wednesday
1985-06-09, Sunday
1943-07-14, Wednesday

Or perl:

perl -MTime::Piece -lne '
    print "$_, ", $. == 1
                    ? "Day"
                    : Time::Piece->strptime($_, "%Y-%m-%d")->strftime("%A")
' testdates.csv

Upvotes: 4

Benjamin W.
Benjamin W.

Reputation: 52441

To read multiples dates from a file using GNU date, you can use the -f/--file option:

$ date -f testdates.csv '+%F, %A'
1997-01-01, Wednesday
1985-06-09, Sunday
1943-07-14, Wednesday

Since your file has a header row, we have to skip that, for example using process substitution and sed:

date -f <(sed '1d' testdates.csv) '+%F, %A'

To get your desired output, combine like this:

echo 'y_m_d, Day'
date -f <(sed '1d' testdates.csv) '+%F, %A'

or write to a new file:

{
    echo 'y_m_d, Day'
    date -f <(sed '1d' testdates.csv) '+%F, %A'
} > testdates.csv.tmp

and after inspection, you can rename with

mv testdates.csv.tmp testdates.csv

Upvotes: 6

Sean Bright
Sean Bright

Reputation: 120704

#/bin/bash

while read datespec; do
  echo $datespec, $(date -d "$datespec" +%A)
done < testdates.csv

Output:

1997-01-01, Wednesday
1985-06-09, Sunday
1943-07-14, Wednesday

Upvotes: 2

Related Questions