Conditional aggregation and print of a column in file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Conditional aggregation and print of a column in file
# 1  
Old 11-17-2009
Question Conditional aggregation and print of a column in file

Hi
My input file looks like
Code:
field1 field2 field3 field4 field5
field1 field2 field3 field4 field5
field1 field2 field3 field4 field5
::::::::::::
::::::::::::

There may be one space of multiple spaces between fields and no fields contains spaces in them.

If field 1 to 4 are equal for consecutive rows sum field 5 and continue this until any field is different.If any field in different, print field 1 to 4 and the sum.

I tried with following code and working perfectly:

Bu for input file of 70000 lines it takes 5 mins.
Can this be improved?? Please help

Code:
      first_rec=1
      while read line
      do
         if [[ $first_rec -eq 1 ]]
         then
            field1=`echo $line|tr -s " "|cut -d" " -f1`
            field2=`echo $line|tr -s " "|cut -d" " -f2`
            field3=`echo $line|tr -s " "|cut -d" " -f3`
            field4=`echo $line|tr -s " "|cut -d" " -f4`
            field5=0
            first_rec=0
         fi
         new_field1=`echo $line|tr -s " "|cut -d" " -f1`
         new_field2=`echo $line|tr -s " "|cut -d" " -f2`
         new_field3=`echo $line|tr -s " "|cut -d" " -f3`
         new_field4=`echo $line|tr -s " "|cut -d" " -f4`
         new_field5=`echo $line|tr -s " "|cut -d" " -f5`
         if [ fields 1 -4 are same ]
         then
            field5=`echo $field5 + $new_field5|bc` #Float field
         else
            echo "$field1$field2$field3$field4$field5" >> output file
            field5=$new_field5
         fi
         field1=$new_field1
         field2=$new_field2
         field3=$new_field3
         field4=$new_field4
      done < Input_file
      # for last record
      echo "$field1$field2$field3$field4$field5" >> output file

# 2  
Old 11-17-2009
Try:

Code:
awk '{ str=$1" "$2" "$3" "$4; if (arr[str]== "") arr[str]=$5; else arr[str]+=$5; } END { for (x in arr) print x" "arr[x]; }' file

input:
Quote:
12 123 213 213 342
12 123 13 213 342
12 23 213 213 342
12 123 213 213 342
12 123 213 213 342
12 23 213 213 342
12 23 213 213 342
output:
Quote:
12 23 213 213 1026
12 123 13 213 342
12 123 213 213 1026
# 3  
Old 11-17-2009
Hi dennis

Brilliant command..
Suppose if the input is fixed length like
Code:
field1<1 space>field2<3 spaces>field3<5spaces>field4<10 spaces>field5

where spaces can vary..
I need output in same format as input just the field 5 will change value.
I need output in format printf "%-20s%-10s%-10s%-10s%10s"

Please help
# 4  
Old 11-17-2009
Quote:
Originally Posted by bittoo
Hi dennis

Brilliant command..
Suppose if the input is fixed length like
Code:
field1<1 space>field2<3 spaces>field3<5spaces>field4<10 spaces>field5

where spaces can vary..
I need output in same format as input just the field 5 will change value.
I need output in format printf "%-20s%-10s%-10s%-10s%10s"

Please help
try this. But am concerned about the speed of execution now...

Code:
awk '{ str=$1" "$2" "$3" "$4; if (arr[str]== "") arr[str]=$5; else arr[str]+=$5; } END { for (x in arr) { str=x" "arr[x]"\n"; split(str,a," ");printf "%-20s%-10s%-10s%-10s%10s\n", a[1],a[2],a[3],a[4],a[5]; }}'  file

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Compare 1st column from 2 file and if match print line from 1st file and append column 7 from 2nd

hi I have 2 file with more than 10 columns for both 1st file apple,0,0,0...... orange,1,2,3..... mango,2,4,5..... 2nd file apple,2,3,4,5,6,7... orange,2,3,4,5,6,8... watermerlon,2,3,4,5,6,abc... mango,5,6,7,4,6,def.... (1 Reply)
Discussion started by: tententen
1 Replies

