Table(3pm)

📦
version 1.78 · 2 versions
  • 1.78 · debian bookworm, debian trixie, ubuntu noble, ubuntu resolute, ubuntu stonking (latest)
  • 1.78 · ubuntu jammy
, in debian bookworm, debian trixie, ubuntu noble, ubuntu resolute, ubuntu stonking

💖 NAME 💖 — Table(3pm) / Data::Table

Bestie, welcome to the Table(3pm) manual from the User Contributed Perl Documentation! 💅 This whole page is about Data::Table — a Data type related to database tables, spreadsheets, CSV/TSV files, HTML table displays, etc. No cap, it's iconic.

🎀 SYNOPSIS 🎀

Okay so first, the news: The package now includes "Perl Data::Table Cookbook" (PDF), which may serve as a better learning material. To download the free Cookbook, visit https://sites.google.com/site/easydatabase/. That's also inside the code block below, but I'm just hyping you up. ✨

         News: The package now includes "Perl Data::Table Cookbook" (PDF), which may serve as a better learning material.
         To download the free Cookbook, visit https://sites.google.com/site/easydatabase/

         # some cool ways to use Table.pm
         use Data::Table;

         $header = ["name", "age"];
         $data = [
           ["John", 20],
           ["Kate", 18],
           ["Mike", 23]
         ];
         $t = Data::Table->new($data, $header, 0);     # Construct a table object with
                                               # $data, $header, $type=0 (consider
                                               # $data as the rows of the table).
         print $t->csv;                        # Print out the table as a csv file.

         $t = Data::Table::fromCSV("aaa.csv");       # Read a csv file into a table object
         ### Since version 1.51, a new method fromFile can automatically guess the correct file format
         # either CSV or TSV file, file with or without a column header line
         # e.g.
         #   $t = Data::Table::fromFile("aaa.csv");
         # is equivalent.
         print $t->html;                       # Display a 'portrait' HTML TABLE on web.

         use DBI;
         $dbh= DBI->connect("DBI:mysql:test", "test", "") or die $DBI::errstr;
         my $minAge = 10;
         $t = Data::Table::fromSQL($dbh, "select * from mytable where age >= ?", [$minAge]);
                                               # Construct a table form an SQL
                                               # database query.

         $t->sort("age", 0, 0);                # Sort by col 'age',numerical,ascending
         print $t->html2;                      # Print out a 'landscape' HTML Table.

         $row = $t->delRow(2);                 # Delete the third row (index=2).
         $t->addRow($row, 4);                  # Add the deleted row back as fifth row.
         @rows = $t->delRows([0..2]);          # Delete three rows (row 0 to 2).
         $col = $t->delCol("age");             # Delete column 'age'.
         $t->addCol($col, "age",2);            # Add column 'age' as the third column
         @cols = $t->delCols(["name","phone","ssn"]);
                                               # Delete 3 columns at the same time.

         $name =  $t->elm(2,"name");           # Element access
         $t2=$t->subTable([1, 3..4],['age', 'name']);
                                               # Extract a sub-table

         $t->rename("Entry", "New Entry");     # Rename column 'Entry' by 'New Entry'
         $t->replace("Entry", [1..$t->nofRow()], "New Entry");
                                               # Replace column 'Entry' by an array of
                                               # numbers and rename it as 'New Entry'
         $t->swap("age","ssn");                # Swap the positions of column 'age'
                                               # with column 'ssn' in the table.

         $t->colMap('name', sub {return uc});  # Map a function to a column
         $t->sort('age',0,0,'name',1,0);       # Sort table first by the numerical
                                               # column 'age' and then by the
                                               # string column 'name' in ascending
                                               # order
         $t2=$t->match_pattern('$_->[0] =~ /^L/ && $_->[3]<0.2');
                                               # Select the rows that matched the
                                               # pattern specified
         $t2=$t->match_pattern_hash('$_{"Amino acid"} =~ /^L-a/ && $_{"Grams \"(a.a.)\""}<0.2'));
                 # use column name in the pattern, method added in 1.62
         $t2=$t->match_string('John');         # Select the rows that matches 'John'
                                               # in any column

         $t2=$t->clone();                      # Make a copy of the table.
         $t->rowMerge($t2);                    # Merge two tables
         $t->colMerge($t2);

         $t = Data::Table->new(                 # create an employ salary table
           [
             ['Tom', 'male', 'IT', 65000],
             ['John', 'male', 'IT', 75000],
             ['Tom', 'male', 'IT', 65000],
             ['John', 'male', 'IT', 75000],
             ['Peter', 'male', 'HR', 85000],
             ['Mary', 'female', 'HR', 80000],
             ['Nancy', 'female', 'IT', 55000],
             ['Jack', 'male', 'IT', 88000],
             ['Susan', 'female', 'HR', 92000]
           ],
           ['Name', 'Sex', 'Department', 'Salary'], 0);

         sub average {  # this is an subroutine calculate mathematical average, ignore NULL
           my @data = @_;
           my ($sum, $n) = (0, 0);
           foreach $x (@data) {
             next unless $x;
             $sum += $x; $n++;
           }
           return ($n>0)?$sum/$n:undef;
         }

         $t2 = $t->group(["Department","Sex"],["Name", "Salary"], [sub {scalar @_}, \&average], ["Nof Employee", "Average Salary"]);
         # For each (Department,Sex) pair, calculate the number of employees and average salary
         $t2 = $t2->pivot("Sex", 0, "Average Salary", ["Department"]);
         # Show average salary information in a Department by Sex spreadsheet

🦋 ABSTRACT 🦋

This perl package uses perl5 objects to make it easy for manipulating spreadsheet data among disk files, database, and Web publishing. 💖

A table object contains a header and a two-dimensional array of scalars. Four class methods Data::fromFile, Data::Table::fromCSV, Data::Table::fromTSV, and Data::Table::fromSQL allow users to create a table object from a CSV/TSV file or a database SQL selection in a snap. ✨

Table methods provide basic access, add, delete row(s) or column(s) operations, as well as more advanced sub-table extraction, table sorting, record matching via keywords or patterns, table merging, and web publishing. Data::Table class also provides a straightforward interface to other popular Perl modules such as DBI and GD::Graph. 💅

The most updated version of the Perl Data::Table Cookbook is available at https://sites.google.com/site/easydatabase/.

We use Data::Table instead of Table, because Table.pm has already been used inside PerlQt module in CPAN. 😭

🌸 INTRODUCTION 🌸

A table object has three data members:

  1. $data: a reference to an array of array-references. It's basically a reference to a two-dimensional array.
  2. $header: a reference to a string array. The array contains all the column names.
  3. $type = 1 or 0. 1 means that @$data is an array of table columns (fields) (column-based); 0 means that @$data is an array of table rows (records) (row-based);

Row-based/Column-based are two internal implementations for a table object. E.g., if a spreadsheet consists of two columns lastname and age. In a row-based table, $data = [ ['Smith', 29], ['Dole', 32] ]. In a column-based table, $data = [ ['Smith', 'Dole'], [29, 32] ]. 🦋

Two implementations have their pros and cons for different operations. Row-based implementation is better for sorting and pattern matching, while column-based one is better for adding/deleting/swapping columns. ✨

Users only need to specify the implementation type of the table upon its creation via Data::Table::new, and can forget about it afterwards. Implementation type of a table should be considered volatile, because methods switch table objects from one type into another internally. Be advised that row/column/element references gained via table::rowRef, table::rowRefs, table::colRef, table::colRefs, or table::elmRef may become stale after other method calls afterwards. ⚠️

