Using pandas .append within for loop

Question

I am appending rows to a pandas DataFrame within a for loop, but at the end the dataframe is always empty. I don't want to add the rows to an array and then call the DataFrame constructer, because my actual for loop handles lots of data. I also tried pd.concat without success. Could anyone highlight what I am missing to make the append statement work? Here's a dummy example:

import pandas as pd
import numpy as np

data = pd.DataFrame([])

for i in np.arange(0, 4):
    if i % 2 == 0:
        data.append(pd.DataFrame({'A': i, 'B': i + 1}, index=[0]), ignore_index=True)
    else:
        data.append(pd.DataFrame({'A': i}, index=[0]), ignore_index=True)

print data.head()

Empty DataFrame
Columns: []
Index: []
[Finished in 0.676s]

Does this answer your question? Appending pandas dataframes generated in a for loop — AMC
– AMC, Commented May 18, 2020 at 2:34

Alexander · Accepted Answer · 2016-05-03 17:49:01Z

69

Every time you call append, Pandas returns a copy of the original dataframe plus your new row. This is called quadratic copy, and it is an O(N^2) operation that will quickly become very slow (especially since you have lots of data).

In your case, I would recommend using lists, appending to them, and then calling the dataframe constructor.

a_list = []
b_list = []
for data in my_data:
    a, b = process_data(data)
    a_list.append(a)
    b_list.append(b)
df = pd.DataFrame({'A': a_list, 'B': b_list})
del a_list, b_list

Timings

%%timeit
data = pd.DataFrame([])
for i in np.arange(0, 10000):
    if i % 2 == 0:
        data = data.append(pd.DataFrame({'A': i, 'B': i + 1}, index=[0]), ignore_index=True)
else:
    data = data.append(pd.DataFrame({'A': i}, index=[0]), ignore_index=True)
1 loops, best of 3: 6.8 s per loop

%%timeit
a_list = []
b_list = []
for i in np.arange(0, 10000):
    if i % 2 == 0:
        a_list.append(i)
        b_list.append(i + 1)
    else:
        a_list.append(i)
        b_list.append(None)
data = pd.DataFrame({'A': a_list, 'B': b_list})
100 loops, best of 3: 8.54 ms per loop

edited May 3, 2016 at 17:49

answered May 3, 2016 at 16:33

Alexander

111k32 gold badges212 silver badges208 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

calpyte Over a year ago

Alright, so saving it to an array and then calling the DataFrame is actually faster then. Thanks!

johnchase · Accepted Answer · 2020-03-18 16:08:20Z

52

You need to set the the variable data equal to the appended data frame. Unlike the append method on a python list the pandas append does not happen in place

import pandas as pd
import numpy as np

data = pd.DataFrame([])

for i in np.arange(0, 4):
    if i % 2 == 0:
        data = data.append(pd.DataFrame({'A': i, 'B': i + 1}, index=[0]), ignore_index=True)
    else:
        data = data.append(pd.DataFrame({'A': i}, index=[0]), ignore_index=True)

print(data.head())

   A    B
0  0  1.0
1  2  3.0
2  3  NaN

NOTE: This answer aims to answer the question as it was posed. It is not however the optimal strategy for combining large numbers of dataframes. For a more optimal solution have a look at Alexander's answer below

edited Mar 18, 2020 at 16:08

answered May 3, 2016 at 16:22

johnchase

13.8k7 gold badges44 silver badges70 bronze badges

1 Comment

calpyte Over a year ago

Thanks that works! Kinda silly that I didn't think of that.

Montresor · Accepted Answer · 2022-09-16 05:57:49Z

2

When you use data.append(pd.DataFrame[['1','2'],['3','4']], ignore_index=True) the result must be assigned back to a dataframe. The result will contain the collated data eg.

data = data.append(pd.DataFrame([['1','2'],['3','4']])) <= use this in the loop

edited Sep 16, 2022 at 5:57

Montresor

8668 silver badges22 bronze badges

answered Sep 9, 2022 at 1:32

rohit_

216 bronze badges

Comments

Mike Müller · Accepted Answer · 2016-05-03 18:58:13Z

1

You can build your dataframe without a loop:

n = 4
data = pd.DataFrame({'A': np.arange(n)})
data['B'] = np.NaN
data.loc[data['A'] % 2 == 0, 'B'] = data['A'] + 1

For:

n = 10000

This is a bit faster:

%%timeit
data = pd.DataFrame({'A': np.arange(n)})
data['B'] = np.NaN
data.loc[data['A'] % 2 == 0, 'B'] = data['A'] + 1

100 loops, best of 3: 3.3 ms per loop

vs.

%%timeit
a_list = []
b_list = []
for i in np.arange(n):
    if i % 2 == 0:
        a_list.append(i)
        b_list.append(i + 1)
    else:
        a_list.append(i)
        b_list.append(None)
data1 = pd.DataFrame({'A': a_list, 'B': b_list})

100 loops, best of 3: 12.4 ms per loop

answered May 3, 2016 at 18:58

Mike Müller

86k21 gold badges174 silver badges165 bronze badges

1 Comment

Dan Fiorino Over a year ago

The question is not about efficiently creating the dummy dataframe example.

Collectives™ on Stack Overflow

Using pandas .append within for loop

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related