Creating an empty Pandas DataFrame, and then filling it

Question

I'm starting from the pandas DataFrame documentation here: Introduction to data structures

I'd like to iteratively fill the DataFrame with values in a time series kind of calculation. I'd like to initialize the DataFrame with columns A, B, and timestamp rows, all 0 or all NaN.

I'd then add initial values and go over this data calculating the new row from the row before, say row[A][t] = row[A][t-1]+1 or so.

I'm currently using the code as below, but I feel it's kind of ugly and there must be a way to do this with a DataFrame directly or just a better way in general.

import pandas as pd
import datetime as dt
import scipy as s
base = dt.datetime.today().date()
dates = [ base - dt.timedelta(days=x) for x in range(9, -1, -1) ]

valdict = {}
symbols = ['A','B', 'C']
for symb in symbols:
    valdict[symb] = pd.Series( s.zeros(len(dates)), dates )

for thedate in dates:
    if thedate > dates[0]:
        for symb in valdict:
            valdict[symb][thedate] = 1 + valdict[symb][thedate - dt.timedelta(days=1)]

Answer 1

NEVER grow a DataFrame row-wise!

TLDR: (just read the bold text)

Most answers here will tell you how to create an empty DataFrame and fill it out, but no one will tell you that it is a bad thing to do.

Here is my advice: Accumulate data in a list, not a DataFrame.

Use a list to collect your data, then initialise a DataFrame when you are ready. Either a list-of-lists or list-of-dicts format will work, pd.DataFrame accepts both.

data = []
for row in some_function_that_yields_data():
    data.append(row)

df = pd.DataFrame(data)

pd.DataFrame converts the list of rows (where each row is a scalar value) into a DataFrame. If your function yields DataFrames instead, call pd.concat.

Pros of this approach:

1. It is always cheaper to append to a list and create a DataFrame in one go than it is to create an empty DataFrame (or one of NaNs) and append to it over and over again.

2. Lists also take up less memory and are a much lighter data structure to work with, append, and remove (if needed).

3. dtypes are automatically inferred (rather than assigning object to all of them).

4. A RangeIndex is automatically created for your data, instead of you having to take care to assign the correct index to the row you are appending at each iteration.

If you aren't convinced yet, this is also mentioned in the documentation:

Iteratively appending rows to a DataFrame can be more computationally intensive than a single concatenate. A better solution is to append those rows to a list and then concatenate the list with the original DataFrame all at once.

pandas >= 2.0 update: append has been removed!

DataFrame.append was deprecated in version 1.4 and removed from the pandas API entirely in version 2.0. See also this github issue that originally proposed its deprecation.

---

---

These options are horrible

append or concat inside a loop

Here is the biggest mistake I've seen from beginners:

df = pd.DataFrame(columns=['A', 'B', 'C'])
for a, b, c in some_function_that_yields_data():
    df = df.append({'A': i, 'B': b, 'C': c}, ignore_index=True) # yuck
    # or similarly,
    # df = pd.concat([df, pd.Series({'A': i, 'B': b, 'C': c})], ignore_index=True)

Memory is re-allocated for every append or concat operation you have. Couple this with a loop and you have a quadratic complexity operation.

The other mistake associated with df.append is that users tend to forget append is not an in-place function, so the result must be assigned back. You also have to worry about the dtypes:

df = pd.DataFrame(columns=['A', 'B', 'C'])
df = df.append({'A': 1, 'B': 12.3, 'C': 'xyz'}, ignore_index=True)

df.dtypes
A     object   # yuck!
B    float64
C     object
dtype: object

Dealing with object columns is never a good thing, because pandas cannot vectorize operations on those columns. You will need to call the infer_objects() method to fix it:

df.infer_objects().dtypes
A      int64
B    float64
C     object
dtype: object

loc inside a loop

I have also seen loc used to append to a DataFrame that was created empty:

df = pd.DataFrame(columns=['A', 'B', 'C'])
for a, b, c in some_function_that_yields_data():
    df.loc[len(df)] = [a, b, c]

As before, you have not pre-allocated the amount of memory you need each time, so the memory is re-grown each time you create a new row. It's just as bad as append, and even more ugly.

Empty DataFrame of NaNs

And then, there's creating a DataFrame of NaNs, and all the caveats associated therewith.

df = pd.DataFrame(columns=['A', 'B', 'C'], index=range(5))
df
     A    B    C
0  NaN  NaN  NaN
1  NaN  NaN  NaN
2  NaN  NaN  NaN
3  NaN  NaN  NaN
4  NaN  NaN  NaN

It creates a DataFrame of object columns, like the others.

df.dtypes
A    object  # you DON'T want this
B    object
C    object
dtype: object

Appending still has all the issues as the methods above.

for i, (a, b, c) in enumerate(some_function_that_yields_data()):
    df.iloc[i] = [a, b, c]

---

---

The Proof is in the Pudding

Timing these methods is the fastest way to see just how much they differ in terms of their memory and utility.

Benchmarking code for reference.

Answer 2

Here's a couple of suggestions:

Use date_range for the index:

import datetime
import pandas as pd
import numpy as np

todays_date = datetime.datetime.now().date()
index = pd.date_range(todays_date-datetime.timedelta(10), periods=10, freq='D')

columns = ['A','B', 'C']

Note: we could create an empty DataFrame (with NaNs) simply by writing:

df_ = pd.DataFrame(index=index, columns=columns)
df_ = df_.fillna(0) # With 0s rather than NaNs

To do these type of calculations for the data, use a NumPy array:

data = np.array([np.arange(10)]*3).T

Hence we can create the DataFrame:

In [10]: df = pd.DataFrame(data, index=index, columns=columns)

In [11]: df
Out[11]:
            A  B  C
2012-11-29  0  0  0
2012-11-30  1  1  1
2012-12-01  2  2  2
2012-12-02  3  3  3
2012-12-03  4  4  4
2012-12-04  5  5  5
2012-12-05  6  6  6
2012-12-06  7  7  7
2012-12-07  8  8  8
2012-12-08  9  9  9

Answer 3

If you simply want to create an empty data frame and fill it with some incoming data frames later, try this:

newDF = pd.DataFrame() #creates a new dataframe that's empty
newDF = newDF.append(oldDF, ignore_index = True) # ignoring index is optional
# try printing some data from newDF
print newDF.head() #again optional 

In this example I am using this pandas doc to create a new data frame and then using append to write to the newDF with data from oldDF.

If I have to keep appending new data into this newDF from more than one oldDFs, I just use a for loop to iterate over pandas.DataFrame.append()

Note: append() is deprecated since version 1.4.0. Use concat().

Answer 4

Initialize empty frame with column names

import pandas as pd

col_names =  ['A', 'B', 'C']
my_df  = pd.DataFrame(columns = col_names)
my_df

Add a new record to a frame

my_df.loc[len(my_df)] = [2, 4, 5]

You also might want to pass a dictionary:

my_dic = {'A':2, 'B':4, 'C':5}
my_df.loc[len(my_df)] = my_dic 

Append another frame to your existing frame

col_names =  ['A', 'B', 'C']
my_df2  = pd.DataFrame(columns = col_names)
my_df = my_df.append(my_df2)

---

Performance considerations

If you are adding rows inside a loop consider performance issues. For around the first 1000 records "my_df.loc" performance is better, but it gradually becomes slower by increasing the number of records in the loop.

If you plan to do thins inside a big loop (say 10M‌ records or so), you are better off using a mixture of these two; fill a dataframe with iloc until the size gets around 1000, then append it to the original dataframe, and empty the temp dataframe. This would boost your performance by around 10 times.

Answer 5

Simply:

import numpy as np
import pandas as pd

df=pd.DataFrame(np.zeros([rows,columns])

Then fill it.

Answer 6

Assume a dataframe with 19 rows

index=range(0,19)
index

columns=['A']
test = pd.DataFrame(index=index, columns=columns)

Keeping Column A as a constant

test['A']=10

Keeping column b as a variable given by a loop

for x in range(0,19):
    test.loc[[x], 'b'] = pd.Series([x], index = [x])

You can replace the first x in pd.Series([x], index = [x]) with any value

Answer 7

This is my way to make a dynamic dataframe from several lists with a loop

x = [1,2,3,4,5,6,7,8]
y = [22,12,34,22,65,24,12,11]
z = ['as','ss','wa', 'ss','er','fd','ga','mf']
names = ['Bob', 'Liz', 'chop']

a loop

def dataF(x,y,z,names):
    res = []

    for t in zip(x,y,z):
        res.append(t)

    return pd.DataFrame(res,columns=names)

Result

dataF(x,y,z,names)

Answer 8

Pandas dataframes can be thought of as a dictionary of pandas columns (pandas Series). Just like a dictionary where adding a new key-value pair is inexpensive, adding a new column/columns is very efficient (and dataframes are meant to be grown horizontally like that).

df = pd.DataFrame()
df['A'] = range(0, 2000_000, 2)   # add one column
df[['B', 'C']] = ['a', 'b']       # add multiple columns

---

On the other hand, just like updating every value of a dictionary requires looping over the entire dictionary, enlarging a dataframe vertically by adding new rows is very inefficient. It's especially inefficient if new rows are added one-by-one in a loop (see this post for a benchmark comparing the possible options).

If new row values depend on previous row values as in the OP, then depending on the number of columns, it might be better to loop over a pre-initialized dataframe of zeros or grow a Python dictionary in a loop and construct a dataframe after (if there are more than 500 columns, it's probably better to loop over the dataframe). But it's never optimal to mix the two, in other words, growing a dictionary of pandas Series will be extremely slow.¹

dates = pd.date_range(end=pd.Timestamp('now'), periods=10000, freq='D').date
symbols = [f"col{i}" for i in range(10)]

# initialize a dataframe
df = pd.DataFrame(0, index=dates, columns=symbols)
# update it in a loop
for i, thedate in enumerate(df.index):
    if thedate > df.index[0]:
        df.loc[thedate] = df.loc[df.index[i-1]] + 1


# build a nested dictionary
data = {}
for i, thedate in enumerate(dates):
    for symb in symbols:
        if thedate > dates[0]:
            data[symb][thedate] = 1 + data[symb][dates[i-1]]
        else:
            data[symb] = {thedate: 0}
# construct a dataframe after
df1 = pd.DataFrame(data)

^{1: That said, for this specific example, cumsum() or even range() would seemingly work without even looping over the rows. This part of the answer is more about cases where looping is unavoidable, such as financial data manipulation etc.}