2. Shell Programming and Scripting

Conditional Column Value

Hi Folks, I'm trying tog ain further experience with shell programming and have set my a small goal of writing a little filesystem monitoring script. So far my output is as follows: PACMYDB03 Filesystem Size Used Avail Use% Status /usr/local/mysql/data ... (5 Replies)
Discussion started by: Axleuk
5 Replies

3. Shell Programming and Scripting

Filter on one column and then perform conditional calculations on another column with a Linux script

Hi, I have a file (stats.txt) with columns like in the example below. Destination IP address, timestamp, TCP packet sequence number and packet length. destIP time seqNo packetLength 1.2.3.4 0.01 123 500 1.2.3.5 0.03 44 1500 1.3.2.5 0.08 44 1500 1.2.3.4 0.44... (12 Replies)
Discussion started by: Zooma
12 Replies

4. Shell Programming and Scripting

Print the file into column

Hi All, Seeking for your assistance regarding on how to print multiple columns from file. Ex. nik.csv - input file 1,2,3,4,5,0054,May 1 11,12,13,14,15,0053,May 2 the 6th field is i converted it into decimal I create a variable on each field - pls see below. T1=`awk -F ","... (5 Replies)
Discussion started by: nikki1200
5 Replies

5. Shell Programming and Scripting

awk Print New Column For Every Two Lines and Match On Multiple Column Values to print another column

Hi, My input files is like this axis1 0 1 10 axis2 0 1 5 axis1 1 2 -4 axis2 2 3 -3 axis1 3 4 5 axis2 3 4 -1 axis1 4 5 -6 axis2 4 5 1 Now, these are my following tasks 1. Print a first column for every two rows that has the same value followed by a string. 2. Match on the... (3 Replies)
Discussion started by: jacobs.smith
3 Replies

6. Shell Programming and Scripting

Print a column with the name of the output file

I have n files and I am using cat to combine them in to one. Before that simply add the name of the output file to 4th column and then print the output. Is it possible ? input1 chr start end name 0 + key input2 chr start end name 0 + key inputn... (1 Reply)
Discussion started by: quincyjones
1 Replies

7. Shell Programming and Scripting

How to print file without first column

Hi, unix Gurus, I hit a problem, I want print out a file without print first column. for example: 123 abc cde ... 234 cde def ... I want to get as following: abc cde .. cde def .. :wall: anybody can help me out. Thanks in advance. (8 Replies)
Discussion started by: ken002
8 Replies

8. Shell Programming and Scripting

comparing column of two different files and print the column from in order of 2nd file

Hi friends, My file is like: Second file is : I need to print the rows present in file one, but in order present in second file....I used while read gh;do awk ' $1=="' $gh'" {print >> FILENAME"output"} ' cat listoffirstfile done < secondfile but the output I am... (14 Replies)
Discussion started by: CAch
14 Replies

9. Shell Programming and Scripting

Strings from one file which exactly match to the 1st column of other file and then print lines.

Hi, I have two files. 1st file has 1 column (huge file containing ~19200000 lines) and 2nd file has 2 columns (small file containing ~6000 lines). ################################# huge_file.txt a a ab b ################################## small_file.txt a 1.5 b 2.5 ab ... (4 Replies)
Discussion started by: AshwaniSharma09
4 Replies

10. Shell Programming and Scripting

Replace a column with a value conditional on a value in col1

Hi, Perhaps a rather simple problem...? I have data that looks like this. BPC0013 ANNUL_49610 0 0 1 1 BPC0014 ANNUL_49642 0 0 2 1 BPC0015 ANNUL_49580 0 0 1 1 BPC0016 ANNUL_49596 0 0 2 1 BPC0017 VULGO_49612 0 0 1 1 BPC0018 ANNUL_49628 0 0 1 1 BPC0019 ANNUL_49692 0 0 2 1 170291_HMG... (4 Replies)
Discussion started by: genehunter
4 Replies
Login or Register to Ask a Question