pivotp without aggregation

[ondohotola]

Related to #2414 given a file test.ns.csv looking like

fqdn,host
a.na,ns1.example.com
b.na,ns1.example.com
b.na,ns2.example.com
c.na,ns1.example.com
c.na,ns2.example.com
c.na,ns3.example.com

I want to produce output looking like (in this case)

fqdn,host1,host2,host3
a.na,ns1.example.com,,
b.na,ns1.example.com,ns2.example.com,
c.na,ns1.example.com,ns2.example.com,ns3.example.com

I can't seem to be able to do this with pivotp and appreciate any help and/or pointers in this regard

[jqnatividad]

Here's how to get your desired result with pivotp @ondohotola .

qsv pivotp host --index fqdn --values host --agg none test.ns.csv
fqdn,ns1.example.com,ns2.example.com,ns3.example.com
a.na,ns1.example.com,,
b.na,ns1.example.com,ns2.example.com,
c.na,ns1.example.com,ns2.example.com,ns3.example.com
(3, 4)

Here's why this works:

1. host: This is the pivot column (the <on-cols> parameter) that will create the new columns
   (i.e. ns1.example.com, ns2.example.com, ns3.example.com, etc)
2. --index fqdn: Uses the fqdn column as the row identifier
3. --values host: Specifies the host column contains the values to pivot
4. --agg none: Ensures no aggregation is performed, which maintains the order of hosts

[ondohotola]

Thank you. I’ll try it out piping to a qsv rename and will revert

[ondohotola]

Not what I wanted to achieve

so amended a.csv looks like

fqdn,host
a.na,ns1.example.com
b.na,ns1.example.com
b.na,ns2.example.com
c.na,ns1.example.com
c.na,ns2.example.com
c.na,ns3.example.com
d.na,ns1.example.com
d.na,ns2.example.com
d.na,ns3.example.com
d.na,ns4.example.com
e.na,ns1.example.com
e.na,ns5.example.com
e.na,ns6.example.com
e.na,ns7.example.com
e.na,ns8.example.com

qsv pivotp -Q host --index fqdn --values host --agg none a.csv

results in

fqdn,ns1.example.com,ns2.example.com,ns3.example.com,ns4.example.com,ns5.example.com,ns6.example.com,ns7.example.com,ns8.example.com
a.na,ns1.example.com,,,,,,,
b.na,ns1.example.com,ns2.example.com,,,,,,
c.na,ns1.example.com,ns2.example.com,ns3.example.com,,,,,
d.na,ns1.example.com,ns2.example.com,ns3.example.com,ns4.example.com,,,,
e.na,ns1.example.com,,,,ns5.example.com,ns6.example.com,ns7.example.com,ns8.example.com

with all host names in the header line instead of something like

fqdn,host1,host1,host2,host3,host4
a.na,ns1.example.com,,,,
b.na,ns1.example.com,ns2.example.com,,,
c.na,ns1.example.com,ns2.example.com,ns3.example.com,,
d.na,ns1.example.com,ns2.example.com,ns3.example.com,ns4.example.com,
e.na,ns1.example.com,ns5.example.com,ns6.example.com,ns7.example.com,ns8.example.com

or

fqdn,host1,host1,host2,host3,host4
a.na,ns1.example.com
b.na,ns1.example.com,ns2.example.com
c.na,ns1.example.com,ns2.example.com,ns3.example.com
d.na,ns1.example.com,ns2.example.com,ns3.example.com,ns4.example.com
e.na,ns1.example.com,ns5.example.com,ns6.example.com,ns7.example.com,ns8.example.com

where only as many columns as the maximum number of hosts per fqdn are generated.

The header names host# can be whatever one wants, I can easily count them.

[ondohotola]

unfortunately 2.1.0 does same.

[jqnatividad]

A smarter pivot is in the backlog #2374

group-by should not only be faster, but has more knobs to tune.

Currently, I'm using Polars' pivot - https://docs.pola.rs/api/python/stable/reference/dataframe/api/polars.DataFrame.pivot.html