Finding index number of a column in a csv file, but command doesnt show result?

user_14181529 0
Viewed 227

I want to get the index number of a CSV column, depending on the column name. For example I have these columns: TotalReported;Deceased;City I tried doing this:

head -n1 data.csv | tr ";" "\n" | grep -nx "Deceased" | cut -d";" -f1

but nothing is being printed. I also tried doing this:

awk -F, '{ for(i=1; i<=NF; i++) if($i=="Deceased") {print i} exit}' data.csv'

which doesn't print anything. What can I do?

This is the content of the data.csv file:

Date_of_report;Municipality_code;Municipality_name;Province;Total_reported;Hospital_admission;Deceased
2020-03-13 10:00:00;GM0003;Appingedam;Groningen;0;0;0
2020-03-13 10:00:00;GM0010;Delfzijl;Groningen;0;0;0
2020-03-13 10:00:00;GM0014;Groningen;Groningen;3;0;0
2020-03-13 10:00:00;GM0024;Loppersum;Groningen;0;0;0
2020-03-13 10:00:00;GM0034;Almere;Flevoland;1;1;0
2020-03-13 10:00:00;GM0037;Stadskanaal;Groningen;0;0;0
2020-03-13 10:00:00;GM0047;Veendam;Groningen;0;0;0
2020-03-13 10:00:00;GM0050;Zeewolde;Flevoland;1;0;0
2020-03-13 10:00:00;GM0059;Achtkarspelen;Friesland;0;0;0
2020-03-13 10:00:00;GM0060;Ameland;Friesland;0;0;0
2020-03-13 10:00:00;GM0072;Harlingen;Friesland;0;0;0
2020-03-13 10:00:00;GM0074;Heerenveen;Friesland;0;0;0

Basically I want to get the index number of a column, and then put the index number in this awk command

v1=$(awk -F";" '{x+=$index}END{print x}' ./data.csv)

and get the sum of all the numbers of that specific column. The last three columns are important since you can only sum those of course.

csv indexing awk shell bash
4 Answers

Use egrep -o to get the start of the line and count the semicolon:

echo 'TotalReported;Deceased;City' | egrep -o '.*Deceased' | sed -e 's/[^;]//g' | wc -c

The output is 2 because Deceased is in the second column.

user_42924710

Could you please try following, written and tested with shown samples only.

awk '
BEGIN{
  FS=";"
}
{ 
  gsub(/\r/,"")
  for(i=1;i<=NF;i++){
     if($i=="Deceased"){
        print "Index is:"i
        exit
     }
  }
}
' Input_file

OR in case your string could be anything then you could create an awk variable named strSearch and could assign its value as per your string which you want to look for its index.

awk -v strSearch="Deceased" '
BEGIN{
  FS=";"
}
{
  gsub(/\r/,"")
  for(i=1;i<=NF;i++){
     if($i==strSearch){
        print "Index is:"i
        exit
     }
  }
}
' Input_file

Output will be as follows.

Index is:7
user_58665800

echo "Date_of_report;Municipality_code;Municipality_name;Province;Total_reported;Hospital_admission;Deceased" | awk -F';' '{ for(i=1; i<=NF; i++) if($i=="Deceased") {print i} exit}'

This prints '7'. Note semicolon, not comma.

Admin1

Note: Use @TheSlater version, it is more concise and better.

But as I was about to paste my shell script:

if [ $# -ne 2 ];
then
    echo "Usage: $0 <file_name> <column_to_find>"
    exit 255
fi
I=1
FILE_NAME=$1
GOAL_COL=$2
head -1 $FILE_NAME > TMP
current_col=`cut -d";" -f$I TMP`
while [ ! -z $current_col ] && [ $current_col != $GOAL_COL ]
do
    ((I++))
    current_col=`cut -d";" -f$I TMP`
done

if [ -z $current_col ];
then 
    echo "NOTFOUND"
else
    echo "$I"
fi
rm TMP
exit 0

Just in case anyone finds it useful

EDIT:

$ sh findIndex.sh th.csv Deceased
7
user_27296050
Related