where the common identifier is in column 2 (-j2) and the value i want to amend my master file with is always in column 3 (-o 1.3 2.3). I'm trying to avoid running this command over and over again, as the -o option keeps on growing (-o 1.3 1.4 2.3 --> -0 1.3 1.4 1.5 2.3 --> -o 1.3 1.4 1.5 1.6 2.3 --> -o 1.3 1.4 1.5...1.n 2.3, where n=~200)
Is there a way to use while read or something else to make the file?
Here's an idea using Perl, assuming that the file contents are in the same order as the file names i.e. file1.csv has data for day 1, file2.csv has data for day 2, and so on...
$
$
$ 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
$
$
Not sure if this is what you wanted. Note that putting all those commas after "HIJ" means that
(a) either the list of cities is hardcoded/known/present in a separate file, or
(b) a separate walk through of all files is done first to get such a list, and then the hash is built
Consider what happens if "HIJ" is absent from files 1 through 199, and present in file # 200. You'd have to have a hash entry with 199 commas on the left.
The city list could also be determined by a single parse of all files, but the program for that would be much more elaborate, I think.
Thanks, but the problem is that the list of the cities changes from file to file, so they are not in the same order every time. Also this method makes it seem like KLM had temperatures of 5 and 8 on days 1 and 2, when the temps were actually observed on days 2 and 3, which is why I have to have commas in there. The file is a csv, so when i read it into a spreadsheet there will be empty cells at times when a temp was not updated for a particular city.
I thought so.
Here's a more elaborate program that should take care of those issues. The script comments should be self-explanatory.
$
$
$ 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
$
$
$
HTH,
tyler_durden
NB - You may want to change the tab "\t" at line 28 to comma "," for a proper csv file.