Sponsored Content
Special Forums Hardware Filesystems, Disks and Memory manipulate csv file to add columns Post 302098443 by oscarmon on Monday 4th of December 2006 09:37:51 AM
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.
 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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
ogrinfo(1)						      General Commands Manual							ogrinfo(1)

NAME
ogrinfo - ogrinfo lists information about an OGR supported data source SYNOPSIS
ogrinfo [--help-general] [-ro] [-q] [-where restricted_where] [-spat xmin ymin xmax ymax] [-fid fid] [-sql statement] [-dialect dialect] [-al] [-so] [-fields={YES/NO}] [-geom={YES/NO/SUMMARY}][--formats] datasource_name [layer [layer ...]].fi DESCRIPTION
The ogrinfo program lists various information about an OGR supported data source to stdout (the terminal). -ro: Open the data source in read-only mode. -al: List all features of all layers (used instead of having to give layer names as arguments). -so: Summary Only: supress listing of features, show only the summary information like projection, schema, feature count and extents. -q: Quiet verbose reporting of various information, including coordinate system, layer schema, extents, and feature count. -where restricted_where: An attribute query in a restricted form of the queries used in the SQL WHERE statement. Only features matching the attribute query will be reported. -sql statement: Execute the indicated SQL statement and return the result. -dialect dialect: SQL dialect. In some cases can be used to use (unoptimized) OGR SQL instead of the native SQL of an RDBMS by passing OGRSQL. -spat xmin ymin xmax ymax: The area of interest. Only features within the rectangle will be reported. -fid fid: If provided, only the feature with this feature id will be reported. Operates exclusive of the spatial or attribute queries. Note: if you want to select several features based on their feature id, you can also use the fact the 'fid' is a special field recognized by OGR SQL. So, '-where 'fid in (1,3,5)'' would select features 1, 3 and 5. -fields={YES/NO}: (starting with GDAL 1.6.0) If set to NO, the feature dump will not display field values. Default value is YES. -geom={YES/NO/SUMMARY}: (starting with GDAL 1.6.0) If set to NO, the feature dump will not display the geometry. If set to SUMMARY, only a summary of the geometry will be displayed. If set to YES, the geometry will be reported in full OGC WKT format. Default value is YES. --formats: List the format drivers that are enabled. datasource_name: The data source to open. May be a filename, directory or other virtual name. See the OGR Vector Formats list for supported datasources. layer: One or more layer names may be reported. If no layer names are passed then ogrinfo will report a list of available layers (and their layerwide geometry type). If layer name(s) are given then their extents, coordinate system, feature count, geometry type, schema and all features matching query parameters will be reported to the terminal. If no query parameters are provided, all features are reported. Geometries are reported in OGC WKT format. EXAMPLE
Example reporting all layers in an NTF file: % ogrinfo wrk/SHETLAND_ISLANDS.NTF INFO: Open of `wrk/SHETLAND_ISLANDS.NTF' using driver `UK .NTF' successful. 1: BL2000_LINK (Line String) 2: BL2000_POLY (None) 3: BL2000_COLLECTIONS (None) 4: FEATURE_CLASSES (None) Example using an attribute query is used to restrict the output of the features in a layer: % ogrinfo -ro -where 'GLOBAL_LINK_ID=185878' wrk/SHETLAND_ISLANDS.NTF BL2000_LINK INFO: Open of `wrk/SHETLAND_ISLANDS.NTF' using driver `UK .NTF' successful. Layer name: BL2000_LINK Geometry: Line String Feature Count: 1 Extent: (419794.100000, 1069031.000000) - (419927.900000, 1069153.500000) Layer SRS WKT: PROJCS["OSGB 1936 / British National Grid", GEOGCS["OSGB 1936", DATUM["OSGB_1936", SPHEROID["Airy 1830",6377563.396,299.3249646]], PRIMEM["Greenwich",0], UNIT["degree",0.0174532925199433]], PROJECTION["Transverse_Mercator"], PARAMETER["latitude_of_origin",49], PARAMETER["central_meridian",-2], PARAMETER["scale_factor",0.999601272], PARAMETER["false_easting",400000], PARAMETER["false_northing",-100000], UNIT["metre",1]] LINE_ID: Integer (6.0) GEOM_ID: Integer (6.0) FEAT_CODE: String (4.0) GLOBAL_LINK_ID: Integer (10.0) TILE_REF: String (10.0) OGRFeature(BL2000_LINK):2 LINE_ID (Integer) = 2 GEOM_ID (Integer) = 2 FEAT_CODE (String) = (null) GLOBAL_LINK_ID (Integer) = 185878 TILE_REF (String) = SHETLAND I LINESTRING (419832.100 1069046.300,419820.100 1069043.800,419808.300 1069048.800,419805.100 1069046.000,419805.000 1069040.600,419809.400 1069037.400,419827.400 1069035.600,419842 1069031,419859.000 1069032.800,419879.500 1069049.500,419886.700 1069061.400,419890.100 1069070.500,419890.900 1069081.800,419896.500 1069086.800,419898.400 1069092.900,419896.700 1069094.800,419892.500 1069094.300,419878.100 1069085.600,419875.400 1069087.300,419875.100 1069091.100,419872.200 1069094.600,419890.400 1069106.400,419907.600 1069112.800,419924.600 1069133.800,419927.900 1069146.300,419927.600 1069152.400,419922.600 1069153.500,419917.100 1069153.500,419911.500 1069153.000,419908.700 1069152.500,419903.400 1069150.800,419898.800 1069149.400,419894.800 1069149.300,419890.700 1069149.400,419890.600 1069149.400,419880.800 1069149.800,419876.900 1069148.900,419873.100 1069147.500,419870.200 1069146.400,419862.100 1069143.000,419860 1069142,419854.900 1069138.600,419850 1069135,419848.800 1069134.100,419843 1069130,419836.200 1069127.600,419824.600 1069123.800,419820.200 1069126.900,419815.500 1069126.900,419808.200 1069116.500,419798.700 1069117.600,419794.100 1069115.100,419796.300 1069109.100,419801.800 1069106.800,419805.000 1069107.300) AUTHORS
Frank Warmerdam warmerdam@pobox.com, Silke Reimer silke@intevation.de GDAL
Tue Sep 18 2012 ogrinfo(1)
All times are GMT -4. The time now is 12:47 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy