Adding an arbitrary number of pandas dataframe [duplicate]

Question

Let say I have below 2 pandas dataframes

import pandas as pd
dat1 = pd.DataFrame({'A' : [1999, -11234], 'B' : [88888, -345], 'C' : [-7777, -8978], 'D' : [7777, -8978]})
dat2 = pd.DataFrame({'A' : [999, 1234], 'B' : [8888, -345], 'C' : [-7777, -8978]})
Collection = [dat1, dat2]

In my original case, I actually have an arbitrary number of dataframes in the list Collection. Now I want to add all such dataframes with element-by-element addition, based on row and column matches (if in a dataframe, if a column and/or row is missing then corresponding value will be assumed as 0 in such addition).

Is there any direct method/function available to achieve this?

Sash Sinha · Accepted Answer · 2023-08-19 23:05:49Z

3

You can use functools.reduce along with pd.DataFrame.add to add an arbitrary number of dataframes together:

from functools import reduce
import pandas as pd

dat1 = pd.DataFrame({'A': [1999, -11234], 'B': [88888, -345], 'C': [-7777, -8978], 'D': [7777, -8978]})
dat2 = pd.DataFrame({'A': [999, 1234], 'B': [8888, -345], 'C': [-7777, -8978]})
dat3 = pd.DataFrame({'A': [100, 200], 'B': [300, -400], 'C': [500, 600], 'D': [700, 800]})
collection = [dat1, dat2, dat3]
result = reduce(lambda x, y: x.add(y, fill_value=0), collection)

This will sum the DataFrames in the collection list, filling any missing values with 0.

Output result:

index	A	B	C	D
0	3098	98076	-15054	8477.0
1	-9800	-1090	-17356	-8178.0

answered Aug 19, 2023 at 23:05

Sash Sinha

22.8k4 gold badges27 silver badges48 bronze badges

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

Comments

Nick · Accepted Answer · 2023-08-19 23:15:16Z

1

You could concat all the dataframes together, then use groupby and sum to get your desired result:

import pandas as pd

dat1 = pd.DataFrame({'A': [1999, -11234], 'B': [88888, -345], 'C': [-7777, -8978], 'D': [7777, -8978]})
dat2 = pd.DataFrame({'A': [999, 1234], 'B': [8888, -345], 'C': [-7777, -8978]})
dat3 = pd.DataFrame({'A': [100, 200], 'B': [300, -400], 'C': [500, 600], 'D': [700, 800]})
collection = [dat1, dat2, dat3]

pd.concat(collection).groupby(level=0).sum()

Output:

      A      B      C       D
0  3098  98076 -15054  8477.0
1 -9800  -1090 -17356 -8178.0

Thanks to @SashSinha for the data

answered Aug 19, 2023 at 23:15

Nick

147k23 gold badges67 silver badges106 bronze badges

Collectives™ on Stack Overflow

Adding an arbitrary number of pandas dataframe [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related