How do I merge two partial CSV tables so the gaps in one are filled from the other?

← JSON and Data Processing · Ref: Q1407

Use ':~:'. Merge never overwrites a set value; it fills whatever is unset or missing, element by element. A CSV's elements are its header, each data row (by position) and each cell (by row and column):
- a wholly unset CSV takes the whole source;
- otherwise a missing header is filled, a row past the target's last row is added, and a cell that is missing or empty is filled from the same row and column of the source;
- every cell the target holds is kept - the first recorded value wins;
- if both CSVs have headers and they differ, nothing changes, so mismatched columns are never welded together.
An empty cell is CSV's only way to say "no value", which is why it counts as unset.

MERGE IS NOT STACKING

To stack the rows of two CSVs, use '+=' (in place) or '+' (a new CSV). ':~:' lines the two tables up position by position instead.

A ROW

Merging a CSVRow into a CSV treats it as a one-row source, so it fills row 0.

See Q1364 for reading and writing CSV. See Q1406 for gathering partial results in Dicts. See Q1405 for the rule on single values.

Example

defines module qa.jsonanddata.mergecsvfill

  defines function

    table()
      -> text as String
      <- rtn as CSV: CSV()
      result <- CSV().parse(text)
      if result.isOk()
        rtn := result.ok()

  defines program

    MergeCsvFillDemo()
      stdout <- Stdout()

      //Hydrogen is missing its weight, Helium its symbol, and there is no Lithium row
      elements <- table("name,weight,symbol\nHydrogen,,H\nHelium,4.0026")
      found <- table("name,weight,symbol\nHydrogen,1.008,X\nHelium,9,He\nLithium,6.94,Li")

      //Fill the gaps; Hydrogen keeps H and Helium keeps 4.0026
      elements :~: found
      stdout.println("filled:")
      stdout.print($elements)

      //Stacking rows is += (or +), not merge
      stacked <- table("name,weight,symbol\nHydrogen,1.008,H")
      stacked += found
      stdout.println(`stacked rows: ${stacked.rows()}`)

Coming from another language?

Java: no built-in CSV type; filling blanks from a second file is a hand-written nested loop. Python: pandas DataFrame.combine_first does the same job (fills NaN from the other frame, keeps existing values). EK9: csv :~: other does it in place, guarded by the headers.

Keywords: partial table, combine_first, stack rows, fill, empty cell, :~:, csv, merge