问题:

I have a set of .csv files with known number of columns that contains NA as entry as well as values.

I would like to get the maximum value of each row and then calculate the average of all the max values.

Input file:

"V1","V2","V3"
10,15,20
20,NA,5
NA,NA,NA
10,5,6

回答1:

Maximum values:

# Display 0 for NA rows
awk -F, 'NR>1{a=0;for(i=1;i<=NF;i++)if($i>a&&$i!="NA")a=$i;print a}' file
20
20
0
10

# Display nothing for NA rows 
awk -F, 'NR>1&&/[0-9]/{a=0;for(i=1;i<=NF;i++)if($i>a&&$i!="NA")a=$i;print a}' file
20
20
10

With average:

# Counting NA rows 
awk -F, 'NR>1{a=0;for(i=1;i<=NF;i++)if($i>a&&$i!="NA")a=$i;s+=a;print a}END{print s/(NR-1)}' file
20
20
0
10
12.5

# Ignoring NA rows
awk -F, 'NR>1&&/[0-9]/{a=0;for(i=1;i<=NF;i++)if($i>a&&$i!="NA")a=$i;s+=a;c++;print a}END{print s/c}' file
20
20
10
16.6667

回答2:

awk '{max=0;for(i=1;i<=NF;i++)if($i!~/NA/&&$i>max){max=$i;}count+=max;print max}END{print count/NR}' your_file

tested below:

>cat temp
10 15 20
20 NA 5
NA NA NA
10 5 6
> awk '{max=0;for(i=1;i<=NF;i++)if($i!~/NA/&&$i>max){max=$i;}count+=max;print max}END{print count/NR}' temp
20
20
0
10
12.5
>