awk command to manipulate csv file in UNIX


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk command to manipulate csv file in UNIX
# 1  
Old 01-29-2016
awk command to manipulate csv file in UNIX

Hi, I am new to awk and unix programming and trying to manipulate a csv file.

My current csv file looks like this:

Code:
col1,col2,col3,col4,col5,col4,col5,col6,col7,col8
223,V,c,2,4,f,r,,y,z
223,V,c,3,2,f,r,,y,z
223,V,c,1,4,f,r,,y,z
223,V,c,4,3,f,r,,y,z
227,V,c,3,1,f,r,,y,z
227,V,c,4,1,f,r,,y,z
228,V,c,5,5,f,r,,y,z
228,V,c,2,4,f,r,,y,z
228,V,c,1,7,f,r,,y,z
228,V,c,1,8,f,r,,y,z
228,V,c,1,1,f,r,,y,z
228,V,c,1,4,f,r,,y,z
228,V,c,1,5,f,r,,y,z

Desired output csv:

Code:
col1,Sum of col4,Sum of col5
223,10,13
227,7,2
228,12,34

I tried a bunch of awk commands but nothing seemed to work Smilie

Is there any awk solution for this?

Thank you in advance.

Last edited by Don Cragun; 01-29-2016 at 05:49 PM.. Reason: Add CODE and ICODE tags.
# 2  
Old 01-29-2016
Code:
awk -F, '
        {
                A_4[$1] += $4
                A_5[$1] += $5
        }
        END {
                for ( k in A_4 )
                        print k, A_4[k], A_5[k]
        }
' OFS=, file

# 3  
Old 01-29-2016
solution

its not working correctly, in the middle of the file it prints the header in one of the rows, and the first line is ,0,0

my csv file has headers so i added NR==1

still no luck
# 4  
Old 01-29-2016
Yoda's suggestion looks good except for the way it handles the input and output header lines. You might want to try this slight modification:
Code:
awk -F, '
NR == 1 {
	OFS = ","
	print $1, "Sum of " $4, "Sum of " $5
	next
}
{	s4[$1] += $4
	s5[$1] += $5
}
END {	for(i in s4)
		print i, s4[i], s5[i]
}' file.csv

Note that the output produced by this script could (except for the header line) have the output lines in random order. If you need the output to be sorted by increasing numeric order of the 1st field, or if you want the output to be in the same order as the input (assuming all lines with the same 1st field value are grouped together as in your sample input), the script would need changed to consistently produce the output order you want.

If you want to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk.
# 5  
Old 01-30-2016
To retain the col1's contents' order, try
Code:
awk '
NR == 1 {print "col1,Sum of col4,Sum of col5"
         next
        }
$1 != LAST &&
NR > 2  {print LAST, SUM4, SUM5
         SUM4 = SUM5 = 0
        }
        {SUM4 += $4
         SUM5 += $5
         LAST  = $1
        }
END     {print LAST, SUM4, SUM5
        }
' FS=, OFS=, file
col1,Sum of col4,Sum of col5
223,10,13
227,7,2
228,12,34

