flatten

Commands ››
Parent Previous Next

The flatten command is used to flatten multiple input rows into a single input row, depending on an identical key field or fields. This is useful if you have data like average temperature data for each month in a year (not all values shown):

1990, 0
1990, 0
1990, 4
...
1990, 2
1990, 1
1991, 1
...

and you would like them to look like:

1990, 0, 0, 4, ... 2, 1
1991, 1, ...

Values are pulled together so long as the key (in this case the year) for successive input rows is the same, and the flattened row is output when the key changes.

You can also use flatten to flatten master/detail records. For example, suppose you have this data file:


Joe,Blow

2012-01-13,Phone bill,105.10

Jane,Doe

2012-01-10,Groceries,5.10

2012-01-12,Drinks,7.15


and you would like to convert it to flat CSV file that looks like this:


Joe,Blow,2012-01-13,Phone bill,105.10

Jane,Doe,2012-01-10,Groceries,5.10

Jane,Doe,2012-01-12,Drinks,7.15


You can do that with this flatten command:


csvfix flatten -me '$fields == 2'  expend.csv


which says that a master record is one that consists of two fields. The -me option specifies an expression which tests each input record;  if it evaluates to true, the record is treated as the master for the following detail records. The details are simply appended to the master.



See also: unflatten


Flag

Req'd?

Description

-k key

No

Specifies one or more key fields. Default is to use the first field as the key

-f data

No

Specifies the data fields. By default, all fields except the key are considered to be data.

-r

No

Remove the key from the output. By default the key is retained and becomes the first field(s) in the output.

-me expr

No

Specify an expression that can identify a master record in a stream of master/detail data. See above for more details. Mutually exclusive with the other options.



The following example flattens the data in the flat.csv file:

csvfix flatten data/flat.csv

producing:

"A","a1","a2","a3","a4"
"B","b1","b2"
"A","a5","a6"
"C","c1","c2","c3","c4"

Created with the Personal Edition of HelpNDoc: Write EPub books for the iPad