Add data from multiple dataframes by column index

Question

I have multiple data frames that looks similar to this:

date                   val1     val2     val3    val4
2020-11-06 10:00:00     0.5      1.0      1.1     1.2
2020-11-06 11:00:00     1.5      1.3      0.1     1.3
2020-11-06 12:00:00     4.5      1.2      2.1     5.2

date                   val1     val2     val3    val4
2020-11-06 10:00:00     1.5      2.0      2.1     2.2
2020-11-06 11:00:00     2.5      2.3      1.1     2.3
2020-11-06 12:00:00     5.5      2.2      3.1     6.2

So some date, and then some values. In each data frame the dates are the same, which kind of makes the date the id. What I would like to do is just add the values from all dataframe indexed by the date. So in the above example the resulting dataframe should be:

date                   val1     val2     val3    val4
2020-11-06 10:00:00     2.0      3.0      3.2     3.4
2020-11-06 11:00:00     4.0      3.6      1.2     3.6
2020-11-06 12:00:00    10.0      3.4      5.2    11.4

set the date as index and add the two data frames.

Quang Hoang
– Quang Hoang

2020-11-09 15:12:15 +00:00
Commented Nov 9, 2020 at 15:12 — Quang Hoang
– Quang Hoang, Commented Nov 9, 2020 at 15:12
stackoverflow.com/questions/38472276/…

Andrea
– Andrea

2020-11-09 15:15:11 +00:00
Commented Nov 9, 2020 at 15:15 — Andrea
– Andrea, Commented Nov 9, 2020 at 15:15

Cameron Riddell · Accepted Answer · 2020-11-09 15:14:02Z

You just need to set the indices and add the dataframes:

out = df1.set_index("date") + df2.set_index("date")

print(out)
                     val1  val2  val3  val4
date                                       
2020-11-06 10:00:00   2.0   3.0   3.2   3.4
2020-11-06 11:00:00   4.0   3.6   1.2   3.6
2020-11-06 12:00:00  10.0   3.4   5.2  11.4

Then if you don't want "date" to be the official index you can simply call .reset_index()

out = out.reset_index()
print(out)
                  date  val1  val2  val3  val4
0  2020-11-06 10:00:00   2.0   3.0   3.2   3.4
1  2020-11-06 11:00:00   4.0   3.6   1.2   3.6
2  2020-11-06 12:00:00  10.0   3.4   5.2  11.4

Another approach is to use the built-in sum with a generator expression so that you don't need to explicitly set the index on each DataFrame:

out = sum(df.set_index("date") for df in (df1, df2))

print(out)
                     val1  val2  val3  val4
date                                       
2020-11-06 10:00:00   2.0   3.0   3.2   3.4
2020-11-06 11:00:00   4.0   3.6   1.2   3.6
2020-11-06 12:00:00  10.0   3.4   5.2  11.4

avarice · Accepted Answer · 2020-11-09 15:16:46Z

0

You can add two dataframes and .set_index:

(df1+df2).set_index('date')

answered Nov 9, 2020 at 15:16

avarice

15.6k3 gold badges19 silver badges38 bronze badges

Collectives™ on Stack Overflow

Add data from multiple dataframes by column index

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related