Efficient way to add rows to dataframe

I used this answer's df.loc[i] = [new_data] suggestion, but I have > 500,000 rows and that was very slow.

While the answers given are good for the OP's question, I found it more efficient, when dealing with large numbers of rows up front (instead of the trickling in described by the OP) to use csvwriter to add data to an in memory CSV object, then finally use pandas.read_csv(csv) to generate the desired DataFrame output.

from io import BytesIO
from csv import writer 
import pandas as pd

output = BytesIO()
csv_writer = writer(output)

for row in iterable_object:
    csv_writer.writerow(row)

output.seek(0) # we need to get back to the start of the BytesIO
df = pd.read_csv(output)
return df

This, for ~500,000 rows was 1000x faster and as the row count grows the speed improvement will only get larger (the df.loc[1] = [data] will get a lot slower comparatively)

Tom Harvey's solution works well. However, I would like to add a simpler solution based on pandas.DataFrame.from_dict.

By adding the data of a row in a list and then this list to a dictionary, you can then use .from_dict(dict) to create a dataframe without iteration.

If each value of the dictionary is a row, you can use just:

pd.DataFrame.from_dict(dictionary, orient='index')

Small example:

# Dictionary containing the data
dic = {
    'row_1': ['some', 'test', 'values', 78, 90],
    'row_2': ['some', 'test', 'values', 100, 589]}

# Creation of the dataframe
df = pd.DataFrame.from_dict(dic, orient='index')
df

          0     1       2    3    4
row_1  some  test  values   78   90
row_2  some  test  values  100  589

You need to split the problem into two parts:

1. Accepting the data (collecting it) every 30 seconds efficiently.
2. Processing the data once its collected.

If your data is critical (that is, you cannot afford to lose it) - send it to a queue and then read it from the queue in batches.

The queue will provide reliable (guaranteed) acceptance and that your data will not be lost.

You can read the data from the queue and dump it in a database.

Now your Python app simply reads from the database and does the analysis at whatever interval makes sense for the application - perhaps you want to do hourly averages; in this case you would run your script each hour to pull the data from the db and perhaps write the results in another database / table / file.

The bottom line - split the collecting and analyzing parts of your application.

Assuming that your dataframe is indexed in order you can:

First check to see what the next index value is to create a new row:

myindex = df.shape[0]+1 

Then use 'at' to write to each desired column

df.at[myindex,'A']=val1
df.at[myindex,'B']=val2
df.at[myindex,'C']=val3

I had 700K rows of data returned from SQL server. All of the above took too long for me. The following approach cut the time significantly.

from collections import defaultdict
dict1 = defaultdict(list)

for row in results:

   dict1['column_name1'] = row['column_name1']


   dict1['column_name20'] = row['column_name20']

df = pd.DataFrame(dict1)

This is all I needed.

sundance's answer might be correct in terms of usage, but the benchmark is just wrong. As correctly pointed out by moobie, an index 3 already exists in this example, which makes access way quicker than with a non-existent index. Have a look at this:

%%timeit
test = pd.DataFrame({"A": [1,2,3], "B": [1,2,3], "C": [1,2,3]})
for i in range(0,1000):
    testrow = pd.DataFrame([0,0,0])
    pd.concat([test[:1], testrow, test[1:]])

2.15 s ± 88 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
test = pd.DataFrame({"A": [1,2,3], "B": [1,2,3], "C": [1,2,3]})
for i in range(0,1000):
    test2 = pd.DataFrame({'A': 0, 'B': 0, 'C': 0}, index=[i+0.5])
    test.append(test2, ignore_index=False)
test.sort_index().reset_index(drop=True)

972 ms ± 14.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
test = pd.DataFrame({"A": [1,2,3], "B": [1,2,3], "C": [1,2,3]})
for i in range(0,1000):
    test3 = [0,0,0]
    test.loc[i+0.5] = test3
test.reset_index(drop=True)

1.13 s ± 46 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Of course, this is purely synthetic, and I admittedly wasn't expecting these results, but it seems that with non-existent indices .loc and .append perform quite similarly. Just leaving this here.

My coworker told me to make a list of dictionary entries, then push the finished list into a dataframe. Compared to pushing one dictionary at a time into a dataframe, the list approach was instantaneous.

This code culled through ~54k records and looks only for those after my targ_datetime value, then writes the desired value back to a list, and then to df_out:

df_out = pd.DataFrame()
df_len = df.count()
counter = 1
list_out = []
targ_datetime = datetime.datetime.fromisoformat('2021-12-30 00:00:00')
for rec in df.selectExpr("CAST(data as STRING) as data").take(df_len):
  j = jsonx.loads(rec[0])
  NewImage = j['dynamodb']['NewImage']
  NewImage['eventName'] = j['eventName']
  if j.get('dynamodb').get('NewImage').get('UPDATED_AT') != None:
    ts = datetime.datetime.fromisoformat(str(j['dynamodb']['NewImage']['UPDATED_AT']).replace('T', ' ')[0:-5])
  else:
    ts = datetime.datetime.fromtimestamp(j['dynamodb']['ApproximateCreationDateTime']/1000)
  if ts >= targ_datetime:
    #df_out = df_out.append(pd.Series(NewImage.values(), index=NewImage.keys()), ignore_index=True)
    j['dynamodb']['NewImage']['UPDATED_AT'] = ts
    list_out.append(NewImage)
    counter = counter +1
  #if counter > 10: break
df_out = pd.DataFrame(list_out)

Strange, no one suggested practical piece of code. So, we see, pandas makes a copy, rearranges memory, checks if index is unique when each row is added... This all is super slow.

So we'll be adding our data to some native python structures (lists and dicts), and we'll turn it into a dataframe as the last step.

df = pd.DataFrame(
    data = {
        'My primary column': [],
        'Another columns': [],
        'One more column': [],
    },
    index = None # even if you need it indexed, we can set it later
)
data_add = [] # we'll be adding records here, it's fast;
# then we'll just append it to a dataframe in one op, it's fast

for record in read_data_source:
    # ... build a row with every column, I have it as list
    row_add_list = [ col1, col2, ... ]
    # we need it as dict, and it's easy to convert
    row_add = { col[0]: col[1] for col in zip(df.columns,row_add_list) }
    data_add.append(row_add)

# now we convert it back to pandas dataframe object
df = pd.concat(
    [
        df,
        pd.DataFrame(data_add)
    ],
    ignore_index = True, # it's fast to ignore index, so we just append
)
# I said, maybe we need index
# if we need, we can easily set it now, one time
# so it is not causing slowness when adding every row
df.set_index('My primary column',inplace=True)
return df

Now you only have to wait couple minutes when the file is saved (maybe it's because my file is quite complex with formulas and formatting); constructing data is now fast