pivot on a single file a-la fold, except into columns.

[xxxserxxx]

I'd like to be able to merge rows sharing a (possibly complex) key within a single (optionally multiple) file.

Example data:

1,a
1,b
2,x
2,y

This is a common, normalized representation of data. I'd like to be able to run:

$ echo "1,a,A\n1,b,B\n2,x,X\n2,y,Y\n" | csvtk fold -H -f 1 -C -v 2

where -C stands in for the new argument that creates new columns for each row:

1,a,b,A,B
2,x,y,X,Y

or

1,a,A,b,B
2,x,X,y,Y

Whether row columns are appended sequentially, or interleved, is unimportant as transforming column orders after the operation is trivial.

It would be acceptable if, when csvtk found that the result would produce rows of different lengths -- for example, an ID has more or less than the usual number of rows, that it would generate an error.

You can almost get there with fold and sep, except there's no way to include additional columns in fold, so it only works on two columns:

$ echo "1,a,A\n1,b,B\n2,x,X\n2,y,Y\n" | csvtk fold -H -f 1 -s ';' -v 2 | csvtk sep -H -s ';' -f 2 -R
1,a,b
2,x,y

The data in the additional rows is lost.

One IRL use case for this is with ledger csv, which produces output like this:

transaction uuid,date,payee,amount
aaa123,2024-03-01,Bill's Garage,50
aaa123,2024-03-01,Bank account,50
456abc,2024-03-05,Solar Power Monopoly,200
456abc,2024-03-05,Credit card,200

This is a double-entry system, so there's always a credit line and a corresponding debit line. What I want is:

transaction uuid,date,payee,source,amount
aaa123,2024-03-01,Bill's Garage,Bank account,50
456abc,2024-03-05,Solar Power Monopoly,Credit card,200

However, an MVP would produce:

transaction uuid,date,payee 1,payee 2,amount 1,amount 2
aaa123,2024-03-01,Bill's Garage,Bank account,50
456abc,2024-03-05,Solar Power Monopoly,Credit card,200

I tried to not present an X:Y problem, but I think a solution would be allowing fold and sep to operate on multiple -v (and -f, respectively) columns,

[shenwei356]

You can almost get there with fold and sep, except there's no way to include additional columns in fold, so it only works on two columns:

We can use sed to convert it to a two-column tab-delimited data and use tab2csv to get it back.

$ echo -ne "1,a,A\n1,b,B\n2,x,X\n2,y,Y\n"  \
    | sed 's/,/\t/' \
    | csvtk fold -Ht -f 1 -s ',' -v 2 \
    | csvtk sep -Ht -s , -f 2 -R  \
    | csvtk tab2csv 

1,a,A,b,B
2,x,X,y,Y

I think a solution would be allowing fold and sep to operate on multiple -v (and -f, respectively) columns,

It would get complex.

[ggrothendieck]

Assume this is the input ledger.csv:

transaction uuid,date,payee,amount
aaa123,2024-03-01,Bill's Garage,50
aaa123,2024-03-01,Bank account,50
456abc,2024-03-05,Solar Power Monopoly,200
456abc,2024-03-05,Credit card,200

and that we define a gawk program, seq.awk, which adds sequence numbers within transaction uuid:

# seq.awk:
BEGIN { OFS = "," }
NR == 1 { print $0, "n" }
NR > 1 { if ($1 == prev) ++n; else n=1; prev = $1; print $0, "payee " n }

Then we can run:

gawk --csv -f seq.awk ledger.csv | csvtk spread -k n -v payee

giving

transaction uuid,date,amount,payee 1,payee 2
aaa123,2024-03-01,50,Bill's Garage,Bank account
456abc,2024-03-05,200,Solar Power Monopoly,Credit card

I didn't notice anything in csvtk for adding sequence numbers by group but if it exists we could use that in place of the gawk program.

[shenwei356]

@ggrothendieck, It should be a different issue. #322

[xxxserxxx]

Solutions outside of csvtk are plentiful; there's a clipivot project that pivots CSV data, and I wrote a fairly trivial program to do this - the code is not complex, and fold is doing most it already. I'm trying to reduce the number of externally programs necessary when transforming CSV, and as you know, once you start touching csv with tools like sed, you're asking for well-formed problems. What about quoted cells, with extra commas in them? This is why we turn to csvtk in the first place, isn't it? Because it understands the format, while sed does not?

