Replace first column of file by command output

awksedtext processing

From this question copy and replace column using unix

I have tried to build a solution which works only from this file:

20070101 10.2317  79.1638   6.0  26.7  20.9   0.8  14.0  98.6
20070102 10.2317  79.1638   5.6  26.5  20.8   1.9  13.6  98.0
20070103 10.2317  79.1638   7.5  27.7  20.8   0.1  15.8  96.4
20070104 10.2317  79.1638   8.1  26.0  19.6   0.0  15.5  94.1

And get the output:

01/01/2007  10.2317   79.1638   6.0  26.7  20.9   0.8  14.0  98.6
02/01/2007  10.2317   79.1638   5.6  26.5  20.8   1.9  13.6  98.0
03/01/2007  10.2317   79.1638   7.5  27.7  20.8   0.1  15.8  96.4
04/01/2007  10.2317   79.1638   8.1  26.0  19.6   0.0  15.5  94.1

Without other files involved.

So I want to substitute the first column by the converted date.

I got the date from the original file with:

$ awk '{print $1}' filedate.txt
20070101
20070102
20070103
20070104

Then I did the date conversion with:

for i in $(awk '{print $1}' filedate.txt); do date -d "$i"  +%d/%m/%Y; done
01/01/2007
02/01/2007
03/01/2007
04/01/2007

But I haven't been able to modify the first column of the file by the date values already converted. I tried with awk substitution (awk '{$1=$dt}1'):

for i in $(awk '{print $1}' filedate.txt); do dt=$(date -d "$i" +%d/%m/%Y) && awk '{$1=$dt}1' filedate.txt; done

But as there is a loop involved, the output is not the desired one.

How can I achieve this with awk? Is it possible to do the same with sed?

EDIT

In the comments of the other question I saw the following way with sed

 sed 's,^\([0-9]\{4\}\)\([0-9]\{2\}\)\([0-9]\{2\}\),\3/\2/\1,'

But I'm now curious about how to do it involving the date command.

Best Answer

Let's use your already existing loop (which I haven't really looked at but which seems to do its job):

for i in $(awk '{print $1}' filedate.txt); do date -d "$i"  +%d/%m/%Y; done

And then tuck my answer to that other question onto it, with slight modification:

for i in $(awk '{print $1}' filedate.txt); do date -d "$i"  +%d/%m/%Y; done |
paste - <( cut -d ' ' -f 2- filedate.txt )

Result:

01/01/2007      10.2317  79.1638   6.0  26.7  20.9   0.8  14.0  98.6
02/01/2007      10.2317  79.1638   5.6  26.5  20.8   1.9  13.6  98.0
03/01/2007      10.2317  79.1638   7.5  27.7  20.8   0.1  15.8  96.4
04/01/2007      10.2317  79.1638   8.1  26.0  19.6   0.0  15.5  94.1

Shorter, without the loop:

date -f <( cut -d ' ' -f 1 filedate.txt ) +"%d/%m/%Y" |
paste - <( cut -d ' ' -f 2- filedate.txt )

Without the pipe:

paste <( date -f <( cut -d ' ' -f 1 filedate.txt ) +"%d/%m/%Y" ) \
      <( cut -d ' ' -f 2- filedate.txt )

All of these examples obviously requires either bash or ksh or any other shell that understands process substitutions. GNU date is also required.

See my answer to that other question for an explanation of how this works.

Related Solutions

Bash – Working with columns – awk and sed

Just use awk directly:

awk '/\/g/ {
        gsub(/\./, "", $2)
        gsub(/../, "&:", $2)
        sub(/:$/, "", $2) 
        print $2,$3
}'

With this solution you don't need grep nor sed.

Print only lines where the first column is unique

awk normally reads each line of the input and invokes the script on it. The cases where you would use getline are few and far between. When your script is run with six lines of input, this is an overview of what happens:

Read line 1 normally

Set variables
Call getline, which reads line 2
Compare variables

Read line 3 normally

Set variables
Call getline, which reads line 4
Compare variables

Read line 5 normally

Set variables
Call getline, which reads line 6
Compare variables

Obviously this isn’t going to work.
Secondly, you made a common mistake in your awk code. In awk, fields from the input are referenced as $number and variables are referenced as variable_name. This is different from shell scripts, where command line arguments are referenced as $number and variables are referenced as $variable_name. Your test
```
if ($1 != $path)
```
should be
```
if ($1 != path)
```
Your overall approach is flawed. You can’t identify strings that occur only once in the file by looking at two lines at a time. I believe that you can do it by looking at three lines at a time (i.e., by keeping the two previous lines in variables), but things like that get complicated and messy. It’s probably simpler to count occurrences. Here’s a minimal modification on your script to do that.
```
awk '{
  if ($1 != path) {
    if (count == 1) {
      print prev
    }
    count=1
  }
  else count++
  prev=$0; path=$1
}
END {
    if (count == 1) {
      print prev
    }
}'
```
I deleted type, since you never used it.

Disclosure: This is essentially the same as the last part of glenn’s answer.

EDIT

Best Answer

Related Solutions

Bash – Working with columns – awk and sed

Print only lines where the first column is unique

Related Question