Pivot multiple

Dear friend,

I want to sum popul based on ville and reg.

input

date    country    ville    reg    popul
20131101    INDIA    Gujarat    College    322047286
20131101    USA    Oregon 2    Kindergaten    477305599
20131101    INDIA    Delhi 1    Ecole    255029428
20131101    MEXICO    Ardu 2    Unidentified    12700
20131101    MEXICO    Ardu 2    Kindergaten    382870479
20131101    INDIA    Delhi 2    Kindergaten    280004848
20131101    USA    Oregon 2    Unidentified    9400
20131102    USA    Auckland    College    149720282
20131102    MEXICO    Ardu 1    Student & Employee    18925553
20131102    INDIA    Delhi 2    Unidentified    15925
20131102    USA    Oregon 1    Student & Employee    14236005
20131102    USA    Auckland    Kindergaten    374236693
20131102    MEXICO    Ardu 1    Ecole    226592538
20131102    INDIA    Gujarat    Student & Employee    7976589
20131102    USA    Oregon 1    Ecole    151688356
20131103    USA    Auckland    Unidentified    7375
20131103    MEXICO    Stemp    College    122930128
20131103    USA    Auckland    Kindergaten    592258933
20131103    USA    Auckland    College    133411400
20131103    USA    Virginia    Student & Employee    7799909
20131103    MEXICO    Stemp    Kindergaten    611243392
20131103    USA    Virginia    Unidentified    10575
20131104    INDIA    Gujarat    College    358369384
20131104    INDIA    Gujarat    Ecole    67330897
20131104    USA    Virginia    Ecole    84923771
20131104    MEXICO    Stemp    Unidentified    29400
20131104    USA    Virginia    Student & Employee    21741745
20131105    INDIA    Delhi 1    Kindergaten    617543929
20131105    USA    Oregon 2    Kindergaten    365362849
20131105    USA    Oregon 2    College    168136515
20131105    MEXICO    Stemp    Student & Employee    18429693
20131105    USA    Oregon 2    Ecole    187090109
20131105    INDIA    Delhi 1    Unidentified    38300

Expected output

ville    College    Ecole    Kindergaten    Student & Employee    Unidentified
Ardu 1    0    226592538    0    18925553    0
Ardu 2    0    0    382870479    0    12700
Auckland    283131682    0    966495626    0    7375
Delhi 1    0    255029428    617543929    0    38300
Delhi 2    0    0    280004848    0    15925
Gujarat    680416670    67330897    0    7976589    0
Oregon 1    0    151688356    0    14236005    0
Oregon 2    168136515    187090109    842668448    0    9400
Stemp    122930128    0    611243392    18429693    29400
Virginia    0    84923771    0    29541654    10575

Try

awk     'NR==1  {next}
         {for (i=1; i<=LnCnt; i++) if ($3 == Ln) break; if (i > LnCnt) Ln[++LnCnt]=$3}
         {for (j=1; j<=HdCnt; j++) if ($4 == Hd[j]) break; if (j > HdCnt) Hd[++HdCnt]=$4}
         {Mx[$3,$4] += $5}
         END {printf "%15s", ""; for (j=1; j<=HdCnt; j++)  printf "%15s", Hd[j]; printf "\n";
              for (i=1; i<=LnCnt; i++)  {printf "%15s", Ln;
                                         for (j=1; j<=HdCnt; j++) printf "%015s", Mx[Ln, Hd[j]];
                                         printf "\n"
                                        }
             }
        ' FS="\t" file
                       College    Kindergaten          Ecole   UnidentifiedStudent & Employee
        Gujarat      358369384                      67330897                       7976589
       Oregon 2      168136515      365362849      187090109           9400
        Delhi 1                     617543929      255029428          38300
         Ardu 2                     382870479                         12700
        Delhi 2                     280004848                         15925
       Auckland      133411400      592258933                          7375
         Ardu 1                                    226592538                      18925553
       Oregon 1                                    151688356                      14236005
          Stemp      122930128      611243392                         29400       18429693
       Virginia                                     84923771          10575       21741745

Make very sure that columns are TAB separated and fields contain just spaces, if at all.

A pipelined approach, very old school, is to sort your input by ville and then reg, so you can add up each reg for a ville and spit out the sum when the ville changes. To start the loop, you need a last_ville variable set to a safe value or unset. To end the loop, you need to add in a dummy line at EOF (which does not need to be sorted).

#!/bin/bash
 
unset last_ville
 
IFS='\t
'
 
echo "ville\tCollege\tEcole\tKindergaten\tStudent & Employee\tUnidentified"
 
(
sort -t'\t' -k3,5 in_file
echo "x\tx\tx\tdummyXX
)|while read date country ville reg popul
do
 if [ "$ville" != "$last_ville" ]
 then
  if [ "$last_ville" != "" ]
  then
   echo "$last_ville\t$coll_sum\t$ecole_sum\t$kinder_sum\t$se_sum\t$unid_sum"
  fi
  if [ "$ville" = "dummyXX" ]
  then
   break
  fi
  last_ville="$ville" coll_sum=0 ecole_sum=0 kinder_sum=0 se_sum=0 unid_sum=0
 fi
 case "$reg" in
 (College)
  (( coll_sum += popul ))
  ;;
 (Ecole)
 .
 .
 .
 (*) #default are unidentified
  (( unid_sum += popul ))
  ;;
 esac
done

I am assuming the fields are tab separated, so the embedded blanks do not set off the sort field identification. When is say \t, I mean a real tab character. If you "typeset -A pop_sum" and use the $ref as $pop_sum's key, it can hold all the sums, but then emptying it is very chatty. You might destroy it and recreate it, but then "" is not a zero, so you need to find a way to add the zero's for "" when printing. Tab separated text is very spreadsheet friendly.