I'm very aware of feature creep, but csvtk generates plots and pivots data in the process, so "minimalist" doesn't seem to be an objective.

Would you accept a patch for this?

[shenwei356]

The final table would soon drive the reader mad when there are more than two distinct values of payee and amount.

$ csvtk pretty ledger.csv 
transaction uuid   date         payee                  amount
----------------   ----------   --------------------   ------
aaa123             2024-03-01   Bill's Garage          50    
aaa123             2024-03-01   Bill's Garage          100   
aaa123             2024-03-01   Bank account           70    
456abc             2024-03-05   Solar Power Monopoly   100   
456abc             2024-03-05   Solar Power Monopoly   200   
456abc             2024-03-05   Credit card            200 

$ cat ledger.csv \
    | csvtk mutate -f 'transaction uuid' -n pn  \
    | csvtk mutate -f 'transaction uuid' -n an  \
    | csvtk replace -f pn -p .+ -r 'payee {gnr}' -g 'payee' \
    | csvtk replace -f an -p .+ -r 'amount {enr}' -g 'amount' \
    | csvtk spread -k pn -v payee \
    | csvtk spread -k an -v amount \
    | csvtk pretty -S round
╭------------------┬------------┬----------------------┬----------------------┬----------┬----------┬----------┬----------╮
| transaction uuid | date       | payee 1              | payee 2              | amount 1 | amount 2 | amount 3 | amount 4 |
├==================┼============┼======================┼======================┼==========┼==========┼==========┼==========┤
| aaa123           | 2024-03-01 | Bill's Garage        |                      | 50       |          |          |          |
├------------------┼------------┼----------------------┼----------------------┼----------┼----------┼----------┼----------┤
| aaa123           | 2024-03-01 |                      | Bill's Garage        |          | 100      |          |          |
├------------------┼------------┼----------------------┼----------------------┼----------┼----------┼----------┼----------┤
| aaa123           | 2024-03-01 | Bank account         |                      |          |          | 70       |          |
├------------------┼------------┼----------------------┼----------------------┼----------┼----------┼----------┼----------┤
| 456abc           | 2024-03-05 | Solar Power Monopoly |                      |          | 100      |          |          |
├------------------┼------------┼----------------------┼----------------------┼----------┼----------┼----------┼----------┤
| 456abc           | 2024-03-05 | Credit card          | Solar Power Monopoly |          |          |          | 200      |
╰------------------┴------------┴----------------------┴----------------------┴----------┴----------┴----------┴----------╯

[xxxserxxx]

On Wed May 28, 2025 at 3:02 AM CDT, Wei Shen wrote: shenwei356 left a comment (shenwei356/csvtk#320) The final table would soon drive the reader mad when there are more than two distinct values of payee and amount.

Probably. That seems like a user issue, though: if they're going to construct a giant table, that's what they're signing up for, ĉu ne? I was going to say that ledger is double-entry and will have only a single payer and payee, but that's not true. Transactions can be split into multiple categories, IIRC -- e.g. a grocery bill can be categorized into "food", "consumables", "liquor", or whatever. So it could, indeed, end up with multiple columns. Which is beside the point, since `ledger` is just a specific use case for the general feature. `xan` has some support for this, and while trying to get what I wanted I was mixing xan, csvtk, and ended up with an invocation that accidentally summoned a demon: ``` ledger -f everything/2024.dat --period 2024 csv --csv-format "%(id),%D,%A,%t\n" tag deductable | \ csvtk sort -H -k 2 -k 1 | \ csvtk uniq -H -f 1,2,3 | \ xan implode -n 2,3 --cmp 0 | \ rg Deductable | \ xan explode 2 | \ rg -v Deductable | \ xan explode 3 | \ uniq | \ csvtk grep -H -f 4 -v -r -p '-\d+' | \ xan select -n '!0' | \ sed 's/| .*//' | \ csvtk summary -H -f 3:sum -g2 ``` And it still didn't give the correct results. I'm going to have to figure this out before tax season 2026, but I have a feeling I'm going to have to go back to my bespoke pivot program. Everything between the second and eleventh line was done with a single, custom, 36-line collation Go program.

…

--- SER Sean E. Russell Age: age195vpft7nzsy83medxagqqsge0lrcuf9txe3z2znlu2wsk69cdu4sx8nfvp GPG key: https://ser1.net/.well-known/pgp.asc