how to add a DataFrame to some columns of another DataFrame

Question

I want to add a DataFrame a (containing a loadprofile) to some of the columns of another DataFrame b (also containing one load profile per column). So some columns (load profiles) of b should be overlaid withe the load profile of a.

So lets say my DataFrames look like:

a:

b:

  P1[kW]  P2[kW] ... Pn[kW]
0   2       2          2
1   3       3          3
2   3       3          3
3   4       4          4
4   2       2          2
5   2       2          2

Now I want to overlay some colums of b:

b.iloc[:, [1]] += a.iloc[:, 0]

I would expect this:

b:

  P1[kW]  P2[kW] ... Pn[kW]
0   2       2          2
1   3       3          3
2   3       3          3
3   4       12         4
4   2       10         2
5   2       2          2

but what I actually get:

b:

  P1[kW]  P2[kW] ... Pn[kW]
0   2       nan        2
1   3       nan        3
2   3       nan        3
3   4       nan        4
4   2       nan        2
5   2       nan        2

That's not exactly what my code and data look like, but the principle is the same as in this abstract example.

Any guesses, what could be the problem?

Many thanks for any help in advance!

EDIT: I actually have to overlay more than one column.
Another example:

load = [0,0,0,0,0,0,0]
data = pd.DataFrame(load)
for i in range(1, 10):
    data[i] = data[0]
data

overlay = pd.DataFrame([0,0,0,0,6,6,0])
overlay

data.iloc[:, [1,2,4,5,7,8]] += overlay.iloc[:, 0]
data

WHAT??! The result is completely crazy. Columns 1 and 2 aren't changed at all. Columns 4 and 5 are changed, but in every row. Columns 7 and 8 are nans. What am I missing?

That is what I would expect the result to look like:

Utsav · Accepted Answer · 2021-06-05 08:02:26Z

1

Please do not pass the column index '1' of dataframe 'b' as a list but as an element.

Code

b.iloc[:, 1] += a.iloc[:, 0]
b

Output

    P1[kW]  P2[kW]  Pn[kW]
0   2       2       2
1   3       3       3
2   3       3       3
3   4       12      4
4   2       10      2
5   2       2       2

Edit

Seems like this what we are looking for i.e to sum certain columns of data df with overlay df

Two Options

Option 1

cols=[1,2,4,5,7,8]
data[cols] = data[cols] + overlay.values
data

Option 2, if we want to use iloc

cols=[1,2,4,5,7,8]
data[cols] = data.iloc[:,cols] + overlay.iloc[:].values
data

Output

    0   1   2   3   4   5   6   7   8   9
0   0   0   0   0   0   0   0   0   0   0
1   0   0   0   0   0   0   0   0   0   0
2   0   0   0   0   0   0   0   0   0   0
3   0   0   0   0   0   0   0   0   0   0
4   0   6   6   0   6   6   0   6   6   0
5   0   6   6   0   6   6   0   6   6   0
6   0   0   0   0   0   0   0   0   0   0

edited Jun 5, 2021 at 8:02

answered Jun 5, 2021 at 5:25

Utsav

6,0932 gold badges36 silver badges58 bronze badges

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

4 Comments

Andre Over a year ago

Thanks :) It works if I only overlay 1 column. But when I want to choose more columns to overlay, I have to pass them as a list, right? And that is where things start to get real crazy: some columns are overlaid correctly, some are nans, some are overlaid, but with different values?!? I feel like going crazy

Utsav Over a year ago

nw, I have updated the solution as per requirement.

Andre Over a year ago

THANK YOU!! :) You have literally saved my day! I was short about smacking my head against the wall :D I would give you multiple upvotes, if that was possible

Utsav Over a year ago

np, I was able to save you some time that's the biggest reward for me. All the best!

Collectives™ on Stack Overflow

how to add a DataFrame to some columns of another DataFrame

1 Answer 1

Edit

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related