For those who want to inherit from the Data::Table class, internal method table::rotate is used to switch from one implementation type into another. There is an additional internal assistant data structure called colHash in our current implementation. This hash table stores all column names and their corresponding column index number as key-value pairs for fast conversion. This gives users an option to use column name wherever a column ID is expected, so that user don't have to use table::colIndex all the time. E.g., you may say $t->rename('oldColName', 'newColName') instead of $t->rename($t->colIndex('oldColName'), 'newColIdx'). 💜

👑 DESCRIPTION 👑

💅 Field Summary 💅

  • data refto_arrayof_refto_array — contains a two-dimensional spreadsheet data.
  • header refto_array — contains all column names.
  • type 0/1 — 0 is row-based, 1 is column-based, describe the orientation of @$data.

🎀 Package Variables 🎀

  • $Data::Table::VERSION
  • @Data::Table::OK — see table::match_string, table::match_pattern, and table::match_pattern_hash. Since 1.62, we recommend you to use $table->{OK} instead, which is a local array reference.
  • @Data::Table::MATCH — see table::match_string, table::match_pattern, and table::match_pattern_hash. Since 1.67, we return the matched row indices in an array. Data::Table::MATCH is this array reference. Here is an example of setting a max price of 20 to all items with UnitPrice > 20.
$t_product->match_pattern_hash('$_{UnitPrice} > 20');
$t_product->setElm($t_product->{MATCH}, 'UnitPrice', 20);
  • %Data::Table::DEFAULTS — Store default settings, currently it contains CSV_DELIMITER (set to ','), CSV_QUALIFER (set to '"'), and OS (set to 0). see table::fromCSV, table::csv, table::fromTSV, table::tsv for details.

✨ Class Methods ✨

Syntax: return_type method_name ( [ parameter [ = default_value ]] [, parameter [ = default_value ]] )

If method_name starts with table::, this is an instance method, it can be used as $t->method( parameters ), where $t is a table reference.

If method_name starts with Data::Table::, this is a class method, it should be called as

Data::Table::method

e.g., $t = Data::Table::fromCSV("filename.csv").

Conventions for local variables:

  • colID: either a numerical column index or a column name;
  • rowIdx: numerical row index;
  • rowIDsRef: reference to an array of column IDs;
  • rowIdcsRef: reference to an array of row indices;
  • rowRef, colRef: reference to an array of scalars;
  • data: ref_to_array_of_ref_to_array of data values;
  • header: ref to array of column headers;
  • table: a table object, a blessed reference.

🎀 Table Creation 🎀

✨ table Data::Table::new ( $data = [], $header = [], $type = 0, $enforceCheck = 1)

okay bestie, this is THE origin story — you're birthing a whole new table. 💖 It returns a table object when it goes well, and undef when it does not.

  • $data — points at the spreadsheet data.
  • $header — points at an array of column names. Real talk: before version 1.69, a column name had to have at least one non-digit character. Since version 1.69 that rule got relaxed. So yes, integer and numeric column names are now accepted, BUT — and this is the plot twist — when you access a column by integer, it gets interpreted as a column name first. 👀
  • $type — 0 or 1 for row-based/column-based spreadsheet.
  • $enforceCheck — 1/0 to turn on/off the initial checking on the size of each row/column, so it can make sure the $data argument actually points to a valid structure. no cap.

In 1.63 we introduced the constants Data::Table::ROW_BASED and Data::Table::COL_BASED as synonyms for $type. 🫶

And if you want an empty Data::Table, you literally just do this:

new Data::Table([], [], Data::Table::ROW_BASED);

✨ table table::subTable ($rowIdcsRef, $colIDsRef, $arg_ref)

make a new table that's a subset of the original one — like a little curated edit of your data. ✂️ Returns a table object.

  • $rowIdcsRef — points to an array of row indices (or a true/false row mask array).
  • $colIDsRef — points to an array of column IDs.

The function makes a copy of the selected elements from the original table. If $rowIdcsRef or $colIDsRef is undefined, that gets interpreted as all rows or all columns — very "we're keeping everything, actually" of it. 💅

heads up: the elements in $colIDsRef may be modified as a side effect before version 1.62. That got fixed in 1.62. so if you're on something ancient, beware.

If $arg_ref->{useRowMask} is set to 1, then $rowIdcsRef is a true/false row mask array, where rows marked as TRUE will be returned. Row mask array is typically the Data::Table::OK set by match_string/match_pattern/match_pattern_hash methods. 🌈

✨ table table::clone

makes a clone of the original. returns a table object, and it's exactly equivalent to table::subTable(undef,undef). identical twin energy, we love to see it. 👑

✨ table Data::Table::fromCSV ($name_or_handler, $includeHeader = 1, $header = ["col1", ... ], {OS=>$Data::Table::DEFAULTS{'OS'}, delimiter=>$Data::Table::DEFAULTS{'CSV_DELIMITER'}, qualifier=>$Data::Table::DEFAULTS{'CSV_QUALIFIER'}, skip_lines=>0, skip_pattern=>undef, encoding=>$Data::Table::DEFAULTS{'ENCODING'}})

create a table from a CSV file. returns a table object. 📄✨

  • $name_or_handler — the CSV file name or an already opened file handler. If a handler is used, it's not closed upon return. To read from STDIN, use Data::Table::fromCSV(\*STDIN, 1).
  • $includeHeader — 0 or 1 to ignore/interpret the first line in the file as column names. If it's set to 0, the array in $header is used. If $header is not supplied, the default column names are "col1", "col2", ...
  • optional named argument OS — specifies under which operating system the CSV file was generated. 0 for UNIX, 1 for PC and 2 for MAC. If not specified, $Data::Table::DEFAULTS{'OS'} is used, which defaults to UNIX. Basically linebreak is defined as "\n", "\r\n" and "\r" for three systems, respectively. 🌍
  • optional name argument delimiter and qualifier — let you replace comma and double-quote by other meaningful single characters.
Exception: if the delimiter or the qualifier is a special symbol in regular expression, you must escape it by '\'. For example, in order to use pipe symbol as the delimiter, you must specify the delimiter as '\|'. don't forget or it's giving chaos. 💀
  • optional name argument skip_lines — lets you specify how many lines in the csv file should be skipped, before the data are interpretted.
  • optional name argument skip_pattern — lets you specify a regular expression. Lines that match the regular expression will be skipped.
  • optional name argument encoding — lets you specify an encoding method of the csv file. This option is added to fromCSV, fromTSV, fromFile since version 1.69. 🦋

The following example reads a DOS format CSV file and writes a MAC format:

$t = Data::Table:fromCSV('A_DOS_CSV_FILE.csv', 1, undef, {OS=>1});
$t->csv(1, {OS=>2, file=>'A_MAC_CSV_FILE.csv'});
open(SRC, 'A_DOS_CSV_FILE.csv') or die "Cannot open A_DOS_CSV_FILE.csv to read!";
$t = Data::Table::fromCSV(\*SRC, 1);
close(SRC);

The following example reads a non-standard CSV file with : as the delimiter, ' as the qaulifier

my $s="col_A:col_B:col_C\n1:2, 3 or 5:3.5\none:'one:two':'double\", single'''";
open my $fh, "<", \$s or die "Cannot open in-memory file\n";
my $t_fh=Data::Table::fromCSV($fh, 1, undef, {delimiter=>':', qualifier=>"'"});
close($fh);
print $t_fh->csv;
# convert to the standard CSV (comma as the delimiter, double quote as the qualifier)
# col_A,col_B,col_C
# 1,"2, 3 or 5",3.5
# one,one:two,"double"", single'"
print $t->csv(1, {delimiter=>':', qualifier=>"'"}); # prints the csv file use the original definition

The following example reads bbb.csv file (included in the package) by skipping the first line (skip_lines=>1), then treats any line that starts with '#' (or space comma) as comments (skip_pattern=>'^\s*#'), use ':' as the delimiter.

