Should we use for loop or list comprehension when creating DataFrames from multiple data sources?

user_3471881 0
Viewed 969

This question is related to @jpp:s answer in Merging files with similar name convention to a dataframe and the decision to mark an earlier thread (Put csv-files in separate dataframes depending on filename) as duplicate because the three answers in that thread were either not working (2/3) or poor (1/3).

Disregarding the answers that were not working, one answer (my answer) was said to be of poor quality because "using concat within a for loop is explicitly not recommended by the docs".

The criticised method:

dataframes = {}
for filename in filenames:
    _df = pd.read_csv(filename)
    key = filename[:3]
    try:
       dataframes[key] = pd.concat([dataframes[key], _df], ignore_index=True)
    except KeyError:
       dataframes[key] = _df

The accepted method (dd is a dictionary where each value is a list of filenames and each key is the first three characters of each filename):

dict_of_dfs
for k, v in dd.items():
    dict_of_dfs[k] = pd.concat([pd.read_csv(fn) for fn in v], ignore_index=True)

Now I agree that list comprehension within the concat call (the accepted method) is more efficient than a for loop where concat is called on each DataFrame.

But does this mean that we should always create DataFrames from multiple data sources by using list comprehension in the concat call (or append) and that using a for loop is so poor that it is actually wrong? And what about readability? I personally (of course) think my criticised method is more readable.

If we read the pandas docs on DataFrame.append we learn that neither for loops or list comprehension are "recommended methods for generating DataFrames":

The following, while not recommended methods for generating DataFrames, show two ways to generate a DataFrame from multiple data sources.

Less efficient:

>>> df = pd.DataFrame(columns=['A'])
>>> for i in range(5):
...     df = df.append({'A': i}, ignore_index=True)
>>> df

   A
0  0
1  1
2  2
3  3
4  4

More efficient:

>>> pd.concat([pd.DataFrame([i], columns=['A']) for i in range(5)],
...           ignore_index=True)

   A
0  0
1  1
2  2
3  3
4  4

So. My questions are as follows:

  1. Is looping and using concat on multiple data sources to create one or multiple instance(s) of DataFrame so poor that it is wrong?

  2. Should we always use list comprehension in a case like this?

  3. The docs don't seem to recommend using neither list comprehension or for loop, so what is the recommended way of creating DataFrame(s) from multiple data sources?

I really appreciate your answers @piRSquared and @jpp. I'm still not convinced about the categorical dismissal of concat in for loops as being poor to the point of being wrong while list comprehensions are correct and accepted.

Given the following test data:

df = pd.DataFrame({'A': np.arange(0, 25000), 'B': np.arange(0, 25000)})

for i in range(0, 50):
    df.to_csv('{}.csv'.format(i))

Methods:

def conc_inside_loop(filenames):
    df = None
    for filename in filenames:

        if df is None:
           df = pd.read_csv(filename)
           continue

        df = pd.concat([df, pd.read_csv(filename)], ignore_index=True)

    return df

def conc_list_comprehension(filenames):
    return pd.concat([pd.read_csv(filename) for filename in filenames], ignore_index=True)

Times:

>> %timeit -n 10 conc_inside_loop(glob.glob('*.csv'))
460 ms ± 15.4 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)


>> %timeit -n 10 conc_list_comprehension(glob.glob('*.csv'))
363 ms ± 32.9 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

Obviously list comprehension is more efficient (I already said that I understand that). But the differences are not huge. I don't think you can call one method poor to the point of being wrong and the other one correct given the differences we see here.

As stated by @piRSquared the last question is too broad. But a third way would be to use concat outside the for loop:

def conc_outside_loop(filenames):
    
    df_list = []
    for filename in filenames:
        df_list.append(pd.read_csv(filename))

    return pd.concat(df_list, ignore_index=True)

>> %timeit -n 10 conc_outside_loop(glob.glob('*.csv'))
344 ms ± 23.4 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
pandas dataframe python
2 Answers
  1. Is looping and using concat on multiple data sources to create one or multiple instance(s) of DataFrame so poor that it is wrong?

Yes! Pandas is great. But you should avoid at all cost the unnecessary production of Pandas objects. Creating Pandas objects can be expensive, DataFrames more than Series but this is probably True for all python. For the "criticized" method: Within a loop you create a Pandas object that will be overwritten in the next iteration of the loop. You should instead think how to gather your data in order to produce the Pandas object at the end of the gathering.

  1. Should we always use list comprehension in a case like this?

No! As I said above, think of it as gathering data in preparation for the construction of the Pandas object. A comprehension is only one such way to gather.

  1. The docs don't seem to recommend using neither list comprehension or for loop, so what is the recommended way of creating DataFrame(s) from multiple data sources?

This is too broad. A case can be made for many approaches. Just don't use concat or append in a loop. I'd call that wrong just about every time.

And by "every time" I don't actually mean "every time". What I DO mean is that you should never create a dataframe at some point prior to a loop, then loop and at each iteration go through the trouble of appending something to the prior initialized dataframe. Every iteration becomes very expensive. In the case of the "Accepted" answer: it assigns a dataframe to a dictionary key and is then left alone. It isn't repeatedly messed with.

user_23366540

Let's be clear, pd.DataFrame.append / pd.concat in a loop is not recommended. @piRSquared's answer explains why, the docs are also explicit on that matter.

The reason lies in how NumPy arrays are structured. You can't append / concatenate them efficiently; these operations require making copies of data. The operations are memory intensive and generally inefficient.

As such, the "accepted" method is the lesser of two evils, as you're performing a relatively small number of pd.concat calls versus one for every filename in the input list via the "criticized" method.

Concatenate just once

You can concatenate all your dataframes and then perform a GroupBy operation:

df = pd.concat([pd.read_csv(fn).assign(file=fn.split('_')[0]) for fn in v],
               ignore_index=True)

dict_of_dfs = dict(tuple(df.groupby('file')))

The key, pardon the pun, is to reduce the number of concatenation operations.

user_92095460
Related