how to get values from first index of array in shell scripting?

Question

I have below code and I want to get daily_balue, monthly_value, priorday_value, priorweek_value, priormonth_value from log file. As per the code the daily_value, monthly value, priorday value, prior week and priormonth value are stored in first index of array.

How to get daily,monthly value priority prior week and prior month values from first index of array ?

sql file as shown below:

first input file columns order:

cash , monthly_value, null,null, 0.000
cash, daily_value,2022-02-22,2022-02-22,3
cash, priormonth_value,2022-02-22,2022-02,8
cash , priorday_value, null,null, 10
cash, priorweek_value,2022-02-22,2022-02-22,32
loans, monthly_value, 2022-02-22,2022-02-22, 21.000
loans, daily_value,2022-02-22,2022-02-22,4
loans,priormonth_value,2022-02-22,2022-02,9
loans , priorday_value, null,null, 12
loans,priorweek_value,2022-02-22,2022-02-22,23

Output for first input should be stored in the below format in a csv file format : $date, $row, $daily_balue, $monthly_value, $priorday_value, $priorweek_value, priormonth_value

2022-02-22, cash, 3, 0.00, 10, 32, 8
2022-02-22, loans, 4, 21.00, 12, 23, 9

In some case the priormonth_values might be missing, so need to add zero in the output file second input file columns order:

cash , monthly_value, null,null, 0.000
cash, daily_value,2022-02-22,2022-02-22,3
cash , priorday_value, null,null, 10
cash, priorweek_value,2022-02-22,2022-02-22,32
loans, monthly_value, 2022-02-22,2022-02-22, 21.000
loans, daily_value,2022-02-22,2022-02-22,4
loans , priorday_value, null,null, 12
loans,priorweek_value,2022-02-22,2022-02-22,23

Output for second input should be stored in the below format in a csv file format : $date, $row, $daily_balue, $monthly_vale, $priorday_value, $priorweek_value, priormonth_value

2022-02-22, cash, 3, 0.00, 10, 32, 0
2022-02-22, loans, 4, 21.00, 12, 23, 0

third input file columns order . here daily_value and monthly_value are missing from the file

cash, priormonth_value,2022-02-22,2022-02,8
cash , priorday_value, null,null, 10
cash, priorweek_value,2022-02-22,2022-02-22,32
loans,priormonth_value,2022-02-22,2022-02,9
loans , priorday_value, null,null, 12
loans, priorweek_value,2022-02-22, 2022-02-02,11

Output for third input should be stored in the below format in a csv file. daily_value and monthly_value are assigned 0 format : $date, $row, $daily_balue, $monthly_vale, $priorday_value, $priorweek_value, priormonth_value

2022-02-22, cash, 0, 0, 10, 32, 8
2022-02-22, loans, 0, 0, 12, 11, 9

declare -A arr


if [ -s $sqlfile ]
then 
  while IFS=, read key value; do
          arr[$key] = "${arr[$key]}$arr[$key]:+,}$value"
  done < $sqlfile

       i=0
       for key in "${!arr[@]}"; do
       row=`echo $key | xargs`
       values = ${arr[$key]}

       daily_value = `echo $values | cut -d ',' -f8 | xargs`
       priomonth_value = `echo $value | cut -d ',' -f12 | xargs`

       i = $((i+1))
       done
else
       echo "$sqlfile is empty"
fi

exit 0

Answer 1