$t = Data::Table::fromCSV("bbb.csv", 1, undef, {skip_lines=>1, delimiter=>':', skip_pattern=>'^\s*#'});

Use the optional name argument encoding to specify file encoding method.

$t = Data::Table::fromCSV("bbb.csv", 1, undef, {encoding=>'UTF-8'});

✨ table table::fromCSVi ($name, $includeHeader = 1, $header = ["col1", ... ])

Same as Data::Table::fromCSV. However, this is an instant method (that's what the 'i' stands for), which can be inherited. 🧚

✨ table Data::Table::fromTSV ($name, $includeHeader = 1, $header = ["col1", ... ], {OS=>$Data::Table::DEFAULTS{'OS'}, skip_lines=>0, skip_pattern=>undef, transform_element=>1, encoding=>$Data::Table::DEFAULTS{'ENCODING'}})

create a table from a TSV file. returns a table object. 🌸

  • $name — the TSV file name or an already opened file handler. If a handler is used, it's not closed upon return. To read from STDIN, use Data::Table::fromTSV(\*STDIN, 1).
  • $includeHeader — 0 or 1 to ignore/interpret the first line in the file as column names. If it's set to 0, the array in $header is used. If $header is not supplied, the default column names are "col1", "col2", ...
  • optional named argument OS — specifies under which operating system the TSV file was generated. 0 for UNIX, 1 for PC and 2 for MAC. If not specified, $Data::Table::DEFAULTS{'OS'} is used, which defaults to UNIX. Basically linebreak is defined as "\n", "\r\n" and "\r" for three systems, respectively.
Exception: if the delimiter or the qualifier is a special symbol in regular expression, you must escape it by '\'. For example, in order to use pipe symbol as the delimiter, you must specify the delimiter as '\|'.
  • optional name argument skip_lines — lets you specify how many lines in the csv file should be skipped, before the data are interpretted.
  • optional name argument skip_pattern — lets you specify a regular expression. Lines that match the regular expression will be skipped.
  • optional name argument transform_element — lets you switch on/off \t to tab, \N to undef (etc.) transformation. See TSV FORMAT for details. However, elements are always transformed when export table to tsv format, because not escaping an element containing a tab will be disasterous. 😭
  • optional name argument encoding — enables one to provide an encoding method when open the tsv file.

See similar examples under Data::Table::fromCSV;

Note: read "TSV FORMAT" section for details.

✨ table table::fromTSVi ($name, $includeHeader = 1, $header = ["col1", ... ])

Same as Data::Table::fromTSV. However, this is an instant method (that's what the 'i' stands for), which can be inherited. 🫶

✨ table Data::Table::fromFile ($file_name, $arg_ref = {linesChecked=>2, allowNumericHeader=>0, encoding=>$Data::Table::DEFAULTS{'ENCODING'}})

create a table from a text file. returns a table object. 📂

  • $file_name — the file name (cannot take a file handler). nope, not even a little.
  • linesChecked — the first number of lines used for guessing the input format. The delimiter will have to produce the same number of columns for these lines. By default only check the first 2 lines, 0 means all lines in the file.
  • $arg_ref — can take additional parameters, such as OS, has_header, delimiter, transform_element, etc. Encoding allows one to specify encoding methods used to open the file, which defaults to UTF-8.

fromFile is added after version 1.51. It relies on the following new methods to automatically figure out the correct file format in order to call fromCSV or fromTSV internally:

fromFileGuessOS($file_name, {encoding=>'UTF-8'})
  returns integer, 0 for UNIX, 1 for PC, 2 for MAC
fromFileGetTopLines($file_name, $os, $lineNumber, {encoding=>'UTF-8'}) # $os defaults to fromFileGuessOS($file_name), if not specified
  returns an array of strings, each string represents each row with linebreak removed.
fromFileGuessDelimiter($lineArrayRef)       # guess delimiter from ",", "\t", ":";
  returns the guessed delimiter string.
fromFileIsHeader($line_concent, $delimiter, $allowNumericHeader) # $delimiter defaults to $Data::Table::DEFAULTS{'CSV_DELIMITER'}
  returns 1 or 0.

It first ask fromFileGuessOS to figure out which OS (UNIX, PC or MAC) generated the input file. The fetch the first linesChecked lines using fromFileGetTopLines. It then guesses the best delimiter using fromFileGuessDelimiter, then it checks if the first line looks like a column header row using fromFileIsHeader. Since fromFileGuessOS and fromFileGetTopLines needs to open/close the input file, these methods can only take file name, not file handler. If user specify formatting parameters in $arg_ref, the routine will skip the corresponding guess work. At the end, fromFile simply calls either fromCSV or fromTSV with $arg_ref forwarded. So if you call fromFile({transform_element=>0}) on a TSV file, transform_elment will be passed onto fromTSV calls internally. 💜

  • fromFileGuessOS — it goes hunting for the linebreak that gives the shortest first line (and if there's a tie, priority order is UNIX, PC, MAC). 🦋
  • fromFileGuessDelimiter — this one vibes off the assumption that the correct delimiter will produce an equal number of columns for the given rows. If multiple matches happen, it picks the delimiter that gives the maximum number of columns. If none matches at all? It just returns the default delimiter. 💅
  • fromFileIsHeader — works on the assumption that no column header can be empty or a numeric value. However!! if we allow numeric column names (especially integer column names), set {allowNumericHeader => 1}. 🌸
  • table Data::Table::fromSQL ($dbh, $sql, $vars) — bestie, this creates a table from the result of an SQL selection query. 🎀 It returns a table object upon success, or undef otherwise.
    • $dbh: a valid database handler. Typically $dbh is obtained from DBI->connect, see "Interface to Database" or DBI.pm.
    • $sql: an SQL query string or a DBI::st object (starting in version 1.61).
    • $vars: optional reference to an array of variable values, required if $sql contains ?s which need to be replaced by the corresponding variable values upon execution, see DBI.pm for details.
    • Hint: in MySQL, Data::Table::fromSQL($dbh, 'show tables from test') will also create a valid table object. 🔥

Data::Table::fromSQL now can take DBI::st instead of a SQL string. This is introduced, so that variable binding (such as CLOB/BLOB) can be done outside the method, for example:

  $sql = 'insert into test_table (id, blob_data) values (1, :val)';
  $sth = $dbh->prepare($sql);
  $sth->bind_param(':val', $blob, {ora_type => SQLT_BIN});
  Data::Table::fromSQL($dbh, $sth);
  • table Data::Table::fromSQLi ($dbh, $sql, $vars) — Same as Data::Table::fromSQL. However, this is an instant method (that's what 'i' stands for), whic h can be inherited. ✨

👑 Table Access and Properties 👑

  • int table::colIndex ($colID) — translates a column name into its numerical position, and the first column has index 0 just like any perl array. Returns -1 for invalid column names. 😭

Since 1.69, we allow an integer to be used as a column header. The integer $colID will first be checked against column names, and if it matched, the corresponding column index is returned. E.g., if the column name for the 3rd column is "1", colIndex(1) will return 2 instead of 1! In such case, if one need to access the second column, one has to access it by column name, i.e., $t->col(($t->header)[1]). 🫠

  • int table::nofCol — return number of columns. 💫
  • int table::nofRow — return number of rows. 💫
  • int table::lastCol — return the index of the last columns, i.e., nofCol - 1.
  • int table::lastRow — return the index of the last rows, i.e., nofRow - 1; This is syntax sugar. 🍓
   # these two are equivalent
   foreach my $i (0 .. $t->lastRow)
   foreach my $i (0 .. $t->nofRow - 1)
  • bool table::isEmpty — return whether the table has any column, introduced in 1.63.
  • bool table::hasCol($colID) — returns whether the colID is a table column, introduced in 1.63.
  • bool table::colName($colNumericIndex) — returns the column name for a numeric column index, notice the first column has an index of 0. Introduced in 1.68. 👑
  • scalar table::elm ($rowIdx, $colID) — return the value of a table element at [$rowIdx, $colID], undef if $rowIdx or $colID is invalid.
  • refto_scalar table::elmRef ($rowIdx, $colID) — return the reference to a table element at [$rowIdx, $colID], to allow possible modification. It returns undef for invalid $rowIdx or $colID.
  • array table::header ($header) — Without argument, it returns an array of column names. Otherwise, use the new header. 🎀
  • int table::type — return the implementation type of the table (row-based/column-based) at the time, be aware that the type of a table should be considered as volatile during method calls. ⚠️

🌈 Table Formatting 🌈

  • string table::csv ($header, {OS=>$Data::Table::DEFAULTS{'OS'}, file=>undef, delimiter=>$Data::Table::DEFAULTS{'CSV_DELIMITER'}, qualifier=>$Data::Table::DEFAULTS{'CSV_QAULIFIER'}}) — return a string corresponding to the CSV representation of the table. $header controls whether to print the header line, 1 for yes, 0 for no. The optional named argument OS specifies for which operating system the CSV file is generated: 0 for UNIX, 1 for P C and 2 for MAC. If not specified, $Data::Table::DEFAULTS{'OS'} is used. Basically the linebreak is defined as "\n", "\r\n" and "\r" for three systems, respectively. If 'file' is given, the csv content will be written into it, besides returning the string. One may specify custom delimiter and qualifier if the other than default are desired. ✨
  • string table::tsv — return a string corresponding to the TSV representation of the table. $header controls whether to print the header line, 1 for yes, 0 for no. The optional named argument OS specifies for which operating system the TSV file is generated: 0 for UNIX, 1 for P C and 2 for MAC. If not specified, $Data::Table::DEFAULTS{'OS'} is used. Basically the linebreak is defined as "\n", "\r\n" and "\r" for three systems, respectively. If 'file' is given, the tsv content will be written into it, besides returning the string. 🦋
Note: read the "TSV FORMAT" section for details. 📖
  • string table::html ($colorArrayRef_or_colorHashRef = ["#D4D4BF","#ECECE4","#CCCC99"], $tag_tbl = {border => '1'}, $tag_tr = {align => 'left'}, $tag_th = {align => 'center'}, $tag_td = {col3 => 'align="right" valign="bottom"', 4 => 'align="left"'}, $l_portrait = 1, $callback = undef ) — return a string corresponding to a 'Portrait/Landscape'-style html-tagged table. 🎨
    • $colorArrayRef_or_colorHashRef: If a hash reference is provided, it will take three CSS class names for odd data rows, even data rows and for the header row. The default hash is {even=>"data_table_even", odd=>"data_table_odd", header=>"data_table_header"). If a hash reference is not found, a reference to an array of three color strings is expected to provided for backgrounds for even-row records, odd-row records, and -der row, respectively. A default color array ("#D4D4BF","#ECECE4","#CCCC99") will be used if $colors isn't defined.
    • Since version 1.74, users can prevent default coloring by passing in a color array reference ["", "", ""]. 🙈
    • Before version 1.59, the parameter can only accept an array reference.
    • $tag_tbl: a reference to a hash that specifies any legal attributes such as name, border, id, class, etc. for the TABLE tag.
    • $tag_tr: a reference to a hash that specifies any legal attributes for the TR tag.
    • $tag_th: a reference to a hash that specifies any legal attributes for the TH tag.
    • $tag_td: a reference to a hash that specifies any legal attributes for the TD tag.

Notice $tag_tr and $tag_th controls all the rows and columns of the whole table. The keys of the hash are the attribute names in these cases. However, $tag_td is column specific, i.e., you should specify TD attributes for every column separately. The key of %$tag_td are either column names or column indices, the value is a reference to a hash. E.g., $tag_td = {col3 => {'style'=>'background-color:#cccc99;'}}. However, before version 1.74, the value is the full string to be inserted into the TD tag. E.g., $tag_td = {col3 => 'align=right valign=bottom} only change the TD tag in "col3" to be <TD align=right valign=bottom>;. This format is still supported for backward compatibility. 💅

$portrait controls the layout of the table. The default is 1, i.e., the table is shown in the "Portrait" style, like in Excel. 0 means "Landscape". Since version 1.59, tbody and thead tags are added to the portrait mode output. 🌸

Since version 1.74, $callback is introduced to give users fine control on the tag for each cell, i.e., for each th/td cells. $callback is a subroutine reference, where the sub is expected to take parameters ($tag, $row_index, $col_index, $col_name, $table), $tag is reference to a hash containing existing TH/TD tags, the sub will return a new tag. The rest of the parameters give sub access to the identity of the table cell, as well as the table itself. 👑

If the following example, the callback function colors each UnitPrice cell based on whether its value is >=20 or <20. It colors each Discontinued cell based on whether its value is TRUE or FALSE. One can also control the column header cells, which has row index of -1. That is the reason we use "$row >=0 " within callback to make sure it cell is not a column header. 🎯

   $t=Data::Table::fromCSV("Data-Table-1.74/Product.csv",1,undef, {'OS'=>1});
   my $callback = sub {
       my ($tag, $row, $col, $colName, $table) = @_;
       if ($row >=0 && $colName eq 'UnitPrice') {
           $tag->{'style'} = 'background-color:'. (($table->elm($row, $col)>=20) ? '#fc8d59':'#91bfdb') . ';';
       }
       if ($row >=0 && $colName eq 'Discontinued') {
           $tag->{'style'} = 'background-color:'. (($table->elm($row, $col) eq 'TRUE') ? '#999999':'#af8dc3') .';';
       }
       return $tag;
   };

   print $t->html(undef, undef, undef, undef, undef, undef, $callback);
Attention: You will have to escape HTML-Entities yourself (for example '<' as '&lt;'), if you have characters in you table which need to be escaped. You can do this for example with the escapeHTML-function from CGI.pm (or the HTML::Entities module). ⚠️
     use CGI qw(escapeHTML);
     [...]
     $t->colMap($columnname, sub{escapeHTML($_)}); # for every column, where HTML-Entities occur.
  • string table::html2 ($colors = ["#D4D4BF","#ECECE4","#CCCC99"], $specs = {'name' => '', 'border' => '1', ...}) — This method is deprecated. It's here for compatibility. It now simple call html method with $portrait = 0, see previous description. 💀

return a string corresponding to a "Landscape" html-tagged table. This is useful to present a table with many columns, but very few entries. Check the above table::html for parameter descriptions.

  • string table::wiki(...) — This method accepts the same parameters as table::html, returns a wikitable instead. 🧚
  • string table::wiki2(...) — This method accepts the same parameters as table::html2, returns a wikitable instead in landscape orientation. 🌈

🌸 Table Operations 🌸

okay bestie, THIS is the main character section 💅 every method that actually does stuff to your table lives here. no skipping, we're doing all of them 🫶

int table::setElm ($rowIdx, $colID, $val)

changes the value sitting at [$rowIdx, $colID] to the shiny new $val. gives you back 1 if it worked, undef if it didn't 😭

as of 1.68, setElm got an upgrade and can do multiple elements at once — meaning $rowIdx and $colIdx can be references to an index array, and then setElm() modifies every single cell defined by that grid. so many cells. so little effort. slay ✨

$t->setElm([0..2], ['ColA', 'ColB'], 'new value');
$t->setElm(0, [1..2], 'new value');

# puts a limit on the price of all expensive items
$t_product->match_pattern_hash('$_{UnitPrice} > 20');
$t_product->setElm($t_product->{MATCH}, 'UnitPrice', 20);

int table::addRow ($rowRef, $rowIdx = table::nofRow, $arg_ref = {addNewCol => 0})

adds a whole new row, and $rowRef can point at the actual list of scalars, or it can be a hash_ref (supported since version 1.60, we love an upgrade). if $rowRef points to a hash, the method looks up each field's value by its column name — like $rowRef->{colName} — and if that name isn't found, undef gets used for that field. nbd.

the new row will be referred to as $rowIdx as the result. so like, addRow($aRow, 0) drops your new row in as the very first row. by default it just appends to the end like a good little row.

in 1.67 we got {addNewCol => 1} — if you pass that, a brand new column gets auto-created for every new element it bumps into inside $rowRef. iconic behaviour tbh 👑

# automatically add a new column "aNewColumn" to $t, in order to hold the new value
$t->addRow({anExistingColumn => 123, aNewColumn => "XYZ"}, undef, {addNewCol => 1});
# $t only had one column, after this call, it will contain a new column 'col2', in order to hold the new value
$t->addRow([123, "XYZ"], undef, {addNewCol => 1});

returns 1 upon success, undef otherwise. periodt.

refto_array table::delRow ( $rowIdx )

deletes the row at $rowIdx. it hands you back the reference to the deleted row. gone but not forgotten 🦋

refto_array table::delRows ( $rowIdcsRef )

deletes every row inside @$rowIdcsRef. it returns an array of the deleted rows, in the same order as $rowIdcsRef, upon success. (yes, upon success. once. that's enough.) ✨

int table::addCol ($colRef, $colName, $colIdx = numCol)

adds a fresh new column — $colRef points to the actual data — and the new column gets referred to as $colName or $colIdx as the result. so addCol($aCol, 'newCol', 0) puts that column as the very first one. by default it appends to the end, naturally.

returns 1 upon success, undef otherwise. and in 1.68, $colRef can just be a scalar — that scalar is the default value used to create the new column. so if you wanna make a new column full of undef, or 0, or 'default', respectively, you do:

$t->addCol(undef, 'NewCol');
$t->addCol(0, 'NewIntCol');
$t->addCol('default', 'NewStringCol');

refto_array table::delCol ($colID)

deletes the column at $colID and returns the reference to the deleted column. bye girl 👋

arrayof_refto_array table::delCols ($colIDsRef)

deletes a whole list of columns, pointed at by $colIDsRef. it returns an array of the deleted columns in the same order as $colIDsRef upon success. mass deletion but make it orderly 🌈

refto_array table::rowRef ($rowIdx)

returns a reference to the row at $rowIdx upon success, or undef otherwise.

refto_arrayof_refto_array table::rowRefs ($rowIdcsRef)

returns a reference to an array of row references upon success, undef otherwise.

array table::row ($rowIdx)

returns a copy of the row at $rowIdx upon success, or undef otherwise. copy is the key word here bestie 💅

refto_hash table::rowHashRef ($rowIdx)

returns a reference to a hash containing a copy of the row at $rowIdx, upon success, or undef otherwise. the keys in that hash are the column names, and the values are the matching elements in that row. and because it's a copy, messing with the hash values does not change the original table. protective queen 🛡️

refto_array table::colRef ($colID)

returns a reference to the column at $colID upon success.

refto_arrayof_refto_array table::colRefs ($colIDsRef)

returns a reference to an array of column references upon success.

array table::col ($colID)

returns a copy of the column at $colID upon success, or undef otherwise.

int table::rename ($colID, $newName)

renames the column at $colID to $newName. the newName has to be valid, and it should not be identical to any other existing column names — no duplicate names in this house 🚫. returns 1 upon success, or undef otherwise.

refto_array table::replace ($oldColID, $newColRef, $newName)

replaces the column at $oldColID with the array pointed at by $newColRef, and renames it to $newName. $newName is optional if you don't feel like renaming. returns 1 upon success, or undef otherwise.

int table::swap ($colID1, $colID2)

swaps the two columns referred to by $colID1 and $colID2. returns 1 upon success, or undef otherwise. trading places, no drama 🎀

int table::moveCol($colID, $colIdx, $newColName)

moves the column referred to by $colID to a new location at $colIdx. if $newColName is specified, the column gets renamed too. returns 1 upon success, or undef otherwise.

int table::reorder($colIDRefs, $arg_ref)

rearranges the columns according to the order you specify in $colIDRef. ⚠️ columns you don't specify in the reference array get appended to the end! so if you'd rather drop the ones you didn't mention, set $arg_ref to {keepRest => 0}.

heads up: reorder() changes the table itself, while subTable(undef, $colIDRefs) returns a new table instead. also reorder() might run faster than subTable, because elements may not need to be copied. speed AND efficiency, we love to see it 🔥

int table::colMap ($colID, $fun)

for every element in column $colID, it maps a function $fun onto it. returns 1 upon success, or undef otherwise. it's a super handy way to format a column. like, if a column named URL contains URL strings, doing colMap("URL", sub {"<a href='$_'>$_</a>"}) before html() turns every URL into a clickable hyper link when it's displayed in a web browser. chef's kiss 👑

int table::colsMap ($fun)

for every row in the table, it maps a function $fun onto it. it can do everything colMap can do and more. returns 1 upon success, or undef otherwise. colMap only gives $fun access to the particular element per row, while colsMap gives $fun full access to all elements per row. so much power 🫶

example: if two columns are named duration and unit (["2", "hrs"], ["30", "sec"]), then colsMap(sub {$_->[0] .= " (".$_->[1].")"; } will change each row into (["2 hrs", "hrs"], ["30 sec", "sec"]). as shown, inside the $func, a column element should be referred to as $_->[$colIndex].

int table::sort($colID1, $type1, $order1, $colID2, $type2, $order2, ...)

sorts a table in place. first sorts by column $colID1 in $order1 as $type1, then by $colID2 in $order2 as $type2, and so on forever. $type is 0 for numerical and 1 for others; $order is 0 for ascending and 1 for descending.

in 1.62, instead of memorising those numbers you can use constants (and heads up, constants do not start with $):

Data::Table::NUMBER
Data::Table::STRING
Data::Table::ASC
Data::Table::DESC

sorting happens in the priority of colID1, colID2, ... returns 1 upon success, or undef otherwise.

⚠️ notice the table is rearranged as a result! this is different from perl's list sort, which returns a sorted copy while leaving the original list untouched. the authors feel inplace sorting is more natural. okay so it's a vibe choice 💀

table::sort can also take a user supplied operator, which is super useful when neither numerical nor alphabetic order is correct.

$Well=["A_1", "A_2", "A_11", "A_12", "B_1", "B_2", "B_11", "B_12"];
$t = Data::Table->new([$Well], ["PlateWell"], 1);
$t->sort("PlateWell", 1, 0);
print join(" ", $t->col("PlateWell"));
# prints: A_1 A_11 A_12 A_2 B_1 B_11 B_12 B_2
# in string sorting, "A_11" and "A_12" appears before "A_2";
my $my_sort_func = sub {
  my @a = split /_/, $_[0];
  my @b = split /_/, $_[1];
  my $res = ($a[0] cmp $b[0]) || (int($a[1]) <=> int($b[1]));
};
$t->sort("PlateWell", $my_sort_func, 0);
print join(" ", $t->col("PlateWell"));
# prints the correct order: A_1 A_2 A_11 A_12 B_1 B_2 B_11 B_12

table table::match_pattern ($pattern, $countOnly)

returns a new table made of only those rows that evaluate to true by $pattern, upon success, or undef otherwise. if $countOnly is set to 1, it just returns the number of rows that match the string without making a new copy of the table. $countOnly is 0 by default.

side effect alert 🚨 @Data::Table::OK (you should use $t->{OK} after 1.62) stores a true/false array for the original table rows. using it, you can find out which rows got selected and which didn't.
side effect alert 🚨 @Data::Table::MATCH stores a reference to an array containing all row indices for matched rows.

inside the $pattern string, a column element should be referred to as $_->[$colIndex]. e.g. match_pattern('$_->[0]>3 && $_->[1]=~/^L') grabs every row where its first column is greater than 3 and its second column starts with the letter 'L'. ⚠️ notice it only takes colIndex — column names are not acceptable here! rude, but okay.

table table::match_pattern_hash ($pattern, $countOnly)

returns a new table made of those rows evaluated to be true by $pattern, upon success, or undef otherwise. if $countOnly is set to 1, it simply returns the number of rows that match the string without making a new copy of the table. $countOnly is 0 by default.

side effect alert 🚨 @Data::Table::OK stores a reference to a true/false array for the original table rows. using it, you can find out which rows are being selected or unselected.
side effect alert 🚨 @Data::Table::MATCH stores a reference to an array containing all row indices for matched rows.

inside the $pattern string, a column element should be referred to as ${column_name}. match_pattern_hash() was added in 1.62. the difference between this method and match_pattern is that here, each row gets fed to the pattern as a hash %_. with match_pattern, each row gets fed as an array ref $_. so the pattern for match_pattern_hash() ends up wayyy cleaner ✨

say a table has two columns: Col_A as the 1st column and Col_B as the 2nd column. a filter like "Col_A > 2 AND Col_B < 2" used to be written as

$t->match_pattern('$_->[0] > 2 && $_->[1] <2');

where you'd have to figure out that $t->colIndex('Col_A') is 0 and $t->colIndex('Col_B') is 1 just to build the pattern. 😭 now you can use the column name directly in the pattern:

$t->match_pattern_hash('$_{Col_A} >2 && $_{Col_B} <2');

this method creates $t->{OK}, as well as @Data::Table::OK, same as match_pattern(). no cap. 💜

Simple boolean operators like and/or can go straight into the pattern string, no cap. And if you want something fancier, the manpage has you covered with this little masterpiece:

my $t= Data::Table->new([[2,5,'Jan'], [1,6,'Feb'], [-3,2,'Apr'], [6,-4,'Dec']], ['X','Y','Month'], 0);
# we need to use our instead of my, so that %Q1 is accessible within match_pattern_hash
our %Q1 = ('Jan'=>1, 'Feb'=>1, 'Mar'=>1);
# find records belongin to Q1 months, we need to use %::Q1 to access the Q1 defined outside Data::Table
$t2=$t->match_pattern_hash('exists $::Q1{$_{Month}}');

The lore here 🫶: you gotta declare it with our instead of my so that %Q1 is reachable inside match_pattern_hash, and then you poke at it through %::Q1 since that's how you grab the Q1 that lives outside Data::Table.

Same energy for subs — subroutines can be accessed inside match_pattern_hash using "::":

sub in_Q1 {
    my $x = shift;
    return ($x eq 'Jan' or $x eq 'Feb' or $x eq 'Mar');
}
$t2=$t->match_pattern_hash('::in_Q1($_{Month})');

However — and this is the part where I grab your shoulders — such usage is discouraged. Because match_pattern_hash() does not throw errors when the pattern is invalid. It'll just sit there being wrong and you'll be debugging vibes at 2am. For complex filtering logic, the manpage strongly recommends you stick to row-based looping. That's the move. 💅

table table::match_string ($s, $caseIgnore, $countOnly)

Returns a new table made of all the rows that contain the string $s in any of their fields, upon success — and undef otherwise. If $caseIgnore evaluates to true, case gets ignored (s/$s/i). If $countOnly is set to 1, it just hands you back the number of rows that match the string, without making a whole new copy of the table. $countOnly is 0 by default.

Side effect: @Data::Table::OK stores a reference to a true/false array for the original table rows. Side effect: @Data::Table::MATCH stores a reference to an array containing all row indices for matched rows. Using it, you can find out which rows got selected vs. unselected, which is lowkey the best part. And plot twist: $s is actually treated as a regular expression and applied to each row element, so you can legitimately specify several keywords by saying something like match_string('One|Other'). Ate. ✨

table table::rowMask($mask, $complement)

mask is a reference to an array, where elements get evaluated as true or false. The size of the mask must be equal to the nofRow of the table. Returns a new table consisting of those rows where the corresponding mask element is true (or false, when complement is set to true).

E.g., $t1=$tbl->match_string('keyword'); and $t2=$tbl->rowMask(\@Data::Table::OK, 1) creates two new tables. $t1 contains all rows matching 'keyword', while $t2 contains all the other rows. Two for the price of one, obsessed. 💖

And okay, bestie, I'm not gatekeeping: the manpage literally says this exact same thing a second time, verbatim, so here it is again in all its repetitive glory 💀:

mask is a reference to an array, where elements are evaluated to be true or false. The size of the mask must be equal to the nofRow of the table. Returns a new table consisting those rows where the corresponding mask element is true (or false, when complement is set to true).

E.g., $t1=$tbl->match_string('keyword'); and $t2=$tbl->rowMask(\@Data::Table::OK, 1) creates two new tables. $t1 contains all rows matching 'keyword', while $t2 contains all other rows.

(She said it twice. Iconic. Moving on. 🎀)

table table::iterator({$reverse => 0})

Returns a reference to an enumerator routine, which lets you loop through each table row. If $reverse is set to 1, it enumerates backward. The convenience here — and this is the whole point — is that each row gets fetched as a rowHashRef, so you can access row elements by name like a civilized person.

my $next = $t_product->iterator();
while (my $row = $next->()) {
  # have access to a row as a hash reference, access row number by &$next(1);
  $t_product->setElm($next->(1), 'ProductName', 'New! '.$row->{ProductName});
}

In this example, each $row is fetched as a hash reference, so you access the elements with $row->{colName}. Heads up though: the elements in that hash are a copy of the original table elements, so modifying $row->{colName} does not modify the original table. If table modification is what you're after, you need to obtain the row index of the returned row. The $next->(1) call — with a non-empty argument — returns the row index of the record that was previously fetched with $next->(). In this example, you use that row index to modify the original table. Sneaky but effective. 🦋

table table::each_group($colsToGroupBy, $funsToApply)

Primary key columns are specified in $colsToGroupBy. All rows are grouped by primary keys first (keys sorted as string). Then for each group, the subroutine $funToAppy is applied to the corresponding rows. $funToApply gets passed two parameters: ($tableRef, $rowIDsRef). All rows sharing the key get passed in as a Data::Table object (with all columns, in the order of ascending row index) as the first parameter. The second, optional parameter contains an array of row indices of the group members. And since all rows in the passed-in table contain the same keys, the key value can be obtained from its first table row. Easy. 🌸

table table::group($colsToGroupBy, $colsToCalculate, $funsToApply, $newColNames, $keepRestCols)

Primary key columns are specified in $colsToGroupBy. All rows are grouped by primary keys first. Then for each group, an array of subroutines (in $funsToAppy) is applied to corresponding columns and yields a list of new columns (specified in $newColNames).

$colsToGroupBy and $colsToCalculate are references to arrays of colIDs. $funsToApply is a reference to an array of subroutine references. $newColNames is a reference to an array of new column name strings. If specified, the size of the arrays pointed to by $colsToCalculate, $funsToApply and $newColNames should be i dentical. And yes, a column may be used more than once in $colsToCalculate — go off, reuse her. 👑

$keepRestCols defaults to 1 (it was introduced as 0 in 1.64, changed to 1 in 1.66 for backward compatibility) introduced in 1.64). Otherwise, the remaining columns are returned with the first encountered value of that group.

E.g., an employee salary table $t contains the following columns: Name, Sex, Department, Salary. (see examples in the SYNOPSIS)

$t2 = $t->group(["Department","Sex"],["Name", "Salary"], [sub {scalar @_}, \&average], ["Nof Employee", "Average Salary"], 0);

Department, Sex are used together as the primary key columns. A new column "Nof Employee" is created by counting the number of employee names in each group, and a new column "Average Salary" is created by averaging the Salary data falled into each group. As the result, we have the head count and average salary information for each (Department, Sex) pair. With your own functions (such as sum, product, average, standard deviation, etc), the group method is very handy for accounting purposes. 🧮

If primary key columns are not defined, all records will be treated as one group.

$t2 = $t->group(undef,["Name", "Salary"], [sub {scalar @_}, \&average], ["Nof Employee", "Average Salary"], 0);

The above statement will output the total number of employees and their average salary as one line. One line! Chef's kiss. 😭

table table::pivot($colToSplit, $colToSplitIsStringOrNumeric, $colToFill, $colsToGroupBy, $keepRestCols)

Every unique value in a column (specified by $colToSplit) becomes a new column. An undef value becomes "NULL". When $colToSplitIsStringOrNumeric is set to numeric (0 or Data::Table:NUMBER), the new column names are prefixed by "oldColumnName=". The new cell element is filled by the value specified by $colToFill (was 1/0 before version 1.63).

Note: yes, it seems I made an incompatible change in version 1.64, where $colToSplitIsStringOrNumber used to be $colToSplitIsNumeric, where 0 meant STRING and 1 meant NUMBER. Now it is opposite. However, I also added auto-type detection code, so this parameter essentially is auto-guessed and most old code should behave the same as before. Crisis averted, mostly. 🙈

When primary key columns are specified by $colsToGroupBy, all records sharing the same primary key collapse into one row, with values in $colToFill filling the corresponding new columns. If $colToFill is not specified, a cell is filled with the number of records that fall into that cell.

$colToSplit and $colToFill are colIDs. $colToSplitIsNumeric is 1/0. $colsToGroupBy is a reference to an array of colIDs. $keepRestCols is 1/0, by default 0. If $keepRestCols is off, only primary key columns and new columns are exported; otherwise, all the rest of the columns are exported as well.

E.g., applying the pivot method to the resultant table of the example of the group method:

$t2->pivot("Sex", 0, "Average Salary",["Department"]);

This creates a 2x3 table, where Departments are used as row keys, and Sex (female and male) become two new columns. "Average Salary" values are used to fill the new table elements. Used together with the group method, pivot method is very handy for accounting type of analysis. If $colsToGroupBy is left as undef, all rows are treated as one group. If $colToSplit is left as undef, the method will generate a column named "(all)" that matches all records sharing the corresponding primary key. Slay. 💅

table table::melt($keyCols, $variableCols, $arg_ref)

The idea of melt() and cast() are taken from Hadley Wickham's Reshape package in R language. A table is first melt()ed into a tall-skinny format, where measurements are stored in the format of a variable-value pair per row. Such a format can then be easily cast()ed into various contingency tables. 🌈

One needs to specify the columns consisting of primary keys, and the columns that are considered as variable columns. The output variable column is named 'variable' unless specified by $arg_ref{variableColName}. The output value column is named 'value', unless specified in $arg_ref{valueColName}. By default NULL values are not output, unless $arg_ref{skip_NULL} is set to false. By default empty string values are kept, unless one sets skip_empty to ` (yeah, that's a lone stray backtick in the original, she's mysterious, we respect her 🫶).

For each object (id), we measure variable x1 and x2 at two time points
$t = new Data::Table([[1,1,5,6], [1,2,3,5], [2,1,6,1], [2,2,2,4]], ['id','time','x1','x2'], Data::Table::ROW_BASED);
# id  time    x1      x2
# 1   1       5       6
# 1   2       3       5
# 2   1       6       1
# 2   2       2       4

# melting a table into a tall-and-skinny table
$t2 = $t->melt(['id','time']);
#id      time    variable        value
# 1       1       x1      5
# 1       1       x2      6
# 1       2       x1      3
# 1       2       x2      5
# 2       1       x1      6
# 2       1       x2      1
# 2       2       x1      2
# 2       2       x2      4

Look at that transformation. Tall. Skinny. Gorgeous. ✨

# casting the table, &average is a method to calculate mean
# for each object (id), we calculate average value of x1 and x2 over time
$t3 = $t2->cast(['id'],'variable',Data::Table::STRING,'value', \&average);
# id      x1      x2
# 1       4       5.5
# 2       4       2.5

🦋 table table::cast($colsToGroupBy, $colToSplit, $colToSplitIsStringOrNumeric, $colToCalculate, $funToApply)

okay bestie, go read melt() first, because melt() and cast() are literally meant to be used together, they're a duo, a package deal, no cap 💖

So the table has been melten before you get here, right? Then cast() does its thing: it groups the table by the primary keys you name in $colsToGroupBy. Then, for each group of objects that share the same id, it groups the values (those are the ones you picked in $colToCalculate) by their unique variable names (those come from $colToSplit). Then it runs your subroutine $funToApply on them to get one aggregate value. 🌸

And the output? Each unique primary key becomes a row, each unique variable name becomes a column, and the cells hold the calculated aggregated value. Iconic behaviour honestly.

Two little edge cases you need to know:

  • If $colsToGroupBy is undef, then all rows get treated as if they're in the same group.
  • If $colToSplit is undef, a fresh column called "(all)" is used to hold the results.

Watch this, it's giving main character energy:

$t = Data::Table->new( # create an employ salary table
  [
    ['Tom', 'male', 'IT', 65000],
    ['John', 'male', 'IT', 75000],
    ['Tom', 'male', 'IT', 65000],
    ['John', 'male', 'IT', 75000],
    ['Peter', 'male', 'HR', 85000],
    ['Mary', 'female', 'HR', 80000],
    ['Nancy', 'female', 'IT', 55000],
    ['Jack', 'male', 'IT', 88000],
    ['Susan', 'female', 'HR', 92000]
  ],
  ['Name', 'Sex', 'Department', 'Salary'], Data::Table::ROW_BASED);

# get a Department x Sex contingency table, get average salary across all four groups
print $t->cast(['Department'], 'Sex', Data::Table::STRING, 'Salary', \&average)->csv(1);
Department,female,male
IT,55000,73600
HR,86000,85000
# get average salary for each department
print $t->cast(['Department'], undef, Data::Table::STRING, 'Salary', \&average)->csv(1);
Department,(all)
IT,70500
HR,85666.6666666667

# get average salary for each gender
print $t->cast(['Sex'], undef, Data::Table::STRING, 'Salary', \&average)->csv(1);
Sex,(all)
male,75500
female,75666.6666666667

# get average salary for all records
print $t->cast(undef, undef, Data::Table::STRING, 'Salary', \&average)->csv(1);
(all)
75555.5555555556

🎀 Table-Table Manipulations

💅 int table::rowMerge ($tbl, $argRef)

Appends all the rows in the table object $tbl onto the original rows. ✨

Real talk about the old behaviour: before 1.62, the merging table $tbl had to have the same number of columns as the original, and those columns had to be in exactly the same order. It returns 1 on success and undef otherwise. And heads up — you should not use the $tbl object afterwards, because it literally becomes part of the new table now. 💀

Since 1.62 though, you can pass {byName =>1, addNewCol=>1} as $argRef. Here's the tea:

  • If byName is set to 1, the columns in $tbl don't have to be in the same order as the first table — matching is done by column name instead.
  • If addNewCol is set to 1, and $tbl has a brand new column name that doesn't already exist in the first table, that column gets automatically added to the resultant table. 🦋

Typically you want to specify both of these options at the same time. Periodt.

🌸 int table::colMerge ($tbl, $argRef)

Same vibe, but for columns: appends all the columns in table object $tbl to the original columns. This time $tbl must have the same number of rows as the original. Returns 1 on success, undef otherwise, and again, don't touch $tbl afterwards — it's part of the new table now. 🙈

Since 1.62, you can specify {renameCol => 1} as $argRef to auto-fix any column name collision. If $tbl has a column that already exists in the first table, it gets renamed (with a _2 suffix) so the collision just... doesn't happen. Slay. 💖

⭐ table table::join ($tbl, $type, $cols1, $cols2, $argRef)

Joining two tables, bestie. Here are the join types supported, defined by $type:

  1. 0: inner join
  2. 1: left outer join
  3. 2: right outer join
  4. 3: full outer join

But in 1.62, instead of memorising those numbers (not me trying to remember which is which 😭), you can just use constants — and psst, notice constants do not start with $:

Data::Table::INNER_JOIN
Data::Table::LEFT_JOIN
Data::Table::RIGHT_JOIN
Data::Table::FULL_JOIN

$cols1 and $cols2 are references to an array of colIDs, and rows that have the same elements in all the listed columns get merged. As for the result table: the columns listed in $cols2 are deleted before the new table is returned.

The implementation is a hash-join, so the running time should be linear with respect to the sum of the number of rows in the two tables (that's assuming both tables fit in memory, iykyk).

Now the gotcha: if the non-key columns of the two tables share the same name, the routine will fail, because the result table can't have two columns with the same name. 🙈 In 1.62, you can specify {renameCol=>1} as $argRef so the second column gets automatically renamed (with a _2 suffix) to dodge the collision.

Two more named args for the NULL girlies:

  • If you'd like to treat the NULLs in the key columns as an empty string, set {NULLasEmpty => 1}.
  • If you do not want to treat NULLs as empty strings, but you still want the NULLs in two tables to be considered equal (but not equal to ''), set {matchNULL => 1}.
Obviously, if NULLasEmpty is set to 1, matchNULL will have no effect. No cap.

🌈 Internal Methods

All of these are mainly implemented so the other methods in the Table class can use them. Users should avoid them, fr fr. But they're listed here anyway for the developers who wanna understand the code and maybe derive a new class from Data::Table. 👑

🧚 int table::rotate

Converts the internal structure of a table between row-based and column-based. Returns 1 upon success, undef otherwise.

🍓 string csvEscape($string, {delimiter=>, qualifier})

Encodes a scalar into a CSV-formatted field.

Optional named arguments: delimiter and qualifier, in case you wanna use characters other than the defaults. The default delimiter and qualifier come from $Data::Table::DEFAULTS{'CSV_DELIMITER'} (defaults to ',') and $Data::Table::DEFAULTS{'CSV_QUALIFIER'} (defaults to '"'), respectively. ✨

Please note that this function only escapes one element in a table. To escape the whole table row, you need this:

join($delimiter, map {csvEscape($_)} @row . $endl;

where $endl refers to End-of-Line, which you may or may not want to add, and it is OS-dependent. That's why the csvEscape method is kept to the simplest form, as an element transformer. 💅

💜 refto_array parseCSV($string)

Breaks a CSV encoded string into an array of scalars (check it out, we did it the cool way 😎).

Optional argument size: specify the expected number of fields after csv-split. Optional named arguments: delimiter and qualifier, in case the user wants to use characters other than the defaults. The default delimiter and qualifier is taken from $Data::Table::DEFAULTS{'CSV_DELIMITER'} (defaults to ',') and $Data::Table::DEFAULTS{'CSV_QUALIFIER'} (defaults to '"'), respectively.

🦋 string tsvEscape($rowRef)

Encodes a scalar into a TSV-formatted string.

🌟 TSV FORMAT

There is no standard for TSV format as far as we know. And the reason we even care? CSV format can't handle binary data very well, so we chose the TSV format to overcome that limitation. 🫶

We define TSV based on the MySQL convention:

"\0", "\n", "\t", "\r", "\b", "'", "\"", and "\\" are all escaped by '\' in the TSV file.
(Warning: MySQL treats '\f' as 'f', and it's not escaped here)
Undefined values are represented as '\N'.

However — plot twist — you can switch off this transformation by setting {transform_element => 0} in the fromTSV or tsv method. 💖

So before, if a cell reads 'A line break is \n', it gets read in as 'A line break is [return]' in memory. When you use the tsv method to export, it gets transformed back to 'A line break is \n'. But if you export it as a csv, that [return] will break the format. 😭 Now if transform_element is set to 0, the cell is stored as 'A line break is \n' in memory, so the csv export will be correct. But remember to set {transform_element => 0} in the tsv export method too, otherwise the cell will become 'A line break is \\n'. And be aware that transform_element controls column headers as well. Not me warning you twice, but I'm warning you twice.

🌈 INTERFACE TO OTHER SOFTWARES

Spreadsheet is a very generic type, so the Data::Table class provides an easy interface between databases, web pages, CSV/TSV files, graphics packages, etc. ✨

Here's a summary (partially repeat, soz) of some classic usages of Data::Table.

🎀 Interface to Database and Web

use DBI;

$dbh= DBI->connect("DBI:mysql:test", "test", "") or die $DBI::errstr;
my $minAge = 10;
$t = Data::Table::fromSQL($dbh, "select * from mytable where age >= ?", [$minAge]);
print $t->html;

🦋 Interface to CSV/TSV

$t = fromFile("mydata.csv"); # after version 1.51
$t = fromFile("mydata.tsv"); # after version 1.51

$t = fromCSV("mydata.csv");
$t->sort(1,1,0);
print $t->csv;

Same for TSV

⭐ Interface to Excel XLS/XLSX

Reads in two tables from NorthWind.xls file, writes them out to XLSX format. See the Data::Table::Excel module for details. 👑

use Data::Table::Excel;

my ($tableObjects, $tableNames)=xls2tables("NorthWind.xls");
$t_category = $tableObjects[0];
$t_product = $tableObjects[1];

tables2xlsx("NorthWind.xlsx", [$t_category, $t_product]);

🌸 Interface to Graphics Package

use GD::Graph::points;

$graph = GD::Graph::points->new(400, 300);
$t2 = $t->match('$_->[1] > 20 && $_->[3] < 35.7');
my $gd = $graph->plot($t->colRefs([0,2]));
open(IMG, '>mygraph.png') or die $!;
binmode IMG;
print IMG $gd->png;
close IMG;

💖 AUTHOR

Copyright 1998-2008, Yingyao Zhou & Guangzhou Zou. All rights reserved.

It was first written by Zhou in 1998, then significantly improved and maintained by Zou since 1999. The authors thank Tong Peng and Yongchuang Tao for valuable suggestions. We also thank those who kindly reported bugs — some of them are acknowledged in the "Changes" file. 🫶

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

Please send bug reports and comments to: easydatabase at gmail dot com. When sending bug reports, please provide the version of Table.pm, the version of Perl. 🙏

👀 SEE ALSO ✨

okay bestie, you made it to the very end, so here's your little reading list — go peek at these when you wanna take the Table vibes further 💖

  • DBI — the database interface situation, iykyk
  • GD::Graph — graphs, plotting, visuals, all of it 🎀
  • Data::Table::Excel — yes, Excel, because sometimes the spreadsheet is the assignment 🦋

and that's a wrap on the whole page, no cap 🫶 this was perl v5.36.0, dated 2022-11-19, and it's giving iconic reference material — go build something cute with it, bestie 💅✨🌸

📦 where this page comes from