phplyr

Data & Workflow

Statistics, Modeling, & Visualization

Join two tables

Add the airport name and altitude to the weather readings at the three New York City airports at 9 a.m. on January 1, 2013. weather has hourly readings from 2013, and airports has the name and altitude, in feet, of 1,458 US airports, keyed by FAA code. Both are from nycflights13.

use Phibble\DataFrame;use function Phplyr\{filter, select, left_join};$data = 'https://raw.githubusercontent.com/phpstats'    . '/datasets/main/csv';$weather = DataFrame::fromCsv("$data/weather.csv");$airports = DataFrame::fromCsv("$data/airports.csv");$nineAm = fn ($r) => $r->month === 1 && $r->day === 1    && $r->hour === 9;echo $weather |>    filter($nineAm) |>    select('origin', 'time_hour', 'temp', 'wind_speed') |>    left_join(        $airports |> select('faa', 'name', 'alt'),        by: ['origin' => 'faa'],        relationship: 'many-to-one',    );
use Phibble\DataFrame;use function Phplyr\phplyr;$data = 'https://raw.githubusercontent.com/phpstats'    . '/datasets/main/csv';$weather = DataFrame::fromCsv("$data/weather.csv");$airports = DataFrame::fromCsv("$data/airports.csv");$nineAm = fn ($r) => $r->month === 1 && $r->day === 1    && $r->hour === 9;$names = phplyr($airports)    ->select('faa', 'name', 'alt')    ->get();echo phplyr($weather)    ->filter($nineAm)    ->select('origin', 'time_hour', 'temp', 'wind_speed')    ->leftJoin(        $names,        by: ['origin' => 'faa'],        relationship: 'many-to-one',    );
# A tibble: 3 × 6
  origin time_hour            temp wind_speed name                  alt
  <chr>  <dttm>              <dbl>      <dbl> <chr>               <int>
1 EWR    2013-01-01 14:00:00  39.9       15.0 Newark Liberty Intl    18
2 JFK    2013-01-01 14:00:00  39.9       16.1 John F Kennedy Intl    13
3 LGA    2013-01-01 14:00:00  39.9       18.4 La Guardia             22
  • by: ['origin' => 'faa'] matches origin in weather with faa in airports, and the result keeps the name origin.
  • left_join() keeps every row of weather in its original order. A reading with no matching airport would get NA in the new columns. Airports with no readings are left out.
  • relationship: 'many-to-one' throws an exception if a code matches more than one airport.
  • hour is local time, and time_hour prints the same instant in UTC.
  • inner_join() drops the readings with no match. full_join() also keeps the airports with no readings and adds them after the matched rows.

Reference: Joins, binding, and set operations