You could use gawk (GNU's version of awk) to do this. The sporadic spaces around , in the input could be dealt with by using *, * as the field separator (FS).

In some cases, there is null where the date should be, so I add the value in the date array with the value in the null array. Since only one of them is set, the sum will be the correct value.

This also solves the case if neither the date nor the null array contains a value. blank + blank becomes 0.

#!/usr/bin/gawk -f

BEGIN {
  FS=" *, *";
  OFS=", ";
}
{
  # [date][row][value_type] = value
  arr[$3][$1][$2] = $5;
}
END {
  cl[0] = "cash";
  cl[1] = "loans";
  for(date in arr) {
    if(date == "null") continue;
    for(i = 0; i < 2; ++i) {
      row = cl[i];
      print date, row,
        arr[date][row]["daily_value"]     +arr["null"][row]["daily_value"],
        arr[date][row]["monthly_value"]   +arr["null"][row]["monthly_value"],
        arr[date][row]["priorday_value"]  +arr["null"][row]["priorday_value"],
        arr[date][row]["priorweek_value"] +arr["null"][row]["priorweek_value"],
        arr[date][row]["priormonth_value"]+arr["null"][row]["priormonth_value"];
    }
  }
}

Output

It's not exactly what you've listed as your expected output. When you expect 21.00, it'll be just 21 here. If .00 is important it can be fixed by using printf("%.2f", the_value); instead of just printing the result of the addition above.

File 1:

2022-02-22, cash, 3, 0, 10, 32, 8
2022-02-22, loans, 4, 21, 12, 23, 9

File 2:

2022-02-22, cash, 3, 0, 10, 32, 0
2022-02-22, loans, 4, 21, 12, 23, 0

File 3:

2022-02-22, cash, 0, 0, 10, 32, 8
2022-02-22, loans, 0, 0, 12, 11, 9

---

---

A version with a scale of 2:

#!/usr/bin/gawk -f

BEGIN {
  FS=" *, *";
}
{
  # [date][row][value_type] = value
  arr[$3][$1][$2] = $5;
}
END {
  cl[0] = "cash";
  cl[1] = "loans";
  vt[0] = "daily_value";
  vt[1] = "monthly_value";
  vt[2] = "priorday_value";
  vt[3] = "priorweek_value";
  vt[4] = "priormonth_value";
  for(date in arr) {
    if(date == "null") continue;
    for(i = 0; i < 2; ++i) {
      row = cl[i];
      printf("%s, %s", date, row);
      for(j = 0; j < 5; ++j) {
        vtype=vt[j];
        printf(", %.2f", arr[date][row][vtype] + arr["null"][row][vtype]);
      }
      printf("\n");
    }
  }
}

Output

File 1:

2022-02-22, cash, 3.00, 0.00, 10.00, 32.00, 8.00
2022-02-22, loans, 4.00, 21.00, 12.00, 23.00, 9.00

File 2:

2022-02-22, cash, 3.00, 0.00, 10.00, 32.00, 0.00
2022-02-22, loans, 4.00, 21.00, 12.00, 23.00, 0.00

File 3:

2022-02-22, cash, 0.00, 0.00, 10.00, 32.00, 8.00
2022-02-22, loans, 0.00, 0.00, 12.00, 11.00, 9.00

Answer 2

Given:

$ head file{1..3}
==> file1 <==
cash , monthy_value, null,null, 0.000
cash,daily_value,2022-02-22,2022-02-22,3
cash,priormonth_value,20shelll22-02-22,2022-02,8

==> file2 <==
cash,priormonth_value,2022-02-22,2022-02,8
cash , monthy_value, null,null, 0.000
cash,daily_value,2022-02-22,2022-02-22,3

==> file3 <==
cash , monthy_value, null,null, 0.000
cash,daily_value,2022-02-22,2022-02-22,3

I personally would use a combination of awk and Bash like so:

for fn in file{1..3}; do 
    declare -A arr
    
    while read k v; do 
        arr["$k"]="$v"
    done <<< $(awk -F, -v keys='daily_value priormonth_value' '
    BEGIN{split(keys,tmp,"[ \t]+"); for (e in tmp) tgt[tmp[e]]}
    $2 in tgt {
        gsub(/\s*,\s*/,",")
        printf("%s %s\n",$2,$(NF))
    }' "$fn")
    echo "$fn"
    declare -p arr
    unset arr 
    echo 
done 

Prints:

file1
declare -A arr=([daily_value]="3" [priormonth_value]="8" )

file2
declare -A arr=([daily_value]="3" [priormonth_value]="8" )

file3
declare -A arr=([daily_value]="3" )