How to Concat Thousands of Pandas Dataframes Generated by a for Loop Efficiently?
Thousands of Dfs of Consistent Columns Are Being Generated in a for Loop Reading Different Files, and I'm Trying to Merge / Concat / Append Them into a Single...
Thousands of dfs of consistent columns are being generated in a for loop reading different files, and I'm trying to merge / concat / append them into a single df, combined:
combined = pd.DataFrame()
for i in range(1,1000): # demo only
global combined
generate_df() # df is created here
combined = pd.concat([combined, df])
This is initially fast but slows as combined grows, eventually becoming unusably slow. This answer on how to append rows explains how adding rows to a dict and then creating a df is most efficient but I can't figure out how to do that with to_dict.
What's a good way to to this? Am I approaching this the wrong way?
3 Answers
You can create list of DataFrames and then use concat only once:
dfs = []
for i in range(1,1000): # demo only
global combined
generate_df() # df is created here
dfs.append(df)
combined = pd.concat(dfs)
The fastest way is building a list of dictionaries and building the dataframe only once at the end:
rows = []
for i in range(1, 1000):
# Instead of generating a dataframe, generate a dictionary
dictionary = generate_dictionary()
rows.append(dictionary)
combined = pd.DataFrame(rows)
This is about 100 times faster that concatenating dataframes, as is proved by the benchmark here.
- Use
concatonly once at the end. - Sort the index of each DataFrame. In my production code this sort didn't take long yet reduced the processing time of
concatfrom 10 + seconds to less than one second!
dfs = []
for i in range(1,1000): # demo only
global combined
df = generate_df() # df is created here
df.sort_index(inplace=True)
dfs.append(df)
combined = pd.concat(dfs)
James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.
