使用awk移调CSV数据（透视变换） [英] Transpose CSV data with awk (pivot transformation)

问题描述

我的CSV数据是这样的：

my CSV data looks like this:

Indicator;Country;Value
no_of_people;USA;500
no_of_people;Germany;300
no_of_people;France;200
area_in_km;USA;18
area_in_km;Germany;16
area_in_km;France;17
proportion_males;USA;5.3
proportion_males;Germany;7.9
proportion_males;France;2.4

我希望我的数据是这样的：

I want my data to look like this:

Country;no_of_people;area_in_km;proportion_males
USA;500;18;5.3
Germany;300;16;7.9
France;200;17;2.4

有更多的指标和更多的国家在这里列出。

There are more Indicators and more countries than listed here.

pretty大文件（用5位数字的行东西）。
环顾四周，一些转线程，但没有符合我的情况（也我是很新的awk的，所以我不能改变code，我发现适合我的数据）。

Pretty large files (number of rows something with 5 digits). Looked around for some transpose threads, but nothing matched my situation (also I'm quite new to awk, so I couldn't change the code I found to fit my data).

感谢您的帮助。
问候
广告

Thanks for your help. Regards Ad

推荐答案

使用 AWK 并保持输出的顺序：

Using awk and maintaining the order of output:

awk -F\; '
NR>1 { 
    if(!($1 in indicators)) { indicator[++types] = $1 }; indicators[$1]++  
    if(!($2 in countries)) { country[++num] = $2 }; countries[$2]++
    map[$1,$2] = $3 
}
END {
    printf "%s;" ,"Country";
    for(ind=1; ind<=types; ind++) {
        printf "%s%s", sep, indicator[ind]; 
        sep = ";"
    }
    print "";
    for(coun=1; coun<=num; coun++) {
        printf "%s", country[coun]
        for(val=1; val<=types; val++) {
            printf "%s%s", sep, map[indicator[val], country[coun]];
        }
        print ""
    }
}' file
Country;no_of_people;area_in_km;proportion_males
USA;500;18;5.3
Germany;300;16;7.9
France;200;17;2.4

这篇关于使用awk移调CSV数据（透视变换）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！