☰

如何将多组列转置为行

tag-icon • tag-icon text-processing
如何将多组列转置为行

我们可以将多组值转置为 Linux 中的行吗？

以下是我拥有的示例数据..

UID,UNAME,UKEY,UFROMDATE,UTODATE,UL,ULID,UF,UFID
"1","abc","12344321","2019-01-01","2019-01-10","1","1A","X1","XA"
"2","abc","12344322","2019-02-01","2019-02-10","2","2A","X2","XB"

我们可以将上面的数据转换成下面的格式吗

UID,UNAME,UKEY,UFROMDATE,UTODATE,ColVal,ID,ColName
"1","abc","12344321","2019-01-01","2019-01-10","1","1A",UL
"1","abc","12344321","2019-01-01","2019-01-10","X1","XA",UF
"2","abc","12344322","2019-02-01","2019-02-10","2","2A",UL
"2","abc","12344322","2019-02-01","2019-02-10","X2","XB",UF

请帮我任何建议..

答案1

假设引用的字符串中没有任何逗号，我们可以使用 awk 将字段分隔符设置为逗号。然后只需手动打印标题行，并为每行打印第 1 至 7 列，然后打印 1-5,8-9：

$ awk -F, -vOFS=, 'NR == 1 {print "UID,UNAME,UKEY,UFROMDATE,UTODATE,ColVal,ID,ColName"}; 
    NR != 1 {print $1, $2, $3, $4, $5, $6, $7, "UL"; 
             print $1, $2, $3, $4, $5, $8, $9, "UF"; }' < file.txt
UID,UNAME,UKEY,UFROMDATE,UTODATE,ColVal,ID,ColName
"1","abc","12344321","2019-01-01","2019-01-10","1","1A",UL
"1","abc","12344321","2019-01-01","2019-01-10","X1","XA",UF
"2","abc","12344322","2019-02-01","2019-02-10","2","2A",UL
"2","abc","12344322","2019-02-01","2019-02-10","X2","XB",UF

答案2

sed 的一些更脏的东西

$ echo "UID,UNAME,UKEY,UFROMDATE,UTODATE,ColVal,ID,ColName" && sed -E '1d; s|"([0-9]+",.+),("[0-9]","[0-9][A-Z]"),("X[0-9]","X[A-Z]")$|\1,\2,UL\n\1,\3,UF|' file.txt

结果

UID,UNAME,UKEY,UFROMDATE,UTODATE,ColVal,ID,ColName
1","abc","12344321","2019-01-01","2019-01-10","1","1A",UL
1","abc","12344321","2019-01-01","2019-01-10","X1","XA",UF
2","abc","12344322","2019-02-01","2019-02-10","2","2A",UL
2","abc","12344322","2019-02-01","2019-02-10","X2","XB",UF

相关内容