# 6  
Old 01-30-2016
A few bits of Perl.
Code:
perl -nalF, -e '                                  # set Perl to make tokens out of lines
BEGIN{$, = $" = ","}                              # output separators
$c[$o++] = $F[0] unless $col1{$F[0]};             # create a queue order
$col1{$F[0]}->[0] += $F[3];                       # add to column 4
$col1{$F[0]}->[1] += $F[4];                       # add to column 5
END{
    $col1{$c[0]}=["Sum of col4", "Sum of col5"];  # set header
    for(@c){print $_,@{$col1{$_}}}                # display tally
}' file.csv

Code:
col1,Sum of col4,Sum of col5
223,10,13
227,7,2
228,12,34


Last edited by Aia; 01-30-2016 at 02:12 PM.. Reason: Add coments
# 7  
Old 02-03-2016
question

Thank you so much! Both RudiC and Aia's solutions work, howeverwith the awk solution, the second line prints

Code:
,0,0

and with the perl solution the last line prints

Code:
,0,0

Can someone explain what might be causing this?

Last edited by Prit Siv; 02-03-2016 at 01:50 PM.. Reason: code tags
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Homework & Coursework Questions

awk command to retrieve record 23 and 89 from UNIX file

Use and complete the template provided. The entire template must be completed. If you don't, your post may be deleted! 1. The problem statement, all variables and given/known data: I am looking for awk command to retrieve only the record number 23 and record number 89 from a unix file?... (6 Replies)
Discussion started by: rakeshp
6 Replies

2. UNIX for Beginners Questions & Answers

awk command to retrieve record 23 and 89 from UNIX file

Hi Everyone, I am looking for awk command to retrieve only the record number 23 and record number 89 from a unix file? Please let me know what is the awk command for this? Regards Rakesh (1 Reply)
Discussion started by: rakeshp
1 Replies

3. Shell Programming and Scripting

Manipulate the text file in UNIX

Hi All, I have a file like below and i have 2 questions on this (They are 3 lines starts with 01 , 02 and 03. but is 01abc333644554 234 2334535 34534535355353 sfsdf345455 353 4543 jgkg tty 7676 02cdesdfsdfsdf 234 wesdfsdf 345345 234234 234234 2342342 dfgdfg sdfgg dgdgdg fgvfs... (6 Replies)
Discussion started by: siva.pitchai
6 Replies

4. Shell Programming and Scripting

awk command to manipulate csv file in UNIX

Hi, I am new to awk/unix and am trying to put together a script to manipulate the date column in a csv file. I have file1.csv with the following contents: Date,ID,Number,Amount,Volume,Size 01-Apr-2014,WERFG,998,105873.96,10873.96,1342.11 01-Apr-2014,POYFR,267,5681.44,5681.44,462.96 I... (2 Replies)
Discussion started by: Prit Siv
2 Replies

5. Shell Programming and Scripting

Manipulate a CSV File

Hello, How do i manipulate .csv file to this format? Thank you very much. Source: john,5 marco,7 john,4 paul,3 marco,8 Output: john,9 marco,15 (5 Replies)
Discussion started by: tara123
5 Replies

6. Shell Programming and Scripting

UNIX command output in csv format

I'm just wondering is there any way to capture the output of a unix command in a csv format. df -h gives the result of filesystem,free space,Used space, use %,mounted on. Is there a way to capture the command output and format it as comma sparated or fixed length file. (3 Replies)
Discussion started by: anita81
3 Replies

7. UNIX for Dummies Questions & Answers

xml to csv using sed and awk command

Hi Guys, Can you help me in creating shell script using sed,awk etc commands to generate csv file using xml file. (5 Replies)
Discussion started by: sbk
5 Replies

8. UNIX for Dummies Questions & Answers

Question on how to manipulate a SIMPLE text file (using awk?)

I have a simple txt files that looks something like this (The title is a part of the text file) Student Grades --------------- 1 Tim Purser 89 2 John Wayne 56 3 Jenn Hawkins 95 4 Harry Potter 75 Here are my questions: How would I ONLY print the names of students... (2 Replies)
Discussion started by: ninjagod123
2 Replies

9. Shell Programming and Scripting

Using awk command for .csv file

Hi , I have .csv file with value separated by ";". 1. Using awk how to extract perticular colums and store in to array 2. For some columns I needs to extract last value of the column How to do same please help me ASAP Thanks and Regards, Sushma (9 Replies)
Discussion started by: sushmab82
9 Replies

10. Filesystems, Disks and Memory

manipulate csv file to add columns

Hi, I have a csv file with a key composed by 3 columns and some other numeric fields and I need to obtain the partial amounts by some part of the key. This may be some difficult to understand, so better see an example, where my input file is: name,surname,department,y2004,y2005,y2006... (6 Replies)
Discussion started by: oscarmon
6 Replies
Login or Register to Ask a Question

Featured Tech Videos