Adding numbers from multiple columns and different rows in pandas

Question

I have the following data.

And I am hoping to add values C and D from 2000, with A from 2001, and create a new column in the data frame, which should look like something like this

where the last column in 2003 is blank because it is incomplete (no data in A, 2004)

I've tried:

sum_cda = df.columns[2:4].... 

df['sum'] = df[sum_cda].sum(axis=1)

I am a newbie in both python and pandas, and I've tried searching the internet for answers to no avail.

Thank you!

FOLLOW UP QUESTION:

what if I want to get the minimum or count or max values between CDA? df['sum'] = df[['C', 'D']].min(axis=1)..

jezrael · Accepted Answer · 2021-09-08 11:17:30Z

1

Use sum for columns C, D and then add shifted column A by Series.shift:

df['sum'] = df[['C', 'D']].sum(axis=1).add(df['A'].shift(-1))

If need also add missing years like 2002 use DataFrame.reindex:

df = df.reindex(range(df.index.min(), df.index.max() + 1))
df['sum'] = df[['C', 'D']].sum(axis=1).add(df['A'].shift(-1))

EDIT:

I want to get the minimum or count or max of CDA?

Then first assign new column:

df['sum'] = df.assign(shifted = df['A'].shift(-1))[['shifted', 'C', 'D']].sum(axis=1)
df['max'] = df.assign(shifted = df['A'].shift(-1))[['shifted', 'C', 'D']].max(axis=1)
df['min'] = df.assign(shifted = df['A'].shift(-1))[['shifted', 'C', 'D']].min(axis=1)

But last value is also count, if need NaN set it manually:

df.loc[df.index[-1], 'sum'] = np.nan
df.loc[df.index[-1], 'max'] = np.nan
df.loc[df.index[-1], 'min'] = np.nan

edited Sep 8, 2021 at 11:17

answered Sep 8, 2021 at 10:34

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

Naru Over a year ago

neat! Thank you. but what if I want to get the minimum or count or max values between CDA? df['sum'] = df[['C', 'D']].min(axis=1)...

Collectives™ on Stack Overflow

Adding numbers from multiple columns and different rows in pandas

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related