manipulate csv file to add columns


 
Thread Tools Search this Thread
Special Forums Hardware Filesystems, Disks and Memory manipulate csv file to add columns
# 1  
Old 12-04-2006
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
John,Smith,sales,300,200,100
Mary,Grant,sales,100,0,100
John,Smith,IT,0,50,250
George,Parker,IT,200,150,250
Sarah,Lee,marketing,300,400,100
John,Smith,marketing,200,0,250

And would like to obtain an output file with the total amounts added by department like:

department,y2004,y2005,y2006
sales,400,200,200
IT,200,200,500
marketing,500,400,350

If you imagine this is a database table, this could easily be done in sql with a group by, but unix scripting is something different... Could anyone help me with this?
Thanks a lot.

Oscar.
# 2  
Old 12-04-2006
awk -F, 'NR==1 {head=$0;next}
{ y4[$3]+=$4;y5[$3]+=$5;y6[$3]+=$6}
END {
print head;
print "sales,"y4["sales"],y5["sales"],y6["sales"];
print "IT," y4["IT"],y5["IT"],y6["IT"];
print "marketing," y4["marketing"],y5["marketing"],y6["marketing"];
}' <data.txt

John Arackal
# 3  
Old 12-05-2006
Hi John, thanks for your quick reply, I hardly had time to test my first awk programs... Just a thing, what if you don't know which are going to be the departments? Though, the department data is going to be grouped as in the example.
Oscar.
# 4  
Old 12-05-2006
Untested, and untidy

Code:
awk -F, 'NR==1 {  printf "%15s", $3
                         for ( i = 4 ; i <= NF; i++ ) { 
                             printf "%8s", $i 
                         }
                         printf "\n"
                         next
                      }                                               
                     {  ind[$3] = NF 
                         for ( i = 4 ; i <= NF; i++ ) {
                             result[$3,i] += $i 
                         }
                      }
            END {
                      for ( j in ind ) { 
                         printf "%15s",j 
                         for ( i = 4 ; i <= ind[j]; i++) { 
                             printf "%8d", result[j,i]
                         }
                         print line}
                   }' datafile

# 5  
Old 12-06-2006
Hi, I could manage with that,
thanks a lot.
# 6  
Old 12-08-2006
Python alternative
Code:
data = {} #to store results
f = open("input.csv")
f.readline()
for line in f:
 	line = line.split(",")
 	line[3:] = [int(i) for i in line[3:] ]
 	if not data.has_key(line[2]):
 		data[ line[2] ] = [ line[3], line[4] , line[5] ]
 	else:
 		data[ line[2] ] = [ data[line[2]][0] + line[3], data[line[2]][1] + line[4] , data[line[2]][2] + line[5]]

for i,j in data.iteritems():
 	print i,j

output:
Code:
marketing [500, 400, 350]
sales [400, 200, 200]
IT [200, 200, 500]

# 7  
Old 12-11-2006
I love Python, tidy and elegant, simple and effective. By the way, I know this is not the correct thread, but anyone who had been in touch with "pexpect" please let me know.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

To add multiple columns in csv file

Hi Guys, Can you help to add static values at the end of the csv file with headers input_file id,name 1,neo 2,pull Expected id,name,status,entry,g_id 1,neo,done,2019-11-01T07:14:23,pass 2,pull,done,2019-11-01T07:14:23,pass My try but not able replacing properly and unable... (1 Reply)
Discussion started by: Master_Mind
1 Replies

2. Shell Programming and Scripting

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: 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... (8 Replies)
Discussion started by: Prit Siv
8 Replies

3. Shell Programming and Scripting

Add 8 columns at the end of .csv file using awk

Hello all, I have a .csv file of 16 columns consists of bunch of numbers. 6.45E+01 1.17E+01 8.10E+04 8.21E+01 8.50E+00 1.20E+01 1.02E+01 1.88E+01 1.86E+04 3.53E+03 1.09E+07 3.82E+04 2.09E+03 3.57E+03 2.98E+03 3.93E+03 6.34E+01 3.23E+01 9.24E+04 ... (5 Replies)
Discussion started by: Zam_1234
5 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. Linux

Add empty columns at the end of a CSV file

I have a .CSV file (lets say named as file.csv) with numeric and string values. The string might contain commas hence they are enclosed in double quotes as in the below format. column1,column2,column3,column4,column5,column6,column7 12,455,"string, with, quotes, and with, commas, in... (3 Replies)
Discussion started by: dhruuv369
3 Replies

7. UNIX for Dummies Questions & Answers

Manipulate and move columns in a file

Hello Unix Gurus, I have a request 2 perform several functions on a file, delete columns, delete rows based on column value, and finally move around columns in the final output. Consider the following input file with 12 columns; ... (1 Reply)
Discussion started by: chumsky
1 Replies

8. Shell Programming and Scripting

AWK : Add columns in the end of csv file

Hi everybody, I need some help please I have a csv file named masterFile1.csv header1,header2,header3 value1,value2,value3 value4,value5,value6 I am trying to add new columns in the end of the csv to have a new csv file named masterFile2.csv like this :... (3 Replies)
Discussion started by: villebonnais
3 Replies

9. Shell Programming and Scripting

Add columns in csv file

Hi everybody, I am new here and I need a reel help please A have a csv file and I want to add new in the end of the file Devisce Model,VMGuest Name,Host OS Name, Memory Size Value1,Value2,Value3,Value4 Value5,Value6,Value7,Value8 Value9,Value10,Value11,Value12 And I want to add to new... (3 Replies)
Discussion started by: villebonnais
3 Replies

10. Shell Programming and Scripting

Add Empty columns at the end of csv file

Hi, Can you please tell me how to add empty columns at the end csv file? Currently there are 6 columns in the csv file. I want to add 35 empty columns at the end of this csv file. Thanks, Tushar (17 Replies)
Discussion started by: Tushar Bendale
17 Replies
Login or Register to Ask a Question