linux - Linux CSV数据比较

Question

我是 Linux 新手，我尝试操作 bash 文件中的一些数据。我尝试了很多解决方案但没有成功。我有三个条件让我迷失在太多的命令中:

1. 比较 xx:xx:xx:xx:xx(第 3 列)是否已存在于文件中

2. 如果已找到，请比较包含相同 xx:xx:xx:xx:xx 的所有行的时间(第 1 列)

3. 如果时间相同，则比较信号强度(第 2 列)并发回具有最低值的线路。

数据文件(csv):

Mar 6 2014 17h29h43, -55, xx:xx:xx:xx:xx (This line has to be removed)
Mar 6 2014 17h29h43, -38, xx:xx:xx:xx:xx
Mar 6 2014 17h29h44, -60, yy:yy:yy:yy:yy

希望的结果:

Mar 6 2014 17h29h43, -38, xx:xx:xx:xx:xx (=> lowest value for xx:xx:xx:xx 17h29h43)
Mar 6 2014 17h29h44, -60, yy:yy:yy:yy:yy

Answer 1

你可能应该在 perl/python 中这样做，但我很无聊，所以为什么不呢:

#!/usr/bin/env bash

filename=$1

#read input file and store into associative array
declare -A arr
while IFS=',' read date value string ; do
    #Change time from 10h00h00 to 10:00:00
    dt=$(sed s'/\([0-9]\+\)h/\1:/g' <<< "$date")
    #Get rid of leading spaces
    string=$(tr -d ' ' <<< "$string")
    value=$(tr -d ' ' <<< "$value")
    #Convert datetime to unix epoch
    epoch=$(date --date="$dt" +%s)
    #echo "$epoch,$string"

    #Create unique key for array
    key="$epoch,$string"

    #Check if key already exists if so compare
    #values and keep the highest    
    ov=-10000
    [ ${arr["$key"]+abc} ] && ov=${arr["$key"]}

    if [[ "$value" -gt "$ov" ]]; then 
        arr["$key"]="$value"
    fi
done < "$filename"

#Display output, sort on epoch and then remove from output
for i in ${!arr[@]}; do
    #Split key into epoch and string
    epoch=$(awk -F, '{print $1}' <<< $i)
    string=$(awk -F, '{print $2}' <<< $i)

    #Convert date back to long format
    dt=$(date -d@"$epoch" '+%h %-d %Y %Hh%Mh%S')

    echo "$epoch $dt, ${arr[$i]} $string"
done | sort -h | sed 's/^[0-9]\+ //'

输入文件(数据文件):

Mar 6 2014 17h29h43, -55, xx:xx:xx:xx:xx
Mar 6 2014 17h29h43, -38, xx:xx:xx:xx:xx
Mar 6 2014 17h29h44, -60, yy:yy:yy:yy:yy
Mar 9 2014 07h29h44, -6, 11:22:33:44:55
Mar 9 2014 07h29h44,  6, 22:33:44:55:66
Mar 9 2014 07h29h44,  3, 22:33:44:55:66
Mar 1 2014 14h33h04, -60, anythingreally

输出:

Mar 1 2014 14h33h04, -60 anythingreally
Mar 6 2014 17h29h43, -38 xx:xx:xx:xx:xx
Mar 6 2014 17h29h44, -60 yy:yy:yy:yy:yy
Mar 9 2014 07h29h44, -6 11:22:33:44:55
Mar 9 2014 07h29h44, 6 22:33:44:55:66