Create a Pandas Dataframe by appending one row at a time

python

dataframe

performance

data-transformation

byAnton Shumikhin·Aug 18, 2024

To append rows to a Pandas DataFrame in a swifty efficient way, start by building a list of dictionaries. Convert it all at once into a DataFrame, like so:

import pandas as pd

rows_list = [{'col1': i, 'col2': i+1} for i in range(5)] # "range(5)" because who likes more than five rows, right?
df = pd.DataFrame(rows_list)

Avoid df.append() within loops—it's not a fan of hide-and-seek, and finds recreating the DataFrame each time an exhausting game.

Ninja tactics for one-row additions

Time to undertake a sparse task—appending a single row or a handful. In this scenario, employ .loc[i]:

df.loc[len(df)] = {'col1': 10, 'col2': 20} # Let's append that row like a ninja, swift and silent!

Before rushing to use this method for bulk additions, remember: "With great append power, comes great inefficiency responsibility."

Space preallocation: Blessing or a trap?

Preallocating space for your DataFrame is like calling shotgun for a seat in advanced—it saves you the hustle but remember to be a realist about the size of the DataFrame:

import numpy as np

df = pd.DataFrame(index=range(10000), columns=['A', 'B'])
for i in range(10000):
    df.loc[i] = [np.random.randint(10), 'name' + str(i)] # "name" + str(i) to brag about our unique IDs generation skills!

Caution: Overdoing it can turn preallocation into precariously memory-consuming luggage.

Clever DataFrame crafting

Let's play Transformers by converting list of tuples or dicts into an elegant DataFrame:

data_tuples = [(i, i + 1) for i in range(100)]
df_tup = pd.DataFrame(data_tuples, columns=['col1', 'col2']) # Transform! Autobots, roll out!

data_dicts = [{'col1': i, 'col2': i + 1} for i in range(100)]
df_dict = pd.DataFrame(data_dicts)

The dictionary version is basically the Optimus Prime in this scenario—naturally faster and ready to adapt to dynamical row data!

The sprint towards performance

If your acceptance criteria include a large-scale row append, you can sprint towards efficiency by using pd.concat or crafting DataFrame from a list of Series:

series_list = [pd.Series({'col1': i, 'col2': i + 1}) for i in range(100)]
df_series = pd.concat(series_list, axis=1).transpose()  # Bolt would be envious!

This bulk operation is not just colleagues with pd.concat; it's close knight buddies designed with memory efficiency in mind.

Creating unique IDs

We all like to stand out. The trick of 'name' + str(i) gives rows their moment of uniqueness:

'row' + str(i) # Could have been 'Bond' + str(i), but I like my rows numbered, not shaken or stirred.

Performance metrics

Show your code's speed by using time.perf_counter(). Let it be the Usain Bolt of the code world:

import time

start_time = time.perf_counter()
# Your code here
end_time = time.perf_counter()
print(f'Executed in {end_time - start_time} seconds') # Who needs speed when you have patience...which I don't.

Upcoming changes alert

Be future-ready! The .append() method will soon be a Panda from the past—it's aiming to retire by Pandas 2.0.

Trade-offs: Convenience vs performance

Bring balance to your code: weigh in between simplicity and speed. The loc steroid might scale up DataFrame, but it might cost you an arm and a leg in performance. Be one with the Zen of Python.

Handling large datasets like a pro

To tame large datasets, master the art of preallocation, generate data with the swift numpy.random.randint, convert lists into DataFrames after accumulating data, and use pd.concat after completing your loop for memory-efficient, mega DataFrame joining. Create Hoodini-like illusions of less memory and faster speed.

Efficient data transformation adopting Pandas

Efficiency is the name of the game. Play to win with vectorized operations with Pandas apply(), map(), and NumPy ufuncs. Channel your inner cat and use categorical dtypes for repetitive string data to save memories, and utilize Pandas groupby(), pivot_table() for no-sweat data reshaping.