|
|
|
|||||||
| Home | Forums | Register | Rules & FAQ | Members List | Arcade | Search | Today's Posts | Mark Forums Read |
| Filesystems, Disks and Memory Questions involving NAS, SAN, RAID, Robotic Libraries, backups, etc go here. |
Other UNIX.COM Threads You Might Find Helpful
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Adding columns to a file | figaro | UNIX for Dummies Questions & Answers | 4 | 1 Week Ago 05:38 AM |
| have a file with 300 columns | dummy_needhelp | Shell Programming and Scripting | 4 | 11-04-2007 09:08 PM |
| How do manipulate file path and names | siegfried | Shell Programming and Scripting | 2 | 09-28-2007 08:20 AM |
| How can i replace certain columns in the file | mani_um | Shell Programming and Scripting | 6 | 06-22-2007 07:40 AM |
| Appending columns on a file | abel | Shell Programming and Scripting | 2 | 09-27-2002 04:04 AM |
 |
|
|
Submit Tools | LinkBack | Thread Tools | Search this Thread | Display Modes |
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. 
|
| Forum Sponsor | ||
|
|
|
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
|
|
12-04-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.
|
|
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
|
Linear Mode
