In pandas, how does a DataFrame built from a dict of lists differ from one built from a list of dicts like API records?
answer
- column-oriented versus row-oriented input
- keys become column labels both ways
- equal lengths versus ragged records
- an absent key leaves a hole
- integer column quietly turns float64
basics
~20 sA dict of lists is column-oriented: each key is a column and all lists must share one length. A list of dicts is row-oriented: each dict is a row, columns are the union of keys, and a missing key becomes NaN.
solid answer
~40 sBoth shapes go straight into `pd.DataFrame(...)` and both get a default `RangeIndex` unless you pass `index=`. With a **dict of lists**, each key is a column in insertion order, and lists of different lengths raise `ValueError: All arrays must be of the same length`. With a **list of dicts** (what an orders API usually returns), each dict is a row and the columns are the union of all keys, in the order pandas first sees them, so a key that appears only in a later record is appended at the end. A record that lacks a key gets `NaN` in that cell, and because NumPy `int64` cannot hold `NaN`, an integer field such as `qty` comes back as `float64`. So records with missing keys build without complaint, with holes you should check with `info()`.
code
python · 18 linesimport pandas as pd
records = [
{"order_id": "A1", "qty": 2, "price": 9.5},
{"order_id": "A2", "qty": 1, "price": 4.0, "coupon": "SPRING"},
{"order_id": "A3", "price": 12.0},
]
from_rows = pd.DataFrame(records)
print(from_rows.columns.tolist())
print(from_rows["qty"].tolist(), from_rows["qty"].dtype)
from_cols = pd.DataFrame({"order_id": ["A1", "A2"], "qty": [2, 1]})
print(from_cols.shape, from_cols["qty"].dtype)
try:
pd.DataFrame({"order_id": ["A1", "A2", "A3"], "qty": [2, 1]})
except ValueError as exc:
print("ValueError:", exc)go deeper
Recall which input is column-oriented and which is row-oriented, and that missing keys in records become NaN rather than an error.
Explain the column order rules, the equal-length ValueError and why one absent key turns an integer field into float64.
Show that you verify API records after building them: shape for row count, info for non-null counts, and an explicit columns list so page-to-page order cannot drift.
Frame the tradeoff of tolerant construction: records that never fail to build move schema checking to the team, so decide where a missing field is caught.
## Two orientations of the same table A pandas **DataFrame** is a two-dimensional table of labelled rows and labelled columns, where every column holds one **dtype** (data type). The `pd.DataFrame` constructor accepts many input shapes, and the two you meet most often describe the same table from opposite directions: - a **dict of lists** (or arrays) is **column-oriented**: one key per column, one list of values per column; - a **list of dicts** is **row-oriented**: one dict per row, one key-value pair per cell. Python data from an HTTP API usually arrives as the second shape, because JSON responses list records. Data you assemble yourself, such as columns computed in a loop, is usually the first. ## Dict of lists: columns first When you pass `{"order_id": [...], "qty": [...]}`, pandas does the following: - each key becomes a **column label**, and the column order follows the dict's insertion order; - each list becomes the values of that column, and pandas infers one dtype per column from them; - every list must have the **same length**; otherwise the constructor raises `ValueError: All arrays must be of the same length`, it never pads or truncates; - the row labels are a default `RangeIndex` (0, 1, 2, ...) unless you pass `index=` with one label per row. The strict length check is a feature: a column that came out one value short is a bug upstream, and the constructor refuses to guess which row is missing. ## List of dicts: rows first When you pass `[{"order_id": "A1", "qty": 2}, {"order_id": "A2", "price": 4.0}]`, pandas treats each dict as one row: - the column labels are the **union** of the keys across all records; - the column order is the order in which keys are **first seen**, so a field that only appears in record 40 becomes the last column; - a record that lacks a key gets a missing value in that cell, shown as `NaN`; - a key present in some records but not others still builds; nothing raises. That last point is the trap. Real APIs omit optional fields (a `coupon` that most orders lack, a `qty` a buggy client dropped), and the frame builds silently with holes. A field of integers with one hole cannot stay `int64`, because NumPy's integer dtype has no slot for a missing value, so pandas upcasts it to `float64` and `2` prints as `2.0`. ## What pandas infers, side by side | Question | Dict of lists | List of dicts | |---|---|---| | What becomes a column? | each key | the union of all keys | | Column order | dict insertion order | order keys are first seen | | Unequal or missing data | `ValueError` on unequal lengths | `NaN` in the missing cell | | Default row labels | `RangeIndex` | `RangeIndex` | | Integer field with a gap | not possible (lengths must match) | becomes `float64` | ## Traps with API records 1. **A nested object stays nested.** If each record carries `"customer": {"id": 7}`, that column holds whole Python dicts in an `object` column. `pd.json_normalize(records)` flattens them into dotted columns such as `customer.id`. 2. **The ID is just another column.** The order ID is not used as the row label unless you ask: pass `index=` with a list of labels, use `DataFrame.from_records(records, index="order_id")`, which moves that field out of the columns, or call `set_index` afterwards (the lesson on axis labels covers that). 3. **`columns=` selects; it does not rename.** Passing `columns=["order_id", "qty"]` with a list of dicts keeps only those keys, in that order, and a name the records lack comes back as an all-NaN column. A typo therefore yields a silent empty column, not an error. 4. **Order can drift between pages.** If page 2 of an API response is built separately and its first record carries keys in a different order, the two frames' column orders differ; fix the order with `columns=` when you build them. ## Checking what you built Right after construction, confirm the result rather than trusting it: - `df.shape` gives `(rows, columns)` and catches a wrong record count; - `df.dtypes` or `df.info()` shows which numeric fields became `float64` because of a gap, and `info()` also prints each column's non-null count, so `2 non-null` out of `3 entries` locates the hole; - `df.head()` shows the first five rows, enough to see whether a nested dict or an unexpected column slipped in. The habit worth stating in an interview: pick the constructor input that matches the data's natural orientation, then verify columns, row count and dtypes before the first transformation, because a list of dicts never complains about missing fields.
- What happens to a nested object inside each record, such as a customer dict?The constructor does not flatten it: the `customer` column holds whole Python dicts in an `object` column, which is awkward to filter or aggregate. `pd.json_normalize(records)` flattens nested keys into dotted columns such as `customer.id` and `customer.tier`, and its `sep` argument changes the separator.
- How do you make the order ID the row label at construction time?Pass `index=` with one label per row, or use `DataFrame.from_records(records, index="order_id")`, which takes the named field out of the columns and uses it as the index. Calling `set_index("order_id")` after construction gives the same result in a separate step.
- Why does `qty` print as 2.0 instead of 2 when one record lacks it?The default integer dtype is NumPy `int64`, which has no representation for a missing value. To store `NaN` for the absent record, pandas upcasts the whole column to `float64`. A nullable dtype such as `Int64` keeps integers with a `pd.NA` marker instead, which the lesson on NA-aware dtypes covers.
- A list of dicts raises an error when records have different keys
- Missing keys become None and an integer column stays int64
- Columns built from records are sorted alphabetically
- A dict of unequal-length lists is padded with NaN
- The keys of a dict of lists become row labels by default