Insert rows with computations of next row

Shell Programming and Scripting
1

Hello folks,
I have data collected in every 3 hours. But, I would like to expand this to 1 hour interval by equally dividing with next row.
For example, I want to keep the first value 1987-01-01-00z 2.0, but following all record should be re-written as follow.
1987-01-01-03z 5.0 becomes 1.66667 after dividing by 3, then two new records 1987-01-01-01z 1.66667 and 1987-01-01-02z 1.66667 should be inserted, and continue...
Also, all precision should be same.
Thanks in advance.

Jae

<input>
1987-01-01-00Z 2.0
1987-01-01-03Z 5.0
1987-01-01-06Z 10.0
1987-01-01-09Z 120.0
1987-01-01-12Z 10
1987-01-01-15Z 8
1987-01-01-18Z 28.302
1987-01-01-21Z 282.566
1987-01-02-00Z 0
1987-01-02-03Z 2.0

<output>
1987-01-01-00Z 2.00000
1987-01-01-01Z 1.66667
1987-01-01-02Z 1.66667
1987-01-01-03Z 1.66667
1987-01-01-04Z 3.33333
1987-01-01-05Z 3.33333
1987-01-01-06Z 3.33333
1987-01-01-07Z 40.0000
1987-01-01-08Z 40.0000
1987-01-01-09Z 40.0000
1987-01-01-10Z 3.33333
1987-01-01-11Z 3.33333
1987-01-01-12Z 3.33333
1987-01-01-13Z 2.66667
1987-01-01-14Z 2.66667
1987-01-01-15Z 2.66667
1987-01-01-16Z 9.43400
1987-01-01-17Z 9.43400
1987-01-01-18Z 9.43400
1987-01-01-19Z 94.1887
1987-01-01-20Z 94.1887
1987-01-01-21Z 94.1887
1987-01-01-22Z 0.00000
1987-01-01-23Z 0.00000
1987-01-02-00Z 0.00000
1987-01-02-01Z 0.66667
1987-01-02-02Z 0.66667
1987-01-02-03Z 0.66667

2

Wow, they certainly do have creative word-problems at Devry nowadays. :slight_smile:

I think you want to use awk. It can read each line, separate the fields, do the division, and print with the desired precision. Should be easy.

3

Yes, I know awk would be the solution. But, my trial doesn't work.
Any input would be appreciated. I am noting to do with Devry.

awk '
BEGIN {
FS="-|" "";
}
{
if (NR==1) print $0
else {
getline record;
n = split( record, arr, " ")
print $1"-"$2"-"$3"-"$4-2" "arr[2]/3;
print $1"-"$2"-"$3"-"$4-1" "arr[2]/3;
print $1"-"$2"-"$3"-"$4;
}

    \}
' inputfile
4

This should do the job:

awk '
NR==1{printf("%s %.5f\n", $1,$2); next}
dat!=substr($1,1,10){dat=substr($1,1,10);cnt=1}
{
  for(i=1;i<=3;i++) {
    printf "%s%02dZ %."6-length(int($2/3))"f\n",substr($1,1,11),cnt,$2/3
    cnt++
  }
}
' inputfile

Regards

5

Thanks Franklin,

I almost got it.
but, the result after 1987-01-01-21Z using your script is not what I want (look at the hour increment).
These are:
1987-01-01-21Z 94.1887
1987-01-02-01Z 0.00000
1987-01-02-02Z 0.00000
1987-01-02-03Z 0.00000
1987-01-02-04Z 0.66667
1987-01-02-05Z 0.66667
1987-01-02-06Z 0.66667

Should be:
1987-01-01-21Z 94.1887
1987-01-01-22Z 0.00000
1987-01-01-23Z 0.00000
1987-01-02-00Z 0.00000
1987-01-02-01Z 0.66667
1987-01-02-02Z 0.66667
1987-01-02-03Z 0.66667

6

Thanks Franklin,

I almost got it.
but, the result after 1987-01-01-21Z using your script is not what I want (look at the hour increment).
These are:
1987-01-01-21Z 94.1887
1987-01-02-01Z 0.00000
1987-01-02-02Z 0.00000
1987-01-02-03Z 0.00000
1987-01-02-04Z 0.66667
1987-01-02-05Z 0.66667
1987-01-02-06Z 0.66667

Should be:
1987-01-01-21Z 94.1887
1987-01-01-22Z 0.00000
1987-01-01-23Z 0.00000
1987-01-02-00Z 0.00000
1987-01-02-01Z 0.66667
1987-01-02-02Z 0.66667
1987-01-02-03Z 0.66667

7

Check your input and output careful, if the output is wrong, I can't understand the logic.

Regards

8

Hi,

code:

nawk 'BEGIN{format="%s-%02dz %6f";n=0}
{
temp=substr($1,12,2)
t=substr($1,1,10)
if (temp=="00")
{
	vin=sprintf(format,t,temp,$2)
	out[n]=vin
	n++
}
else
{	
	num=$2/3
	vin=sprintf(format,t,temp-2,num)
	out[n]=vin
	n++
	vin=sprintf(format,t,temp-1,num)
	out[n]=vin
	n++
	vin=sprintf(format,t,temp,num)
	out[n]=vin
	n++
}
}
END{
for (i=0;i<=n;i++)
print out
}' filename
9

Thanks summer_cherry;
It is very closer, but I am not able to get the right answer using your script, either. Two time steps are missing in the output.
1987-01-01-22Z and 1987-01-01-23Z.
I think the script should have special care of midnight (00Z) in order to make 22Z and 23Z by subtracting 2 and 1, respectively.
For example,
2 hour time lag of 1987-02-01-00Z should be 1987-01-01-22Z, and
1 hour time lag of 1987-02-01-00Z should be 1987-01-01-23Z.

I appreciate your kind help.

Jae

10

It should be a challenge for you to figure it out yourself with the given answers.:wink:
Anyhow, I think I understand the problem.
I'm not able to test it out at this moment but try this:

awk '
NR==1{printf("%s %.5f\n", $1,$2); next}
dat!=substr($1,1,10) && substr($1,12,2 != "00"){dat=substr($1,1,10);cnt=1}
{
  for(i=1;i<=3;i++) {
    if(cnt==24){cnt=0}
    printf "%s%02dZ %."6-length(int($2/3))"f\n",substr($1,1,11),cnt,$2/3
    cnt++
  }
}
' inputfile

Regards

11

FYI,
Based on previous scripts, I revised a little bit to make it work.
Again, thanks summer_cherry and Franklin.

awk 'BEGIN{format="%s-%02dz %12.6f"; n=0}
{
temp=substr($1,12,2)
t=substr($1,1,10)
if (temp=="00" && NR == 1)
{
vin=sprintf(format,t,temp,$2)
out[n]=vin
n++
}
else if (temp=="00" && NR > 1)
{
num=$2/3
vin=sprintf(format,t,22,num)
out[n]=vin
n++
vin=sprintf(format,t,23,num)
out[n]=vin
n++
vin=sprintf(format,t,temp,num)
out[n]=vin
n++
}
else
{
num=$2/3
vin=sprintf(format,t,temp-2,num)
out[n]=vin
n++
vin=sprintf(format,t,temp-1,num)
out[n]=vin
n++
vin=sprintf(format,t,temp,num)
out[n]=vin
n++
}
}
END{
for (i=0;i<=n;i++)
print out
[i]}' inputfile

12

With this solution you don't get the format as you requested, try my last script.

Regards