ENH: Row-wise dataframe builder

[clbarnes]

Feature Type

• Adding new functionality to pandas

• Changing existing functionality in pandas

• Removing existing functionality in pandas

Problem Description

I would like to build a dataframe row-by-row. This is a common use case. It is common for data to be ingested or calculated row-wise, but does not fit well with columnar memory models. The current solution (building from a list of lists) does not allow you to specify the dtypes of each column, just one dtype for the whole dataframe.

Feature Description

Add a new class, DataFrameBuilder (working title), whose constructor takes a list of columns and optionally a list of dtypes for those columns. Rows can then be added one by one in a memory-efficient way (i.e. not realising intermediate dataframes). Once all rows are added, a .build() method would realise the final dataframe with the correct column dtypes.

Alternative Solutions

The DataFrame constructor could take multiple dtypes, but this would further complicate an already extremely complicated class. Users could construct a dataframe of dtype object from a list of lists, and then manually cast each column's dtype afterwards (this could be an implementation of the DataFrameBuilder).

Additional Context

I wrote this implementation, which is small and maintainable, and it's become indispensable in practically every project where I use pandas: https://gist.github.com/clbarnes/9c53701e8b436603d236ac0ab26ff8a9

[jbrockmendel]

Several comments in the SO page suggest using pd.concat. Is there something about the use case that makes that unworkable?

[clbarnes]

As I understand it, that would require creating a new dataframe for every row, then concatenating them, which feels like a lot of boilerplate, and then still requires you to cast dtypes at the end. So it's

builder = DataFrameBuilder(
    ["a", "b", "c"],
    ["uint64", "float32", object]
)
builder.append_row([1, 2, 3])
builder.append_row([4, 5.5, "potato"])
df = builder.build()

vs

columns = ["a", "b", "c"]
dfs = []
dfs.append(DataFrame([[1, 2, 3]], columns=columns, dtype=object)
dfs.append(DataFrame([[4, 5.5, "potato"]], columns=columns, dtype=object)
df_tmp = pd.concat(dfs, ignore_index=True)
df = df_tmp.astype({"a": "uint64", "b": "float32", "c": object}, copy=False)

Not so different in terms of lines, but to me, the former reads a lot cleaner and reduces the number of dips in and out of pandas data types. Maybe that's faster that iterating over the internal lists and doing one big cast: if so, concat could always be just the internal implementation of the builder.

At that point you may as well just do

rows = []
rows.append([1, 2, 3])
rows.append([4, 5.5, "potato"])
df_tmp = pd.DataFrame(rows, columns=["a", "b", "c"], dtype=object)
df = df_tmp.astype({"a": "uint64", "b": "float32"}, copy=False)

I personally prefer the builder over this because your columns and dtypes are defined up front and row lengths can be checked as they come in, but recognise that's a pretty weak argument. Plus the not having to create a temporary dataframe and then cast all the columns straight away.

[topper-123]

Do you have a example showing the performance difference between the DataFrameBuilder approach and the other approaches?

[mroeschke]

Thanks for the request but it appears there hasn't been much interest in this issue from the core team and community over the years so closing.