Skip to content
Bamboo Weekly

pandas DataFrame

Building a data frame by hand from dicts, lists, and other Python data.

When was the last time you actually typed out a data frame? In real work you almost never construct one by hand — you read one. read_csv and read_excel are what I reach for, and where a beginner should spend their time. pd.DataFrame() is not a daily method, and a page pretending otherwise would be lying to you.

Four situations genuinely call for it:

There is no separate Series page here, so both constructors live on this one. A series is a single column with an index; a data frame is a dict of them sharing one index.

Official documentation: pandas.DataFrame and pandas.Series.

The forms that earn their keep

pd.DataFrame({'net': [...], 'operator': [...]})     # dict of lists: one entry per column
pd.DataFrame([{'net': 'us'}, {'net': 'ak'}])        # list of dicts: one entry per row
pd.DataFrame(rows, columns=['net', 'operator'])     # list of lists: columns= is not optional
pd.DataFrame(data, index=[...], dtype='int16')      # row labels, and one dtype for everything
pd.Series([...], index=[...], name='operator')      # a single labeled column

The dict of lists is the common form, the one to reach for when typing a table out. The list of dicts is what an API hands back — one JSON object per record — and pd.DataFrame(records) handles the flat case directly; when the objects are nested, use json_normalize instead.

index= sets the row labels, which is how you make a lookup that .map() can use. dtype= applies to every column at once, so it only helps when the frame is all one type — mix text and numbers under dtype='int16' and you get ValueError: invalid literal for int() with base 10. For per-column control, build the frame and then astype it.

A worked example, on real data

The USGS earthquake feed labels every event with the network that reported it — us, ak, pr. Those codes are documented on the USGS site and nowhere in the download, which is the classic reason to type a frame by hand:

import pandas as pd

url = ('https://earthquake.usgs.gov/fdsnws/event/1/query.csv'
       '?starttime=2026-07-01&endtime=2026-08-01&minmagnitude=2.5')

df = pd.read_csv(url, usecols=['time', 'mag', 'net', 'place'])

networks = pd.DataFrame({
    'net': ['us', 'ak', 'pr', 'nc', 'tx', 'ci', 'hv'],
    'operator': ['USGS national catalog', 'Alaska Earthquake Center',
                 'Puerto Rico Seismic Network', 'Northern California',
                 'TexNet', 'Southern California',
                 'Hawaiian Volcano Observatory'],
})

(
    df
    .merge(networks, on='net', how='left')
    .groupby('operator')['mag'].agg(['count', 'max'])
    .sort_values('count', ascending=False)
    .round(2)
)

                              count   max
operator
USGS national catalog          2008  7.30
Alaska Earthquake Center        306  5.20
Puerto Rico Seismic Network     139  4.80
Northern California              41  4.42
TexNet                           33  5.00
Southern California              31  4.32
Hawaiian Volcano Observatory     21  4.53

Seven hand-typed rows turned 2,611 opaque codes into a table you can hand to someone else. When a lookup has exactly one output column, a series with an index says it more compactly, and .map() takes it directly:

operators = pd.Series(['USGS national catalog', 'Alaska Earthquake Center',
                       'Puerto Rico Seismic Network'],
                      index=['us', 'ak', 'pr'], name='operator')

df['net'].map(operators)

Four mistakes people make

Assuming a dict of lists and a dict of series behave the same way. Give pd.DataFrame a dict of lists with different lengths and it stops you: ValueError: All arrays must be of the same length. Give it a dict of series and it does not stop you — it aligns them on their indexes and fills the gaps with NaN:

s1 = pd.Series([2008, 306, 139], index=['us', 'ak', 'pr'])
s2 = pd.Series([7.3, 5.2, 4.8], index=['us', 'ak', 'hv'])

pd.DataFrame({'count': s1, 'max_mag': s2})

     count  max_mag
ak   306.0      5.2
hv     NaN      4.8
pr   139.0      NaN
us  2008.0      7.3

Four rows from two three-element inputs, in an order neither of them had, with count quietly promoted to float. The alignment is a feature when you are assembling computed results that share an index. It is also silent, so check .shape afterward.

Letting a list of dicts invent columns. Keys that differ between records each become their own column, padded with NaN. A single typo is enough:

pd.DataFrame([{'net': 'us', 'operator': 'USGS national catalog'},
              {'net': 'ak', 'opreator': 'Alaska Earthquake Center'}])

  net               operator                  opreator
0  us  USGS national catalog                       NaN
1  ak                    NaN  Alaska Earthquake Center

No error, three columns, and a NaN where your data is. When the records come from an API, check .columns before going any further.

Forgetting columns= on a list of lists. Pandas cannot know what your positional values are called, so it numbers them: pd.DataFrame([['us', 'USGS'], ['ak', 'Alaska']]) gives you columns 0 and 1. Legal, and awful — integer column names collide with positional thinking everywhere downstream.

Growing a frame row by row with pd.concat. This is the one I care most about. Every iteration copies the entire frame built so far, so cost grows faster than the row count, and the dtypes are lost as well. With 10,000 rows:

df = pd.DataFrame(columns=['net', 'mag'])
for one_row in rows:
    df = pd.concat([df, pd.DataFrame([one_row])], ignore_index=True)

1.48 seconds
net    object
mag    object

Accumulate plain Python dicts in the loop instead, and call the constructor once at the end:

df = pd.DataFrame(rows)

1.6 ms
net        str
mag    float64

Roughly 900 times faster, and the dtypes come out right. Double the rows and the loop more than doubles while the single call barely moves. If you find yourself appending to a data frame inside a loop, the loop should be building a list.

Where it shows up in Bamboo Weekly

Nine of the 455 Bamboo Weekly posts construct a frame with pd.DataFrame(), and five use pd.Series(). That is the honest scale of this method, and why the four situations above are the whole story.

Bamboo Weekly #17: Debt ceiling parses a fixed-width Treasury report line by line, appending each row to a list, then hands the finished list to one constructor. No columns= is passed, so it is also a live example of the integer column names above.

Bamboo Weekly #61: Solar eclipse turns a column of lists into a frame with pd.DataFrame(df['ECLIPSE'].to_list(), index=df.index). That index= is what lets the result line up with the original for a concat along the columns.

Bamboo Weekly #150: Kalshi walks nested API responses in a loop, appending a dict per market to an ordinary list, and only then calls pd.DataFrame(all_markets) — the last mistake, written the right way around.

Bamboo Weekly #115: Sahm rule builds pd.DataFrame({'UNRATE': s, '3_month_mean': s.rolling(window=3).mean().shift(1)}), a dict of series relying on index alignment to put a shifted rolling mean beside the raw unemployment rate.

Practice it

Work through a pd.DataFrame() exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/dataframe/

Go deeper

The far more common way to get a data frame is to read one, and read_csv and read_excel between them cover most of it. After that, merge is what a hand-built lookup table is for, concat glues frames together, and set_index turns a column into the row labels. The Pandas user guide's intro to data structures tours every input the constructors accept.

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

See it on real data

Below are the 18 Bamboo Weekly exercises that use DataFrame on real-world data — try each one, then study the worked solution.

Part of the Pandas Methods Index. See also practice by skill.