Formatting file with Awk?

Login or Register to Ask a Question and Join Our Community

Formatting file with Awk?

Tags

advanced, awk, file

Login to Discuss or Reply to this Discussion in Our Community

Top Forums UNIX for Advanced & Expert Users Formatting file with Awk?

08-12-2008

Registered User

18, 0

Join Date: Jun 2008

Last Activity: 25 March 2009, 11:51 AM EDT

Posts: 18

Thanks Given: 0

Thanked 0 Times in 0 Posts

Formatting file with Awk?

I have a file in CSV format (2 columns ID and Number of Items):

AB1 ,,10
AB2 ,,20
AB2 ,, 30
AB3 ,, 10
AB4 ,, 20
AB4 ,, 30
AB4 ,, 40
AB5 ,, 50
AB6 ,, 10
AB7 ,, 20
AB7 ,, 30
AB7 ,, 40
......

This file is produced daily i would like to get it in the following format, so all duplicates codes are summed together:

AB1 ,,10
AB2 ,,50
AB3 ,, 10
AB4 ,, 90
AB5 ,, 50
AB6 ,, 10
AB7 ,, 90
......

I believe some use of awk -F will be required, could some assist me please

SAMZ

View Public Profile for SAMZ

Find all posts by SAMZ

08-12-2008

Registered User

594, 29

Join Date: Jun 2003

Last Activity: 19 May 2015, 6:39 AM EDT

Location: Scotland

Posts: 594

Thanks Given: 1

Thanked 29 Times in 27 Posts

Code:

#  awk '{FS=",";t[$1]+=$3}END{for (i in t){print i ",, " t[i]}}' file1
AB1 ,, 10
AB2 ,, 50
AB3 ,, 10
AB4 ,, 90
AB5 ,, 50
AB6 ,, 10
AB7 ,, 90

Tytalus

View Public Profile for Tytalus

Find all posts by Tytalus

08-12-2008

Registered User

18, 0

Join Date: Jun 2008

Last Activity: 25 March 2009, 11:51 AM EDT

Posts: 18

Thanks Given: 0

Thanked 0 Times in 0 Posts

