Reading and writing data

CSV files

DataFrame::fromCsv() reads delimited text the way readr’s read_csv() and read_delim() do. It takes a path (files ending in .gz are decompressed) or an open stream. DataFrame::fromCsvString() reads from a string.

readr’s options are named arguments in camelCase:

$text = <<<'CSV'
    # exported 2024-06-01
    id;zip;score;visited
    1;02134;3,5;2024-05-01
    2;10001;-;2024-05-03
    3;94110;4,25;2024-05-04
    CSV;

$df = DataFrame::fromCsvString(
    $text,
    types: ['zip' => 'c'],
    delimiter: ';',
    na: ['-'],
    comment: '#',
    decimalMark: ',',
);

echo $df;

# A tibble: 3 × 4
     id zip   score visited   
  <int> <chr> <dbl> <date>    
1     1 02134  3.5  2024-05-01
2     2 10001 NA    2024-05-03
3     3 94110  4.25 2024-05-04

Column types

types accepts readr’s compact string, with one letter per column:

types also accepts an array by column name. A value is a Type, a type name such as 'factor', or a letter. The key .default sets the type of every column not named.

$penguins = DataFrame::fromCsv('penguins.csv', types: 'ffddii_i');
echo $penguins->glimpse(), "\n";

$some = DataFrame::fromCsv('penguins.csv', types: ['species' => 'factor', '.default' => '_'], select: ['species']);
echo $some->glimpse();

Rows: 344
Columns: 7
$ species     <fct> Adelie, Adelie, Adelie, Adelie, Adelie, Adelie, Adelie, Ad…
$ island      <fct> Torgersen, Torgersen, Torgersen, Torgersen, Torgersen, Tor…
$ bill_len    <dbl> 39.1, 39.5, 40.3, NA, 36.7, 39.3, 38.9, 39.2, 34.1, 42.0, …
$ bill_dep    <dbl> 18.7, 17.4, 18.0, NA, 19.3, 20.6, 17.8, 19.6, 18.1, 20.2, …
$ flipper_len <int> 181, 186, 195, NA, 193, 190, 181, 195, 193, 190, 186, 180,…
$ body_mass   <int> 3750, 3800, 3250, NA, 3450, 3650, 3625, 4675, 3475, 4250, …
$ year        <int> 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007…
Rows: 344
Columns: 1
$ species <fct> Adelie, Adelie, Adelie, Adelie, Adelie, Adelie, Adelie, Adelie…

Guessing

Without explicit types, phibble guesses each column from up to guessMax rows, sampled the way readr 2 samples them. A column is:

• bool if every value is T, F, TRUE, FALSE, True, False, true, or false,
• int if every value is a whole number (readr guesses double here),
• float if every value is a number, including Inf and NaN,
• date if every value is an ISO 8601 date,
• datetime if every value is an ISO 8601 date and time,
• string otherwise.

Numbers with leading zeros such as 007 stay strings, as in readr.

When a value cannot be parsed as its column’s type, a row has the wrong number of fields, or a quoted field is never closed, phibble still returns the data. The value becomes NA, and the read raises one E_USER_WARNING that lists the problems. Rows are numbered as in readr::problems(): the first data row is row 2, and skipped, blank, and comment lines are not counted.

Quotes

Quoted fields follow readr 2’s rules. A field is quoted when its first character is the quote character. Inside a quoted field, a doubled quote is a literal quote. Any other quote turns quoting off or back on and is dropped, so "x "y" z" reads as x y z, and "x "y",z" reads as x y,z. A quote inside a field that does not start with one is kept as it is. With escapeDouble: false, the quotes inside a quoted field are kept, and only the opening and closing quotes are dropped.

Files written with PHP’s fputcsv() and its default backslash escape contain \" instead of "". readr does not treat the backslash as an escape, and neither does phibble unless escapeBackslash: true is passed.

set_error_handler(function (int $level, string $message): bool {
    echo "Warning: {$message}\n";

    return true;
});

$df = DataFrame::fromCsvString("id,score\n1,2.5\n2,oops\n3\n", types: ['score' => 'double']);

echo $df;

Warning: One or more parsing issues: row 4: expected 2 columns, got 1; column 'score': 1 value could not be parsed as float.
# A tibble: 3 × 2
     id score
  <int> <dbl>
1     1   2.5
2     2  NA  
3     3  NA

Reading in chunks

DataFrame::fromCsvChunked() reads a file a chunk of rows at a time, as readr’s read_delim_chunked() does. It passes each chunk to a callback with the 1-based index of the chunk’s first row, and stops if the callback returns false. DataFrame::csvChunks() returns the same chunks as a generator keyed by that index. Memory depends on the chunk size, so a file larger than the memory limit can be filtered down as it is read:

$kept = [];
foreach (DataFrame::csvChunks('feed.tsv', chunkSize: 10_000, delimiter: "\t", types: ['.default' => 'c']) as $chunk) {
    $kept[] = $chunk->filter(col('category')->eq('Running'));
}
$running = DataFrame::concat($kept);

Types not given are guessed from the first chunk, and every later chunk is parsed as those types. As in readr, guessMax defaults to the chunk size. Give every type, as above, to skip guessing. A value in a later chunk that does not fit its column’s type becomes NA. Factors without levels take their levels from each chunk.

Every other option works as it does in fromCsv(). Each chunk’s problems raise their own warning, with rows numbered from the start of the file.

When the phit extension is loaded (0.2.0 or later), the chunks are read natively with Phit\CsvReader, about three times faster on a wide tab-separated file. The results are the same as the PHP reader’s, which is used otherwise.

Writing CSV

writeCsv($path) writes a file and toCsv() returns the text. The format follows readr’s write_csv(). Values are quoted only when needed, NA is written as NA, logicals as TRUE and FALSE, dates in ISO 8601, and datetimes in UTC with a Z. Factors are written as their labels, and list-columns throw.

$df = tribble(
    ['name', 'note', 'score', 'seen'],
    ['Ana', 'likes "quotes"', 1e5, true],
    ['Ben', 'a, b', null, false],
);

echo $df->toCsv();

name,note,score,seen
Ana,"likes ""quotes""",100000,TRUE
Ben,"a, b",NA,FALSE

The options are delimiter, na, append, colNames, quote ('needed', 'all', or 'none'), escape, and eol.

JSON

toJson() writes a list of records, and toJson('columns') writes an object of columns. NA is written as null and kept in every record. DataFrame::fromJson() reads either shape and detects which one it has.

$df = tibble(id: [1, 2], score: [2.5, null]);

echo $df->toJson(), "\n";
echo $df->toJson('columns'), "\n";

echo DataFrame::fromJson('{"id": [1, 2], "ok": [true, null]}');

[{"id":1,"score":2.5},{"id":2,"score":null}]
{"id":[1,2],"score":[2.5,null]}
# A tibble: 2 × 2
     id ok   
  <int> <lgl>
1     1 TRUE 
2     2 NA

Databases

DataFrame::fromPdo() runs a query with positional or named bindings and loads the result. PostgreSQL and MySQL report each column’s declared type, and phibble uses it:

A text column stays text, so 'TRUE' and '2024-01-02' in a TEXT column are strings. Only values without a declared type are guessed with the CSV rules. SQLite stores values without a column type, so its results are always guessed, and so are results from other drivers.

NUMERIC and DECIMAL values become doubles, as RPostgres and RMariaDB read them. A double holds about 15 significant digits, so 12345678901234567.89 loses its last digits. Prices such as NUMERIC(10,2) fit. Pass a string type to keep a decimal exact:

DataFrame::fromPdo($pdo, 'SELECT id, balance FROM accounts', types: ['balance' => 'string']);

Explicit types always take precedence over declared ones. fromQuery(), the Laravel ->toDataFrame() macro, and SqlTable::collect() follow the same rules.

$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE visits (id INTEGER, clinic TEXT, bp REAL, seen TEXT)');
$pdo->exec("INSERT INTO visits VALUES (1, 'north', 121.5, '2024-05-01'), (2, 'south', NULL, '2024-05-02'), (3, 'north', 133.0, '2024-05-02')");

$df = DataFrame::fromPdo($pdo, 'SELECT * FROM visits WHERE clinic = ?', ['north']);

echo $df;

# A tibble: 2 × 4
     id clinic    bp seen      
  <int> <chr>  <dbl> <date>    
1     1 north   122. 2024-05-01
2     3 north   133  2024-05-02

DataFrame::fromQuery() loads a query builder without depending on its library:

• A Laravel query builder, Eloquent builder, or relation is read through toSql(), getBindings(), and its connection’s PDO object.
• A Doctrine DBAL QueryBuilder is read through getSQL() and getParameters(). Pass a PDO or DBAL connection as the second argument to run the SQL through PDO. Without one, the builder runs the query itself and phibble guesses every column, since DBAL results carry no column types.

To keep a query in the database and run verbs there, use a SQL table instead.

Laravel collections

DataFrame::fromCollection() reads any object with an all() or toArray() method, such as a Laravel Collection. The items may be arrays, objects, or Eloquent models. toCollection() returns an Illuminate\Support\Collection of row arrays and throws when Laravel is not installed.

// Any object with all() or toArray() works, such as a Laravel Collection
// of arrays or Eloquent models. This stand-in avoids a Laravel dependency.
$collection = new class
{
    public function all(): array
    {
        return [
            ['id' => 1, 'name' => 'Ana'],
            (object) ['id' => 2, 'name' => 'Ben'],
        ];
    }
};

echo DataFrame::fromCollection($collection);

# A tibble: 2 × 2
     id name 
  <int> <chr>
1     1 Ana  
2     2 Ben