Processing of csv files. sycsvpro offers following functions
- analyze a csv file
- extract rows and columns from a file
- remove duplicate lines from a file where duplicates are identified by key columns (since version 0.1.11) add unique to command line interface (since version 0.1.12)
- collect values of rows and assign them to categories
- map column values to new values
- allocate column values to a key column (since version 0.0.4)
- count values in columns and use the value as column name
- aggregate row values and add the sum to the end of the row
- arithmetic operations on values of columns
- sort rows based on columns (since version 0.0.9)
- insert rows to a csv-file (since version 0.0.8)
- create or edit a Ruby script
- list scripts available optionally with methods (since version 0.0.7)
- execute a Ruby script file that operates a csv file
- create a table from a source file with dynamically create columns (since version 0.1.4)
- join two file based on a joint column value (since version 0.1.7)
- merge files based on common headline columns (since version 0.1.10)
- transpose (swapping) rows and columns (since version 0.1.13)
- arithmetic operations between multiple files that have a table like structure (since version 0.2.0)
To get help type
$ sycsvpro -h
In the following examples we assume the following files 'machines.csv', 'region.csv' and revenue.csv
customer;machine;control;drive;motor;date;contract;price;c-id
hello;h1;con123;dri120;mot100;1.01.3013;1;2.5;123
hello;h2;con123;dri130;mot110;1.02.3012;1;12.1;123
indix;i1;con456;dri130;mot090;5.11.3013;1;23.24;345
chiro;c1;con333;dri110;mot100;1.10.3011;1;122.15;456
chiro;c2;con331;dri100;mot130;3.05.3010;1;25.3;456
region;country;c-id
R1;DE,123
R2;AT;234
R3;US;345
R4;CA;456
2010;2011;2012;2013;2014;customer
50;100;150;100;200;hello
100;50;10;1000;20;indix
2000;250;300;3000;chiro
Analyze the content of the provided file in.csv
$ sycsvpro -f in.csv analyze
Analysis of in.csv
7 columns: ["customer", "machine", "control", "drive", "motor", "date", "contract"]
10393 rows
0: customer
1: machine
2: control
3: drive
4: motor
5: date
6: contract
Row sample data:
hello;h1;con123;dri120;mot100;16.02.2014;1
Extract row 1,2 and 10-20 as well as columns 4 and 6-7
$ sycsvpro -f in.csv -o out.csv extract -r 1,2,10-20 -c 4,6-7
Remove duplicate lines from a file. Duplicates are identified by key columns. If no key columns are provided the whole line is checked for uniqueness
$ sycsvpro -f in.csv -o out.csv unique -r 1,2,8-12 -c 4,10-15 -k 0,1
Collect all product rows (2, 3 and 4) to the category product
$ sycsvpro -f in.csv -o out.csv collect -r 2-20 -c products:2-4
$ cat out.csv
[products]
con123
con331
con333
con456
dri100
dri110
dri120
dri130
mot090
mot100
mot110
mot130
Map the product names to new names. Consider columns 2-4 only for mapping
The mapping file (mapping) uses the result from the collect command above
con123:control123
con331:control331
con333:control333
con456:control456
dri100:drive100
dri110:drive110
dri120:drive120
dri130:drive130
mot090:motor090
mot100:motor100
mot110:motor110
mot130:motor130
$ sycsvpro -f in.csv -o out.csv map mapping -c 2-4
Swap rows and columns of revenue.csv to out.csv
$ sycsvpro -f revenue.csv -o out.csv transpose
2010;50;100;2000
2011;100;50;250
2012;150;10;300
2013;100;1000;3000
2014;200;20;20
customer;hello;indix;chiro
To use only columns 2013 and 2014 you can specify a the columns to transpose
$ sycsvpro -f revenue.csv -o out.csv transpose -c 3-5
2013;100;1000;3000
2014;200;20;20
customer;hello;indix;chiro
To filter for hello only
$ sycsvpor -f revenue.csv -o out.csv transpose -c 3-5 -r 0,1
2013;100
2014;200
customer;hello
Allocate all the machine types to the customer
$ sycsvpro -f in.csv -o out.csv assign -k 0 -r 1-20 -c 1
hello;h1;h2
indix;i1
chiro;c1;c2
Count all customers (key column) in rows 2 to 20 that have machines that start with h and have a contract valid beginning after 1.1.2000. Add a sum row with title Total at column 1
$ sycsvpro -f in.csv -o out.csv count -r 2-20 -k 0:customer
-c 1:/^h/,5:">1.1.2000" --df "%d.%m.%Y" -s "Total:1"
The result in file out.csv is
$ cat out.csv
customer;>1.1.2000;^h
Total;5;2
hello;2;2
indix;1;0
chiro;2;0
It is possible to use multiple key columns -k 0:customer,1:machines
Aggregate row values and add the sum to the end of the row. In the example we aggregate the customer names.
$ sycsvpro -f in.csv -o out.csv aggregate -c 0 -s Total:1,Sum
The aggregation result in out.csv is
$ cat out.csv
customer;Sum
Total;5
hello;2
indix;1
chiro;2
Analyze the contract revenue per customer and per year
$ sycsvpro -f in.csv -o out.csv table
-h "Customer,c5=~/\\.(\\d{4})/"
-k c1
-c "c5=~/\\.\\d{4})/:+n1"
The table result will be in out.csv
$ cat out.csv
Customer;3013;3012;3011;3010
hello;2.5;12.1;0;0
indix;23.24;0;0;0
chiro;0;0;122.15;25.3
Process arithmetic operations on the contract count and create a target column and a sum which is added at the end of the result file
$ sycsvpro -f in.csv -o out.csv calc -r 2-20 -h *,target
-c 6:*2,7:c6*10
$ cat out.csv
customer;machine;control;drive;motor;date;contract;target
hello;h1;con123;dri120;mot100;1.01.3013;2;20
hello;h2;con123;dri130;mot110;1.02.3012;2;20
indix;i1;con456;dri130;mot090;5.11.3013;2;20
chiro;c1;con333;dri110;mot100;1.10.3011;2;20
chiro;c2;con331;dri100;mot130;3.05.3010;2;20
0;0;0;0;0;0;10;100
In the sum row non-numbers in the colums are converted to 0. Therefore column 0 is summed up to 0 as all strings are converted to 0.
Write only columns 0, 6 and 7 by specifying write columns
$ sycsvpro -f in.csv -o out.csv calc -r 2-20 -h "customer,contract,target"
-c 6:*2,7:c6*10
-w 0,6-7
$ cat out.csv
customer;contract;target
hello;2;20
hello;2;20
indix;2;20
chiro;2;20
chiro;2;20
0;10;100
A spread sheet is a table with rows and columns. On or between spread sheets operations can be conducted. A spread sheet's rows must have same column sizes and may have row and column labels.
Use cases are
- arithmetic operations on spread sheets
- information about table like data
###Example for Arithmetic Operation Asume we want to calculate the market for computer services. We have the count of computers in each country, we are offering different services with service specific prices. We know the market for each service in percent. With this data we can calculate the market value.
Count of computers in target countries
[Tablet] [Laptop] [Desktop]
[CA] 1000 2000 500
[DE] 2000 3000 400
[MX] 500 4000 800
[RU] 1500 1500 1000
[TR] 1000 2500 3000
[US] 3000 3500 1200
Prices for different services offered computer specific
[Clean] [Maintain] [Repair]
[Tablet] 10 50 100
[Laptop] 20 60 150
[Desktop] 50 100 200
Market for the different services
[Clean] [Maintain] [Repair]
[Tablet] 0.10 0.05 0.03
[Laptop] 0.05 0.10 0.02
[Desktop] 0.20 0.30 0.04
To calculate the market value we have to multiply each row of the country file with the columns of the service prices and service market file (for readabiltiy it has been split up to multiple rows)
$ sycsvpro -o market_value.csv spreadsheet \
-f country.csv,prices.csv,market.csv \
-a country,price,market \
-o "SpreadSheet.bind_columns( \
country.transpose.column_collect { |value| value * price * market } \
).transpose"
Note: If you get obscure errors then check whether your aliases (-a flag)
conflict with a method of your classes. Therefore it is adviced to
always use specific names like in the example country, price, market
The result of the operation is written to market_value.csv (labels have been optimized for better readability)
[Tablet] [Laptop] [Desktop]
[CA-Clean] 1000.0 2000.0 5000.0
[CA-Maintain] 2500.0 12000.0 15000.0
[CA-Repair] 3000.0 6000.0 4000.0
[DE-Clean] 2000.0 3000.0 4000.0
[DE-Maintain] 5000.0 18000.0 12000.0
[DE-Repair] 6000.0 9000.0 3200.0
[MX-Clean] 500.0 4000.0 8000.0
[MX-Maintain] 1250.0 24000.0 24000.0
[MX-Repair] 1500.0 12000.0 6400.0
[RU-Clean] 1500.0 1500.0 10000.0
[RU-Maintain] 3750.0 9000.0 30000.0
[RU-Repair] 4500.0 4500.0 8000.0
[TR-Clean] 1000.0 2500.0 30000.0
[TR-Maintain] 2500.0 15000.0 90000.0
[TR-Repair] 3000.0 7500.0 24000.0
[US-Clean] 3000.0 3500.0 12000.0
[US-Maintain] 7500.0 21000.0 36000.0
[US-Repair] 9000.0 10500.0 9600.0
###Example for Information on Spread Sheets With the analyze command we get information about the general structure and some sample data of a csv file. If we want to look at the csv file more detailed we can use the spreadsheet command. In this case we don't want to write the result to the file as it is no spread sheet, so we can ommit the global -o option.
sycsvpro spreadsheet -f country.csv -r true -c true -a a \
-o "puts;puts a;puts a.ncol;puts a.nrow;puts a.size"
This will give us the information about the data, the number of columns and rows and the number of values in the csv file. But for this case there is a standard method that provides this information
sycsvpro spreadsheet -f country.csv -r true, -c true -a a -o "a.summary"
Summary
-------
rows: 6, columns: 3, dimension: [6, 3], size: 18
row labels:
["CA","DE","MX","RU","TR","US"]
column labels:
["Clean","Maintain","Repair"]
If the result is no spread sheet it won't be written to the outfile (-o) but we can print the result to the console with the -p flag
sycsvpro spreadsheet -f country.csv,prices.csv,market.csv \
-r true,true,true -c true,true,true \
-a country,price,market \
-o "result = []; \
a.each_column { \
|column| result << column * price * market \
}; \
result" \
-p
The last evaluation, in this case result, will be returned as the result. The -p flag will print the result to the console
Operation
---------
result = []
country.transpose.each_column { |column| result << column * price * market }
result
Result
------
[CA*Clean*Clean] [CA*Maintain*Maintain] [CA*Repair*Repair]
[Tablet*Tablet*Tablet] 1000.0 2500.0 3000.0
[Laptop*Laptop*Laptop] 2000.0 12000.0 6000.0
[Desktop*Desktop*Desktop] 5000.0 15000.0 4000.0
[DE*Clean*Clean] [DE*Maintain*Maintain] [DE*Repair*Repair]
[Tablet*Tablet*Tablet] 2000.0 5000.0 6000.0
[Laptop*Laptop*Laptop] 3000.0 18000.0 9000.0
[Desktop*Desktop*Desktop] 4000.0 12000.0 3200.0
[MX*Clean*Clean] [MX*Maintain*Maintain] [MX*Repair*Repair]
[Tablet*Tablet*Tablet] 500.0 1250.0 1500.0
[Laptop*Laptop*Laptop] 4000.0 24000.0 12000.0
[Desktop*Desktop*Desktop] 8000.0 24000.0 6400.0
[RU*Clean*Clean] [RU*Maintain*Maintain] [RU*Repair*Repair]
[Tablet*Tablet*Tablet] 1500.0 3750.0 4500.0
[Laptop*Laptop*Laptop] 1500.0 9000.0 4500.0
[Desktop*Desktop*Desktop] 10000.0 30000.0 8000.0
[TR*Clean*Clean] [TR*Maintain*Maintain] [TR*Repair*Repair]
[Tablet*Tablet*Tablet] 1000.0 2500.0 3000.0
[Laptop*Laptop*Laptop] 2500.0 15000.0 7500.0
[Desktop*Desktop*Desktop] 30000.0 90000.0 24000.0
[US*Clean*Clean] [US*Maintain*Maintain] [US*Repair*Repair]
[Tablet*Tablet*Tablet] 3000.0 7500.0 9000.0
[Laptop*Laptop*Laptop] 3500.0 21000.0 10500.0
[Desktop*Desktop*Desktop] 12000.0 36000.0 9600.0
Join the machine and contract file with columns from the customer address file
$ sycsvpro -f in.csv -o out.csv join address.csv -c 0,1
-p 2,1
-i "COUNTRY,REGION"
-j "3=8"
This will create the result
customer;COUNTRY;REGION;machine;control;drive;motor;date;contract;price;c-id
hello;DE;R1;h1;con123;dri120;mot100;1.01.3013;1;2.5;123
hello;DE;R1;h2;con123;dri130;mot110;1.02.3012;1;12.1;123
indix;US;R3i1;con456;dri130;mot090;5.11.3013;1;23.24;345
chiro;CA;R4;c1;con333;dri110;mot100;1.10.3011;1;122.15;456
chiro;CA;R4;c2;con331;dri100;mot130;3.05.3010;1;25.3;456
If you have multiple IDs in a row than you can also conduct multiple joins in on streak.
$ sycsvpro -f in.csv -o out.csv join address.csv -c 0,1;0,3
-p 2,1;4,5
-i "COUNTRY,REGION"
-j "3=8;3=10"
Merge files machine_count.csv and revenue.csv based on the year columns.
$ sycsvpro -o out.csv merge machines.csv,revenue.csv
-h "2010,2013,2014"
-k "0,5"
-s "(\\d{4}),(\\d{4})"
This will create the out.csv
$ cat out.csv
;2010;2013;2014
hello;1;0;0
indix;1;0;0
chiro;0;1;0
hello;50;100;200
indix;100;1000;20
chiro;2000;300;3000
Sort rows on specified columns as an example sort rows based on customer (string s) and contract date (date d)
$ sycsvpro -f in.csv -o out.csv sort -r 2-20 -c s:0,d:5
$cat out.csv
customer;machine;control;drive;motor;date;contract;target
hello;h2;con123;dri130;mot110;1.02.3012;1
hello;h1;con123;dri120;mot100;1.01.3013;1
indix;i1;con456;dri130;mot090;5.11.3013;1
chiro;c2;con331;dri100;mot130;3.05.3010;1
chiro;c1;con333;dri110;mot100;1.10.3011;1
Sort expects the first non-empty row as the header row. If --headerless switch is set then sort assumes no header being available.
Add rows at the bottom or on top of a file. The command below adds the content of the file file-with-rows-to-insert.text on top of the file in.csv and saves it to out.csv
$ sycsvpro -f in.csv -o out.csv insert file-with-rows-to-insert.txt -p top
Creates or if it exists opens a file for editing. The file is created in the directory ~/.syc/sycsvpro/scripts. Following command creates a Ruby script with the name script.rb and a method call_me
$ sycsvpro edit -s script.rb -m call_me
List the scripts, insert-file or all scripts available in the scripts directory which is also displayed. Comments before methods are also displayed
script directory: ~/.syc/sycsvpro/scripts
$ sycsvpro list -m
script.rb
call_me
Execute takes a Ruby script file as an argument and processes the script. The following command executes the script script.rb and invokes the method calc
$ sycsvpro execute ./script.rb calc
Below is an example script file that is ultimately doing the same as the count command
$ sycsvpro -f in.csv -o out.csv count -r 1-20 -k 0 -c 4,5
def calc
customers = {}
heading = []
rows infile: "./spec/sycsvpro/files/in.csv",
row_filter: "1-20",
key_column: 0,
machine_column: 3,
data_columns: [4,5] do |key, machine, columns|
customer = customers[key] || customers[key] = { name: key, products: Hash.new(0) }
columns.each do |column|
heading << column if heading.index(column).nil?
customer[:products][column] += 1
end
end
write_to "./spec/sycsvpro/files/out.csv" do |out|
out.puts (["customer"] + heading.sort).join(';')
customers.each do |k,v|
line = [k]
heading.sort.each do |h|
line << v[:products][h]
end
out.puts line.join(';')
end
end
end
rows and write_to are convenience methods provided by sycsvpro that can be used in script files to operate on files.
rows will return values at the specified columns in the order they are provided in the call to rows. The columns to be returned in the block have to end with column or columns dependent if a value or an array should be returned. You can find the rows and write_to methods at lib/sycsvpro/dsl.rb.
Examples for scripts using sycsvpro can be found at sugaryourcoffee/sycsvpro-scripts
sycsvpro emerged from my daily work when cleaning and anaylzing data. If you want to dig deeper I would recommend R.
A work flow could be as follows
- Analyze the file
analyze
orspreadsheet
- Clean the data
map
- Extract rows and columns of interest
extract
- Count values
count
- Do arithmetic operations on the values
calc
orspreadsheet
- Sort the rows based on column values
sort
When I have analyzed the data I use Microsoft Excel or LibreOffice Calc to create nice graphs. To create more sophisiticated analysis R is the right tool to use. I also use sycsvpro to clean and prepare data and then do the analysis with R.
-
Now it is possible to have comma ',' in the filter as non separating values. You can now define a filter like 1-2,4,/[56789]{2,}/,10
-
Filtering rows on boolean expression based on values contained in columns. The boolean expression has to be enclosed between BEGIN and END
Example:
-r BEGINs0=='Ruby'&&n1<1||d2==Date.new(2014,6,17)END
- s0 - string in column 0
- n1 - number in column 1
- d2 - date in column 2
-
list
shows the directory of the script file (dir: true
) and has the flag all to show all scripts, that is insert files and Ruby files -
When counting columns with count the column headers are sorted alphabetically. No it is possible to set
sort: false
to keep the column headers in the sequence they are specified
- In counter
sort: false
didn't work with column filters likecols: "1,2"
. Now all filters work - Sorter now accepts a start row where to start sorting. Rows before the start row are added on top of the sorted file
sycsvpro -f infile analyze
now lists the columns with sample data- Add
params
method to Dsl that retrieves the params provided in the execute command:sycsvpro execute script.rb method infile param1 param2
- Add
clean\_up
to Dsl that takes files to be deleted after the script has run:clean\_up(%w{file1 file2})
-
A new Table class is available with following features
- Create dynamic headline columns based on source table data
- Associate values to multi keys
- Create values based on arithmetic operations of source table data
Example
sycsvpro -f in.csv -o out.csv table -h "c4,c5,c0=~/\\.(\\d{4})/" -k "c4,c5" -c "c0=~/\\.(\\d{4})/:+n1"
- h the header is created from the source table header of column 4 and 5. Another header column is created dynamicall based on the year part of a date in column 0
- k the key is based on source table of column 4 and 5
- c the column operation is in the form HeaderName:Operation. In this case the HeaderName is dynamically determined based on column 0 and added the value of column 1 to this column that is associated to the key
c4, n4, d4 are string, number and date values respectively
- Add a sum row after the heading or at the end of file like so
sycsvpro -f in.csv -o out.csv table -h "c4,c5,c0=~/\\.(\\d{4})/" -k "c4,c5" -c "c0=~/\\.(\\d{4})/:+n1" -s "c0=~/\\.(\\d{4})/"
This will sum up the dynamically created column.
- Commas within columns expression are now ignored while splitting columns of table columns
- Table takes a number format now with
--nf DE
which will convert numbers from DE locale like 1.000,00 to 1000.00 - Table uses a precision for numbers. Default is 2. Can be assigned with
pr: 2
- Calc can now be used not to only do arithmetic operations on columns but also string operations. Ultimately any valid Ruby command can be used to process a column value `sycsvpro -f customer.csv -o customer-number.csv calc -h "Customer_ID,Customer,Country" -r "1-eof" -c "2:s0.scan(/^([A-Z]+)\//).flatten[0], 0:s0.scan(/(?<=\/)(.*)$/).flatten[0],1:s1"
- Join is a new class that joins to tables based on a joint column value
sycsvpro -f infile.csv -o outfile.csv join source.csv -c "2,4" -j "1=3" -p "1,3" -h "*" -i "A,B"
This will join infile.csv with source.csv based on the join columns (j "1=3"). From source.csv columns 2 and 4 (-c "2,4") will be inserted at column positions 1 and 3 (-p "1,3"). The header will be used from the infile.csv (-h "*") supplemented by the columns A and B (-i "A,B") that will also be positioned at column 1 and 3 (-p "1,3").
- Join now can join multiple key values in 1 streak
- When creating columns dynamically in count they are in arbitrary sequence.
You can now provide a switch
sort: "2"
which will sort the header from column 2 on.
- It is now possible to merge multiple files based on common headline columns
- Fix ~/.syc/sycsvpro system directory creation when no .syc directory is available
- Unique removes duplicate lines from the infile. Duplicate lines are identified by key columns
- Add unique to sycsvpro command line interface
- Optimize Mapper by only considering columns provided for mapping which should increase performance
- match_boolean_filter? in Filter now also processes strings with single quotes inside
- Tranposer tranposes rows and columns that is make columns rows and vice versa
- Calculator can now have colons inside the operation sycsvpro -f in.csv -o out.csv -c "122:+[1,3,5].inject(:+)" Previously the operation would have been cut after inject(
- A write flag in Calculator specifies which colons to add to the result.
- Calculator introduced a switch 'final_header' which indicates the header provided should not be filtered in regard to a provided 'write' flag but written to the result file as is
- Merger now doesn't require a key column that is files can be merged without key columns.
- SpreadSheet has been introduced. A spread sheet is used to conduct operations like multiplication, division, addition and subtraction between multiple files that have a table like structure. SpreadSheet can also be used to retrieve information about csv files
- When creating spread sheets from file empty rows are skipped
- To equalize column sizes of rows in spread sheets
equalize: true
flag was introduced - To distinguish between different number locales like 1.234.567,89,
1,234,567.89, 1 234 567.89 and the like a
ds
flag was introduced to spread sheet to indicate the number formatting - Optimize performance when creating spread sheets from files
- Dsl module has got 3 new methods #is_integer?, #is_float? and #str2num to convert strings that represent numbers to numericals
- Add the equalize switch to the spread sheet command line
- Optimize performance of SpreadSheet#write
- Introduce r and c arguments to SpreadSheet#write to indicate whether the row and column labels should be written to the file. Row and column labels are written per default for compatibility reasons
- Catch encoding errors when creating spread sheet from file
The class documentation can be found at rubygems and the source code at github. This might be valuable when writing scripts.