How can I assign each column value to Its name?

Question

I have a MetaData.csv file that contains many values to perform an analysis. All I want are: 1- Reading column names and making variables similar to column names. 2- Put values in each column into variables as an integer that can be read by other commands. column_name=Its_value

MetaData.csv:

MAF,HWE,Geno_Missing,Inds_Missing
0.05,1E-06,0.01,0.01

I wrote the following codes but it doesn't work well:

#!/bin/bash
Col_Names=$(head -n 1 MetaData.csv) # Cut header (camma sep)
Col_Names=$(echo ${Col_Names//,/ }) # Convert header to space sep
Col_Names=($Col_Names) # Convert header to an array 

for i in $(seq 1 ${#Col_Names[@]}); do
N="$(head -1 MetaData.csv | tr ',' '\n' | nl |grep -w 
"${Col_Names[$i]}" | tr -d " " | awk -F " " '{print $1}')";
${Col_Names[$i]}="$(cat MetaData.csv | cut -d"," -f$N | sed '1d')";
done

Output:

HWE=1E-06: command not found
Geno_Missing=0.01: command not found
Inds_Missing=0.01: command not found
cut: 2: No such file or directory
cut: 3: No such file or directory
cut: 4: No such file or directory
=: command not found

Expected output:

MAF=0.05
HWE=1E-06
Geno_Missing=0.01
Inds_Missing=0.01

Problems:

1- I want to use array length (${#Col_Names[@]}) as the final iteration which is 5, but the array index start from 0 (0-4). So MAF column was not captured by the loop. Loop also iterate twice (once 0-4 and again 2-4!). 2- When I tried to call values in variables (echo $MAF), they were empty!

Any solution is really appreciated.

Answer 1

This produces the expected output you posted from the sample input you posted:

$ awk -F, -v OFS='=' 'NR==1{split($0,hdr); next} {for (i=1;i<=NF;i++) print hdr[i], $i}' MetaData.csv
MAF=0.05
HWE=1E-06
Geno_Missing=0.01
Inds_Missing=0.01

If that's not all you need then edit your question to clarify your requirements.

Answer 2

I don't really think you can implement a robust CSV reader/parser in Bash, but you can implement it to work to some extent with simple CSV files. For example, a very simply bash-implemented CSV might look like this:

#!/bin/bash

set -e

ROW_NUMBER='0'
HEADERS=()
while IFS=',' read -ra ROW; do
    if test "$ROW_NUMBER" == '0'; then
        for (( I = 0; I < ${#ROW[@]}; I++ )); do
            HEADERS["$I"]="${ROW[I]}"
        done
    else
        declare -A DATA_ROW_MAP
        for (( I = 0; I < ${#ROW[@]}; I++ )); do
            DATA_ROW_MAP[${HEADERS["$I"]}]="${ROW[I]}"
        done
# DEMO {
        echo -e "${DATA_ROW_MAP['Fnames']}\t${DATA_ROW_MAP['Inds_Missing']}"
# } DEMO
        unset DATA_ROW_MAP
    fi
    ROW_NUMBER=$((ROW_NUMBER + 1))
done

Note that is has multiple disadvantages:

• it only works with ,-separated fields (truly "C"SV);
• it cannot handle multiline records;
• it cannot handle field escapes;
• it considers the first row always represents a header row.

This is why many commands may produce and consume \0-delimited data just because this control character may be easier to use. Now what I'm not sure about is whether test is the only external command executed by bash (I believe it is, but it can be probably re-implemented using case so that no external test is executed?).

Example of use (with the demo output):

./read-csv.sh < MetaData.csv

19.vcf.gz    0.01
20.vcf.gz
21.vcf.gz
22.vcf.gz

I wouldn't recommend using this parser at all, but would recommend using a more CSV-oriented tool (Python would probably be the easiest choice to use; + or if your favorite language, as you mentioned, is R, then probably this is another option for you: Run R script from command line ).

Answer 3

If I'm understanding your requirements correctly, would you please try something like:

#!/bin/bash

nr=1                                    # initialize input line number to 1
while IFS=, read -r -a ary; do          # split the line on "," then assign "ary" to the fields
    if (( nr == 1 )); then              # handle the header line
        col_names=("${ary[@]}")         # assign column names
    else                                # handle the body lines
        for (( i = 0; i < ${#ary[@]}; i++ )); do
            printf -v "${col_names[i]}" "${ary[i]}"
                                        # assign the variable "${col_names[i]}" to the input field
        done
        # now you can access the values via its column name
        echo "Fnames=$Fnames"
        echo "MAF=$MAF"
        fname_list+=("$Fnames")         # create a list of Fnames
    fi
    (( nr++ ))                          # increment the input line number
done < MetaData.csv
echo "${fname_list[@]}"                 # print the list of Fnames

Output:

Fnames=19.vcf.gz
MAF=0.05
Fnames=20.vcf.gz
MAF=
Fnames=21.vcf.gz
MAF=
Fnames=22.vcf.gz
MAF=
19.vcf.gz 20.vcf.gz 21.vcf.gz 22.vcf.gz

• The statetemt IFS=, read -a ary is mostly equivalent to your first three lines; it splits the input on ",", and assigns the array variable ary to the field values.
• There are several ways to use a variable's value as a variable name (Indirect Variable References). printf -v VarName Value is one of them.

[EDIT]

Based on the OP's updated input file, here is an another version:

#!/bin/bash

nr=1                                    # initialize input line number to 1
while IFS=, read -r -a ary; do          # split the line on "," then assign "ary" to the fields
    if (( nr == 1 )); then              # handle the header line
        col_names=("${ary[@]}")         # assign column names
    else                                # handle the body lines
        for (( i = 0; i < ${#ary[@]}; i++ )); do
            printf -v "${col_names[i]}" "${ary[i]}"
                                        # assign the variable "${col_names[i]}" to the input field
        done
    fi
    (( nr++ ))                          # increment the input line number
done < MetaData.csv

for n in "${col_names[@]}"; do          # iterate over the variable names
    echo "$n=${!n}"                     # print variable name and its value
done

# you can also specify the variable names literally as follows:
echo "MAF=$MAF HWE=$HWE Geno_Missing=$Geno_Missing Inds_Missing=$Inds_Missing"

Output:

MAF=0.05
HWE=1E-06
Geno_Missing=0.01
Inds_Missing=0.01
MAF=0.05 HWE=1E-06 Geno_Missing=0.01 Inds_Missing=0.01

As for the output, the first four lines are printed by echo "$n=${!n}" and the last line is printed by echo "MAF=$MAF .... You can choose either statement depending on your usage of the variables in the following code.