Shell Programming and Scripting

City Temp
ABC    20
DEF    30
HIJ     15

City  Temp
ABC    22
DEF    29
KLM     5

City  Temp
 ABC    24
 DEF    27
 KLM     8

ABC  20,22,24
DEF  30,29,27
HIJ   15,,
KLM  ,5,8

join -t',' -j 2 -a1 -a2 -o 0 1.3 2.3 file1.csv file2.csv

$ 
$ 
$ cat file1.csv
Day 1
City Temp
ABC 20
DEF 30
HIJ 15
$ 
$ 
$ cat file2.csv
Day 2
City Temp
ABC 22
DEF 29
KLM 5
$ 
$ 
$ cat file3.csv
Day 3
City Temp
ABC 24
DEF 27
KLM 8
$ 
$ 
$ ls *.csv | sort | cat * | perl -lane '!/Day|City/ and $x{$F[0]}.=",".$F[1]; END{foreach $k (keys %x){print $k,"\t",substr($x{$k},1)}}'
ABC 20,22,24
DEF 30,29,27
HIJ 15
KLM 5,8
$ 
$

$
$
$ cat file1.csv
Day 1
City Temp
ABC    20
DEF    30
HIJ     15
$
$ cat file2.csv
Day 2
City  Temp
ABC    22
DEF    29
KLM     5
$
$ cat file3.csv
Day 3
City  Temp
 ABC    24
 DEF    27
 KLM     8
$
$ # show the contents of the Perl program
$ cat -n join_temps.pl
     1  #!perl -w
     2
     3  my $ptrn = "file*.csv";  # the pattern of text files that should be globbed
     4  my $maxday = 1;          # variable to store the maximum day number was encountered
     5  my $day;                 # day number in the current csv file that is being processed
     6  my %temps;               # the hash that stores the temp value for the key "cityNNN"
     7  my %cities;              # the hash to store city names
     8
     9  while (glob $ptrn) {
    10    # open the csv file and process it
    11    open(IN, $_) or die "Can't open file $_: $!";
    12    while (<IN>) {
    13      # trim newline and whitespaces
    14      chomp;
    15      s/^\s*//g;
    16      s/\s*$//g;
    17      # capture the day number as a 3 digit number; set $maxday if needed
    18      # otherwise, set the key=>value pair in the hash %temps
    19      # key = City1NNN, value = temperature; NNN = 3 digit day e.g. 001,098,123 etc.
    20      if (/^Day (\d+)/) {$maxday = $1 if $1 > $maxday; $day = sprintf("%03d",$1)}
    21      elsif (!/^City/) {@x = split/[ ]+/; $cities{$x[0]}=1; $temps{$x[0].$day}=$x[1]}
    22    }
    23    close(IN) or die "Can't close file $_: $!";
    24  }
    25
    26  # now start printing the output data in the form: City<=TAB=>temp1,temp2,temp3,...
    27  foreach $k (sort keys %cities){
    28    print $k,"\t";
    29    foreach $i (1..$maxday) {
    30      $n = sprintf("%03d",$i);
    31      $temp = defined $temps{$k.$n} ? $temps{$k.$n} : "";
    32      # you can code this if your Perl version is 5.10 or higher => $temp = $temps{$k.$n} // "";
    33      print $i == 1 ? $temp : ",$temp";
    34    }
    35    print "\n";
    36  }
$
$ # run the Perl program
$ perl join_temps.pl
ABC     20,22,24
DEF     30,29,27
HIJ     15,,
KLM     ,5,8
$
$
$