I have several files like this
file A
Good Bad Fair
Strawberry 1 4 5
Banana 23 12 4
Plantain 0 0 1
Orange 0 0 0
file B
Strawberry 1 1 3
Banana 2 1 0
Plantain 0 0 0
Orange 0 0 0
file C
Strawberry 1 0 0
Banana 2 1 4
Plantain 5 0 7
Orange 0 7 0
Now I want to regroup the data in the following output format
OUTPUT FILE
GOOD
A B C
Strawberry 1 1 1
Banana 23 2 2
Plantain 0 0 5
Orange
BAD
A B C
Strawberry
Banana
Plantain
Orange
FAIR
A B C
Strawberry
Banana
Plantain
Orange
please anybody with an idea on how it can be achieved, while computing sum under each output file. The first column in in the input files is fixed for all.
Thanks.
Based on this example you should be able to figure out how to total the columns in the output files for yourself.
#set -vx
echo -n "Working..."
# # set variables
grades="Good Bad Fair"
files="A B C"
names="Strawberry Banana Plantain Orange"
# # process files
for grade in $grades; do
echo "$grade" > ${grade}.txt
echo "A B C" >> ${grade}.txt
for name in $names; do
typeset -i file_count=1
for file in $files; do
if [[ $grade = Good ]]; then
value=$(grep $name $file | cut -d" " -f2)
elif [[ $grade = Bad ]]; then
value=$(grep $name $file | cut -d" " -f3)
elif [[ $grade = Fair ]]; then
value=$(grep $name $file | cut -d" " -f4)
fi
if (( $file_count == 1 )); then
val1=$value
elif (( $file_count == 2 )); then
val2=$value
elif (( $file_count == 3 )); then
val3=$value
fi
typeset -i file_count=$(expr $file_count + 1)
done
echo "$name $val1 $val2 $val3" >> ${grade}.txt
done
done
# # sum columns in each file
for grade in $grades; do
echo ""
cat ${grade}.txt
done
Would you mind to explain how the BAD and the FAIR paragraphs should be populated? What's the logic?
based on variable grade="Good Bad Fair" you know which column to get the data from.
grade=Good
name=Strawberry
file=A
value=$(grep $name $file | cut -d" " -f2)
value=$(grep Strawberry A | cut -d" " -f2)
get line from file A where line contains text Strawberry then cut line into fields based on (-d" ") space character and get (-f2) field 2
Here is a solution in Perl which is more efficient than just using shell
#!/usr/bin/perl -w
# # set variables
@files="A B C";
# # open output files
open (GOOD, "> good.txt");
open (BAD, "> bad.txt");
open (FAIR, "> fair.txt");
# # for each file
while ($file = <@files>) {
# # open file
open (INPUT, "< $file");
# # process each line of file
while ($line = <INPUT>) {
chomp $line;
# # IF line contain strawberries
if ($line =~ m/Strawberry/) {
# # Split line into each value
($name,$good,$bad,$fair) = split (/ /, $line);
if ($file =~ m/A/) {
$sA_good=$good;
$sA_bad=$bad;
$sA_fair=$fair;
}
elsif ($file =~ m/B/) {
$sB_good=$good;
$sB_bad=$bad;
$sB_fair=$fair;
}
elsif ($file =~ m/C/) {
$sC_good=$good;
$sC_bad=$bad;
$sC_fair=$fair;
}
}
# # ELSE IF line contain bananas
elsif ($line =~ m/Banana/) {
# # Split line into each value
($name,$good,$bad,$fair) = split (/ /, $line);
if ($file =~ m/A/) {
$bA_good=$good;
$bA_bad=$bad;
$bA_fair=$fair;
}
elsif ($file =~ m/B/) {
$bB_good=$good;
$bB_bad=$bad;
$bB_fair=$fair;
}
elsif ($file =~ m/C/) {
$bC_good=$good;
$bC_bad=$bad;
$bC_fair=$fair;
}
}
# # ELSE IF line contain plantains
elsif ($line =~ m/Plantain/) {
# # Split line into each value
($name,$good,$bad,$fair) = split (/ /, $line);
if ($file =~ m/A/) {
$pA_good=$good;
$pA_bad=$bad;
$pA_fair=$fair;
}
elsif ($file =~ m/B/) {
$pB_good=$good;
$pB_bad=$bad;
$pB_fair=$fair;
}
elsif ($file =~ m/C/) {
$pC_good=$good;
$pC_bad=$bad;
$pC_fair=$fair;
}
}
# # ELSE IF line contain oranges
elsif ($line =~ m/Orange/) {
# # Split line into each value
($name,$good,$bad,$fair) = split (/ /, $line);
if ($file =~ m/A/) {
$oA_good=$good;
$oA_bad=$bad;
$oA_fair=$fair;
}
elsif ($file =~ m/B/) {
$oB_good=$good;
$oB_bad=$bad;
$oB_fair=$fair;
}
elsif ($file =~ m/C/) {
$oC_good=$good;
$oC_bad=$bad;
$oC_fair=$fair;
}
}
}
}
# # calc totals
$tgA=($sA_good + $bA_good + $pA_good + $oA_good);
$tgB=($sB_good + $bB_good + $pB_good + $oB_good);
$tgC=($sC_good + $bC_good + $pC_good + $oC_good);
$tbA=($sA_bad + $bA_bad + $pA_bad + $oA_bad);
$tbB=($sB_bad + $bB_bad + $pB_bad + $oB_bad);
$tbC=($sC_bad + $bC_bad + $pC_bad + $oC_bad);
$tfA=($sA_fair + $bA_fair + $pA_fair + $oA_fair);
$tfB=($sB_fair + $bB_fair + $pB_fair + $oB_fair);
$tfC=($sC_fair + $bC_fair + $pC_fair + $oC_fair);
# # print data to output files
print GOOD "GOOD\n";
print GOOD "A B C\n";
print GOOD "Strawberry\t$sA_good\t$sB_good\t$sC_good\n";
print GOOD "Banana \t$bA_good\t$bB_good\t$bC_good\n";
print GOOD "Plantain \t$pA_good\t$pB_good\t$pC_good\n";
print GOOD "Orange \t$oA_good\t$oB_good\t$oC_good\n";
print GOOD "===================================\n";
print GOOD "Totals \t$tgA\t$tgB\t$tgC\n";
print BAD "BAD\n";
print BAD "A B C\n";
print BAD "Strawberry\t$sA_bad\t$sB_bad\t$sC_bad\n";
print BAD "Banana \t$bA_bad\t$bB_bad\t$bC_bad\n";
print BAD "Plantain \t$pA_bad\t$pB_bad\t$pC_bad\n";
print BAD "Orange \t$oA_bad\t$oB_bad\t$oC_bad\n";
print BAD "===================================\n";
print BAD "Totals \t$tbA\t$tbB\t$tbC\n";
print FAIR "FAIR\n";
print FAIR "A B C\n";
print FAIR "Strawberry\t$sA_fair\t$sB_fair\t$sC_fair\n";
print FAIR "Banana \t$bA_fair\t$bB_fair\t$bC_fair\n";
print FAIR "Plantain \t$pA_fair\t$pB_fair\t$pC_fair\n";
print FAIR "Orange \t$oA_fair\t$oB_fair\t$oC_fair\n";
print FAIR "===================================\n";
print FAIR "Totals \t$tfA\t$tfB\t$tfC\n";
# # close files
close (GOOD);
close (BAD);
close (FAIR);
close (INPUT);
exit 0;
Thanks a lot ldapswandog, I think the perl codes should work for me. While I am still working on it. I will be very grateful if you have more useful hint on your codes. I quickly run the perl codes now I got Zero cloumn sum.
To be more clearly, what I need is to get the output like this:
(1)
first cloumn fixed and it can be more than 4 items(i.e
Strawberry
Banana
Plantain
Orange
(2)
2nd column will be fileA_column2
3nd cloumn will be fileB_column2
4th column will be fileC_column2
while the last row will be
TOTAL, sum_of_col_2, Sum_of_col3, sum_of_col_4
(3)
the output in 3 files( i.e Good Fair Bad)
Thanks a lot, I really appreciate your codes. I am still working on them.
I use perl codes, I got some output errors
with
output files good, fair, bad.txt with empty column contents except the fixed one
for good.txt I have:
GOOD
A B C
Strawberry
Banana
Plantain
Orange
Totals 0 0 0
look at the split function, it is using {space}, maybe your files are using {tab}. Once you get the split working the totals will populate. The script now has the mechanics you need. if you have altering conditions like more columns or more files you should be able to make the required changes. This is a learning exercise play with it and learn.
#!/usr/bin/perl
my (%hash,$first,$second,$third);
my @files=('a.txt','b.txt','c.txt');
sub l_sub{
my $file=$_[0];
open FH,"<$file";
while(<FH>){
chomp;
my @tmp=split;
if($.==1 and $file="a.txt"){
($first,$second,$third)=@tmp;
next;
}
$hash{$tmp[0]}->{POS}=$. if not exists $hash{$tmp[0]};
$hash{$tmp[0]}->{$first}.=$tmp[1]." ";
$hash{$tmp[0]}->{$second}.=$tmp[2]." ";
$hash{$tmp[0]}->{$third}.=$tmp[3]." ";
}
}
foreach $f (@files){
l_sub($f);
}
foreach my $ind ($first,$second,$third){
print $ind,"\n";
print join " ", @files;
print "\n";
foreach my $key (sort {$hash{$a}->{POS} <=> $hash{$b}->{POS}} keys %hash){
print $key," ",$hash{$key}->{$ind},"\n";
}
print "--------------\n";
}
output:
GOOD
a.txt b.txt c.txt
Strawberry 1 1 1
Banana 23 2 2
Plantain 0 0 5
Orange 0 0 0
--------------
BAD
a.txt b.txt c.txt
Strawberry 4 1 0
Banana 12 1 1
Plantain 0 0 0
Orange 0 0 7
--------------
FAIR
a.txt b.txt c.txt
Strawberry 5 3 0
Banana 4 0 4
Plantain 1 0 7
Orange 0 0 0
--------------
thanks a lot, the codes work well for the sample, I am still working on it for long list.
you should add Python
#!/usr/env/python
file1_data = open("file1").readlines()[1:]
file2_data = open("file2").readlines()
file3_data = open("file3").readlines()
good={};fair={};bad={}
for i in range(len(file2_data)):
file1_data = file1_data.split()
file2_data = file2_data.split()
file3_data = file3_data.split()
good.setdefault(file1_data[0],[])
fair.setdefault(file1_data[0],[])
bad.setdefault(file1_data[0],[])
good[file1_data[0]].append(file1_data[1])
good[file2_data[0]].append(file2_data[1])
good[file3_data[0]].append(file3_data[1])
fair[file1_data[0]].append(file1_data[2])
fair[file2_data[0]].append(file2_data[2])
fair[file3_data[0]].append(file3_data[2])
bad[file1_data[0]].append(file1_data[3])
bad[file2_data[0]].append(file2_data[3])
bad[file3_data[0]].append(file3_data[3])
print "Good"
print "*" * 100
for i,j in good.iteritems(): print i,','.join(j)
print "*" * 100
print "Fair"
for i,j in fair.iteritems(): print i,','.join(j)
print "*" * 100
print "Bad"
for i,j in bad.iteritems(): print i,','.join(j)
# ./test.py
Good
****************************************************************************************************
Orange 0,0,0
Strawberry 1,1,1
Banana 23,2,2
Plantain 0,0,5
****************************************************************************************************
Fair
Orange 0,0,7
Strawberry 4,1,0
Banana 12,1,1
Plantain 0,0,0
****************************************************************************************************
Bad
Orange 0,0,0
Strawberry 5,3,0
Banana 4,0,4
Plantain 1,0,7