Fsdb::F...ilepivot(3)

📦
version 3.9 · 3 versions
, in epel 9

💅 Fsdb::F...ilepivot(3) — aka dbfilepivot, bestie ✨

okay so this whole page lives in the User Contributed Perl Documentation section of the manual, and honestly? it's a whole vibe. buckle up 🎀

✨ NAME ✨

dbfilepivot — pivot a table, converting multiple rows into a single wide row 🦋

✨ SYNOPSIS ✨

dbfilepivot [-e empty] -k KeyField -p PivotField [-v ValueField]

✨ DESCRIPTION ✨

So you take a table and you pivot it, right? that means: multiple rows that all correspond to the same key get smooshed together into one single wide row. it's giving glow-up 💖

picture a normalized database — data with a schema like (id, attribute, value). valid, correct, very demure. but sometimes you'd way rather see it as (id, attribute1, attribute2). (for example, gnuplot's stacked histograms requires denormalized data, no cap.) Dbfilepivot converts the normalized format into the denormalized (but sometimes useful!) format. here the "id" is the key, the attribute is the "pivot", and the value is, well, the optional "value" 🫶

an example makes it so much clearer. a gradebook usually looks like:

#fsdb name hw_1 hw_2 hw_3
John       97  98  99
Paul       -   80  82

but a properly normalized format would represent it as:

#fsdb name hw score
John       1  97
John       2  98
John       3  99
Paul       2  80
Paul       3  82

this tool converts the second form into the first, when you run it as

dbfilepivot -k name -p hw -v score

or

dbfilepivot --possible-pivots='1 2 3' -k name -p hw -v score

here name is the key column that says which rows belong to the same entity, hw is the pivot column that indicates which column in the output is relevant, and score is the value that says what actually goes in the output. iconic teamwork 💅

the pivot creates a new column "key_tag1", "key_tag2", etc. for each tag — that's the contents of the pivot field in the input. then it populates those new columns with the contents of the value field in the input. slay ✨

if no value column is specified, then values are either empty or 1.

⚠️ heads up bestie: Dbfilepivot assumes all lines with the same key are adjacent in the input source, like dbmapreduce(1) with the -S option. to enforce this invariant, by default it requires input be sorted by key. no exceptions, periodt.

there is no requirement that the pivot field be sorted (provided the key field is already sorted). lowkey a relief ngl 🌸

by default, dbfilepivot makes two passes over its data, which means it needs temporary disk space equal to the input size. 😭 with the --possible-pivots option you can specify the pivots yourself, skip that second pass, and avoid the temporary data storage entirely. main character optimization energy.

memory usage is proportional to the number of unique pivot values.

the inverse of this command is dbcolsplittorows 🦋

✨ OPTIONS ✨

  • -k or --key KeyField — specify which column is the key for grouping. Required (no default). you literally cannot skip this one 🫶
  • -p or --pivot PivotField — specify which column is the key to indicate which column in the output is relevant. Required (no default).
  • -v or --value ValueField — specify which column is the value in the output. if none is given, 1 is used for the value.
  • --possible-pivots PP — specify all possible pivot values as PP, a whitespace-separated list. with this option, data is processed only once (not twice). efficiency queen 👑
  • -C S or --element-separator S — specify the separator S used to join the input's key column with its contents. (defaults to a single underscore _.)
  • -e E or --empty E — give value E as the value for empty (null) records.
  • -S or --pre-sorted — assume data is already grouped by key. provided twice, it removes the validation of this assertion. by default, we sort by key.
  • -T TmpDir or --tmpdir TmpDir — where to put tmp files. also uses environment variable TMPDIR, if -T is not specified. default is /tmp.

this module also supports the standard fsdb options, babes:

  • -d — enable debugging output.
  • -i or --input InputSource — read from InputSource, typically a file name, or "-" for standard input, or (if in Perl) a IO::Handle, Fsdb::IO or Fsdb::BoundedQueue objects.
  • -o or --output OutputDestination — write to OutputDestination, typically a file name, or "-" for standard output, or (if in Perl) a IO::Handle, Fsdb::IO or Fsdb::BoundedQueue objects.
  • --autorun or --noautorun — by default, programs process automatically, but Fsdb::Filter objects in Perl do not run until you invoke the run() method. the "--(no)autorun" option controls that behavior within Perl.
  • --help — show help.
  • --man — show full manual.

✨ SAMPLE USAGE ✨ 💖

Input:

#fsdb name hw score
John       1  97
John       2  98
John       3  99
Paul       2  80
Paul       3  82

Command:

cat data.fsdb | dbfilepivot -k name -p hw -v score

Output:

#fsdb name hw_1 hw_2 hw_3
John    97      98      99
Paul    -       80      82
#   | dbfilepivot -k name -p hw -v score

look at that transformation. literally ate. 🔥

✨ SEE ALSO ✨

Fsdb(3). dbcolmerge(1). dbcolsplittorows(1). dbcolsplittocols(1). go read them, they're all friends 🧚

✨ CLASS FUNCTIONS ✨ (for the Perl girlies 🐚)

new

$filter = new Fsdb::Filter::dbfilepivot(@arguments);

create a new dbfilepivot object, taking command-line arguments.

set_defaults

$filter->set_defaults();

internal: set up defaults.

parse_options

$filter->parse_options(@ARGV);

internal: parse command-line arguments.

_find_possible_pivots

$filter->_find_possible_pivots();

internal: scan input data to find all possible pivot values.

returns npivots, pivots_aref.

_given_possible_pivots

$filter->_given_possible_pivots();

internal: parse option of possible pivots.

returns npivots, pivots_href.

setup

$filter->setup();

internal: setup, parse headers.

run

$filter->run();

internal: run over each rows.

copyright (C) 2011-2024 by John Heidemann <johnh@isi.edu> 👑

this program is distributed under terms of the GNU general public license, version 2. see the file COPYING with the distribution for details.

okay that's the whole page bestie, ily, go pivot something 🌈💜

🎀 this was Fsdb::F...ilepivot(3) from perl v5.32.1, dated 2024-08-02 — signed, sealed, slayed 💅✨
📦 where this page comes from