CODEWITHSUNDEEP
pandaspython-3.5
Zanam
Zanam

Reputation: 4807

Pandas subtract column values only when non nan

I have a dataframe df as follows with about 200 columns:

Date        Run_1   Run_295 Prc
2/1/2020                    3
2/2/2020    2               6
2/3/2020            5       2

I want to subtract column Prc from columns Run_1 Run_295 Run_300 only when they are non-Nan or non empty, to get the following:

Date        Run_1   Run_295 
2/1/2020                            
2/2/2020    -4                      
2/3/2020            3

I am not sure how to proceed with the above.

Code to reproduce the dataframe:

import pandas as pd
from io import StringIO
s = """Date,Run_1,Run_295,Prc
2/1/2020,,,3
2/2/2020,2,,6
2/3/2020,,5,2"""
df = pd.read_csv(StringIO(s))
print(df)

Upvotes: 0

Views: 2178

Answers (2)

Umar.H
Umar.H

Reputation: 23099

Three steps, melt to unpivot your dataframe

Then loc to handle assignment

& GroupBy to reomake your original df.

sure there is a better way to this, but this avoids loops and apply 

cols = df.columns

s = pd.melt(df,id_vars=['Date','Prc'],value_name='Run Rate')

s.loc[s['Run Rate'].isnull()==False,'Run Rate'] = s['Run Rate'] - s['Prc']

df_new = s.groupby([s["Date"], s["Prc"], s["variable"]])["Run Rate"].first().unstack(-1)

print(df_new[cols])

variable      Date  Run_1  Run_295  Prc
0         2/1/2020    NaN      NaN    3
1         2/2/2020   -4.0      NaN    6
2         2/3/2020    NaN      3.0    2

Upvotes: 1

sash_wash
sash_wash

Reputation: 146

You can simply subtract it. It exactly does what you want:

df.Run_1-df.Prc

Here is the complete code to your output:

df.Run_1= df.Run_1-df.Prc
df.Run_295= df.Run_295-df.Prc
df.drop('Prc', axis=1, inplace=True)

df

    Date        Run_1   Run_295
0   2/1/2020    NaN     NaN
1   2/2/2020    -4.0    NaN
2   2/3/2020    NaN     3.0

Upvotes: 1

Related Questions