Thanks, that works a treat. Could someone please explain in detail as to what is being done here for (i in t){print i ",, " t[i].

# awk '{FS=",";t[$1]+=$3}END{for (i in t){print i ",, " t[i]}}' file1

I understand that the awk is reading each line from file1 and that the columns are serated by ',' character.
I think the t[$1] with the {for (i in t){print i ",, " t[i]} is somehow grouping together all the same codes and corresponding values and +=$3 is totalling them, ut would like a better clarification of how the ode is actually doing this

SAMZ

View Public Profile for SAMZ

Find all posts by SAMZ

08-13-2008

Registered User

18, 0

Join Date: Jun 2008

Last Activity: 25 March 2009, 11:51 AM EDT

Posts: 18

Thanks Given: 0

Thanked 0 Times in 0 Posts

bump!!

edit by bakunin: Sorry to put it so bluntly, but i think it is utter gall to "bump" a thread (where you have already gotten a sufficient answer, as you yourself admitted) after 11(!) hours. 11 hours - this is the time passed between this post and your last one.

Apart from the fact that bumping up threads is against the rules - who do you think we are? Do you think we have nothing better to do than to sit and wait if you want have something explained? We are NOT your first level support, we have NO obligation to help you at all, let alone within a certain time frame.

Last edited by bakunin; 08-13-2008 at 12:56 PM..

SAMZ

View Public Profile for SAMZ

Find all posts by SAMZ

08-13-2008

Registered User

594, 29

Join Date: Jun 2003

Last Activity: 19 May 2015, 6:39 AM EDT

Location: Scotland

Posts: 594

Thanks Given: 1

Thanked 29 Times in 27 Posts

Code:

awk '{FS=",";   ### cols are seperated by commas.

t[$1]+=$3} ## set up an array t where each element is referenced by $1, and add the value of $3 to the relevant $1 element

END  ### after we've looped over all lines.. 

{for (i in t) ### for every entry in t i.e. all the elements labelled with the 1 entries


{print i ",, " t[i]}}' file1 ### print out the label, two commas, then the contents of the labelled element.

Tytalus

View Public Profile for Tytalus

Find all posts by Tytalus

08-13-2008

Registered User

18, 0

Join Date: Jun 2008

Last Activity: 25 March 2009, 11:51 AM EDT

Posts: 18

Thanks Given: 0

Thanked 0 Times in 0 Posts

thanks

SAMZ

View Public Profile for SAMZ

Find all posts by SAMZ

08-13-2008

Registered User

112, 0

Join Date: May 2008

Last Activity: 2 October 2016, 7:44 AM EDT

Location: Jordan

Posts: 112

Thanks Given: 6

Thanked 0 Times in 0 Posts

Please try this..

nawk 'NR==1{FS="," ;v=$1;Total=0}$1!=v{print v",," Total} ;Total=0; k=0 ;v=$1}
$1==v{Total=Total+$3}' input > Total

Regards

yahyaaa

View Public Profile for yahyaaa

Find all posts by yahyaaa

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

File formatting with awk

Hi, I would like to format the file input example to the specific output example. I have tried numerous different ways, however not able to extract the information as desired. Any assistance to get the file formatted would be truly appreciated: Input: server:<server...

2. UNIX for Dummies Questions & Answers

Help with awk'ing formatting this

Hi, I have a file below that I am wanting to awk. The lines of relevance are lines 7 and 9 $ nl /tmp/x 1 ADRCI: Release 11.2.0.3.0 - Production on Sun Jun 23 17:01:02 2013 2 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 3 ADR base =...

3. Shell Programming and Scripting

AWK/Shell script for formatting data in a file

Hi All, Need an urgent help to convert a unix file in to a particular format: **source file:** 1111111 2d2f2h2 3dfgsd3 ........... 1111111 <-- repeats in every nth line. remaining all lines will be different 123ss41 432ff45 ........... 1111111 <-- repetition qwe1234 123weq3...

4. UNIX for Dummies Questions & Answers

awk formatting

Hi all, I'm writing a simple awk code: awk 'BEGIN {FS="|"};{print "Type\tNumber\ttypes\tTotal";};{print $1, "\t", $2, "\t", $3, "\t", $4, "\t";}' db_query.txt it gives me the result: Type Number types Total XXX 498.0 5100.0 5274.661 Type Number types Total...

5. Shell Programming and Scripting

formatting awk

when i try this awk its giving out put as below. awk '!(/^$/||/--/||/selected/||/^ *$/){print $1}' tmp.txt output ===== 1 2010-08-03-12.31.26.126000 how excluede the 1st line ? i mean i want output only 2nd line i.e 2010-08-03-12.31.26.126000;

6. Shell Programming and Scripting

AWK formatting help.

Dear all I require help with AWK regarding this situation Input is : fn1 12345 fn1 23456 fn3 231513 fn1 22325 fn3 123125 Desired output is fn1 12345 23456 22325 fn3 231513 123125

7. Shell Programming and Scripting

String formatting using AWK

Hi, I need to insert a line at a particular line number. I am using the below code: sed $REV_LINO_NO" i\\ # $CURRENT_DATE $NAME Changed pwd for cindy\'s id" file > file1 This code works, but the formatting is not as I expected. For example, I get lines as shown below...

8. Shell Programming and Scripting

formatting data file with awk or sed

Hi, I have a (quite large) data file which looks like: _____________ header part.. more header part.. x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13 ... ... x59 x60 y1 y2 y3 y4... ... y100 ______________ where x1, x2,...,x60 and y1, y2,...y100 are numbers of 10 digits (so each line...

9. Shell Programming and Scripting

Awk formatting of a data file - nested for loops?

Hello - is there any way in awk I can do... 4861 x(1) y(1) z(1) 4959 x(1) y(1) z(1) 5007 x(1) y(1) z(1) 4861 x(2) y(2) z(2) 4959 x(2) y(2) z(2) 5007 x(2) y(2) z(2) 4861 x(3) y(3) z(3) 4959 x(3) y(3) z(3) 5007 x(3) y(3) z(3) to become... 4861 x(1) y(1) z(1) 4861 x(2) y(2) z(2)...

10. Shell Programming and Scripting

Formatting using awk

Let's say I write a simple script that contains the following: date | awk '{print $1}' date | awk '{print $2}' Of course, when I run the script the output will look similar to: Tue Mar What if I want my ouput to be on one line as follows: Tue Mar What changes would I need to...

Login or Register to Ask a Question