使用awk插入具有日期和时间的数据文件中的数据列 [英] Using awk to interpolate data column based in a data file with date and time

问题描述

以下文件具有多列，其中包含日期，时间和不完整的数据集，如使用一个简单文件所示

The following file has multiple columns with date, time and incomplete data set as shown using a simple file

# Matrix.txt
13.09.2016:23:44:10;;4.0
13.09.2016:23:44:20;10.0;
13.09.2016:23:44:30;;
13.09.2016:23:44:40;30.0;7.0

如何使用awk在每列上进行线性插值以获取丢失的数据:

How can I do an linear interpolation on each column using awk to get the missing data:

# Output.txt
13.09.2016:23:44:10;0.0;4.0
13.09.2016:23:44:20;10.0;5.0
13.09.2016:23:44:30;20.0;6.0
13.09.2016:23:44:40;30.0;7.0

推荐答案

这是Gnu awk中的一种解决方案.它针对第一个给定的数据文件运行两次，记住第一个和最后一个数据点( y ₁ ，y ₂ )及其时间戳( x ₂ ，x ₂ )，计算点的斜率( k =(y ₂ -y ₁ )/(x ₂ -x ₁ ))并为空元素((y =(x ₁ -x)+ y ₁ ).

Here is one solution in Gnu awk. It runs twice for the first given data file, remembers first and last data points (y₁, y₂) and their timestamps (x₂, x₂), computes slopes of the points (k=(y₂-y₁)/(x₂-x₁)) and inter- and extrapolates values for empty elements ((y=(x₁-x)+y₁).

这不是万无一失的证明，它不会检查是否被零除或是否有两个斜率点或任何其他检查.

It's not fool proof, it doesn't check for division by zeroes or if there are two points for the slopes or any other checks whatsoever.

$ cat inexpolator.awk
BEGIN {
    FS=OFS=";"
    ARGC=3; ARGV[2]=ARGV[1]        # run it twice for first file
}
BEGINFILE {                        # on the second round
        for(i in p)                # compute the slopes
            k[i]=(y2[i]-y1[i])/(x2[i]-x1[i])
}
{
    split($1,a,"[:.]")             # reformat the timestamp
    ts=mktime(a[3] " " a[2] " " a[1] " " a[4] " " a[5] " " a[6])
}
NR==FNR {                          # remember first and last points for slopes
    for(i=2;i<=NF;i++) {
        p[i]
        if(y1[i]=="") { y1[i]=$i; x1[i]=ts }
        if($i!="") { y2[i]=$i; x2[i]=ts }
    }
    next                           # only on the first round
}
{                                  # reformat ts again for output
    printf "%s", strftime("%d.%m.%Y:%H:%M:%S",ts) OFS  # print ts
    for(i=2;i<=NF;i++) {
        if($i=="") $i=k[i]*(ts-x1[i])+y1[i]            # compute missing points
        printf "%.1f%s", $i, (i<NF?OFS:ORS)            # print points
    }
}

运行它:

$ awk -f inexpolator.awk Matrix.txt
13.09.2016:23:44:10;0.0;4.0
13.09.2016:23:44:20;10.0;5.0
13.09.2016:23:44:30;20.0;6.0
13.09.2016:23:44:40;30.0;7.0

这篇关于使用awk插入具有日期和时间的数据文件中的数据列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！