Converting YAML to CSV for Review in a Spreadsheet

A YAML file with sixty services in it is hard to compare by eye. The same data in a spreadsheet takes a minute: sort by port, filter by tag, spot the row with no memory limit. That is the case for converting YAML to CSV, and it works well as long as you remember the direction of travel. The CSV is a view for reading, built from the YAML, and it is not a second copy to edit. This post converts a list of service records, shows the three places the conversion goes wrong, and ends with the script. It belongs to the cluster on converting YAML to and from JSON, CSV, XML and Excel; the reverse is converting a CSV into a YAML list.

The input

A list of mappings is the shape that maps cleanly onto rows. This file describes two services, one with a nested limits mapping and a list of tags, the other with fewer fields:

- name: api
  port: 8080
  tags: [web, public]
  limits: {cpu: 2, memory: 512}
- name: worker
  port: 9000
  tags: [queue]
  limits: {cpu: 1}

Every record becomes a row and every key becomes a column, so the question is what to do with nested mappings (they have no single cell) and lists (they have several values).

Flatten nested keys with dotted names

pandas.json_normalize walks nested mappings and names each leaf column with its path:

import yaml
import pandas as pd

rows = yaml.safe_load(open("services.yaml"))
df = pd.json_normalize(rows, sep=".")
print(df.to_csv(index=False))

name,port,tags,limits.cpu,limits.memory
api,8080,"['web', 'public']",2,512.0
worker,9000,['queue'],1,

Two things in that output are wrong for a reviewer, and one is right. The right one: limits.cpu and limits.memory are columns, and worker, which has no memory limit, gets an empty cell. That empty cell is information, since it shows where a service has no limit.

Fix 1: lists in cells

The tags column contains ['web', 'public'] with the brackets and quotes of a Python list. A spreadsheet treats it as text, so a filter for "web" has to match inside a string, and the quoting inside quoting makes the CSV harder to parse. Join the list into one cell with a separator that does not appear in the values:

df["tags"] = df["tags"].apply(lambda t: ";".join(t))

Now the cell reads web;public. A semicolon or a pipe works; avoid a comma, which collides with the CSV delimiter and forces quoting. If a record can have no tags key, the cell holds a missing value and ";".join would fail on it, so guard with t if isinstance(t, list) else []. For one cell per tag, the better shape is a second table with a row per service and tag, the long form that spreadsheets pivot well.

Fix 2: integers turned into floats

limits.memory printed as 512.0. The column has one value and one missing, and pandas cannot hold a missing value in an integer column, so it stores the column as floats. Nothing in the data changed, but a reviewer who reads 512.0 wonders why a memory limit has a decimal, and a consumer that expects an integer may reject it. Use pandas' nullable integer type:

df["limits.memory"] = df["limits.memory"].astype("Int64")

Now the cell prints 512, and the missing one stays empty. Apply the same to any integer column that can have gaps. It is the same effect that makes an Excel column of whole numbers sprout .0 after a round trip through pandas, and it is a cosmetic change that looks like a data change.

Fix 3: write the file so Excel opens it correctly

A CSV has no way to declare its encoding. Excel assumes the system's legacy code page unless the file starts with a UTF-8 byte order mark, so an accented name arrives as mojibake. Write with utf-8-sig:

df.to_csv("services.csv", index=False, encoding="utf-8-sig")
print(open("services.csv", "rb").read(60))

b'\xef\xbb\xbfname,port,tags,limits.cpu,limits.memory\napi,8080,web;publ'

The first three bytes, EF BB BF, are the mark. Reading the file back with the same encoding gives the clean header and rows:

name,port,tags,limits.cpu,limits.memory
api,8080,web;public,2,512.0
worker,9000,queue,1,

(That is the listing before the integer fix; with Int64 the 512.0 reads 512.) The BOM is harmless to most programs but not to all; a tool that does not expect it sees a strange character glued to the first column name. The trade-off and the other Excel traps are covered in opening CSV files in Excel.

Keep values from being "helped" by the spreadsheet

Excel reinterprets values when it opens a CSV: a code such as 1-2 becomes a date, a long identifier becomes scientific notation and 02134 loses its zero. This is the spreadsheet's behavior and not the CSV's, and Excel drops leading zeros when opening a CSV explains the options. For a review copy, importing through Data > From Text/CSV and setting the column type to text works. Or write the review copy as an .xlsx with pandas' to_excel, which stores each cell with its type. Choose the workbook when the data has codes.

The whole script

import sys

import pandas as pd
import yaml

rows = yaml.safe_load(open(sys.argv[1]))
df = pd.json_normalize(rows, sep=".")
for col in df.columns:
    if df[col].map(lambda v: isinstance(v, list)).any():
        df[col] = df[col].map(lambda v: ";".join(map(str, v)) if isinstance(v, list) else v)
    elif pd.api.types.is_float_dtype(df[col]):
        non_null = df[col].dropna()
        if len(non_null) and (non_null % 1 == 0).all():
            df[col] = df[col].astype("Int64")
df.to_csv(sys.argv[2], index=False, encoding="utf-8-sig")

The loop joins any list column and converts whole-number float columns to nullable integers. It guesses, so it will also convert a column of real measurements that happen to be whole numbers. For a review copy that is acceptable; for a data pipeline, declare the types.

What to do with the reviewer's comments

A reviewer will mark things in the spreadsheet: "this port is wrong", "missing limit". Capture those as a list of service names and fields, then make the edits in the YAML and regenerate the CSV. The alternative, converting the edited CSV back to YAML, loses comments, anchors, ordering and quoting, as round tripping YAML without losing comments shows from the other side. If you must accept edits in a spreadsheet, treat the spreadsheet as the source of record for those fields and generate the YAML from it every time, never by hand.

When a CSV is a poor view of a YAML file

The structure is deep. A Kubernetes manifest with containers, volumes and probes nested five levels deep flattens into dozens of sparse columns. Flatten to a long table of path and value instead, as in flattening nested YAML into rows.

Records have different shapes. If every service has different keys, the CSV is mostly empty cells. Review the YAML directly with a tool that understands it.

The file mixes kinds. A multi-document file with Deployments, Services and ConfigMaps is several tables. Split by kind and convert each.

Types matter to the reader. CSV has none, so a booleans column, a date column and a version column all become text.

For a quick look at a file in tabular form without a script, a CSV and JSON converter works in the browser. The pandas function is documented in the json_normalize reference, and the CSV format itself is specified in RFC 4180.