CODEWITHSUNDEEP
pythonpandasnullnan
hoa tran
hoa tran

Reputation: 1679

If value = NaN then

I'm new to Python and I'm trying to use Pandas (I can use VBA but it takes quite a long time) to merge 2 sheets of Excel into 1 (Like Vlookup function in Excel). And I have a problem as below:

This is my code:

import pandas as pd

df1 = pd.DataFrame({'v_contract_number': ['VN120001438','VN120001439'],'maturity date': ['31/12/2021','31/12/2021']})
df2 = pd.DataFrame({'v_contract_number': ['VN120001438','VN120001439'], 'd_revised_maturity_date': ['31/12/2021',' ']})
print(df1)

# df1
  v_contract_number maturity date
0       VN120001438    31/12/2021
1       VN120001439    31/12/2021

print(df2)

# df2
  v_contract_number d_revised_maturity_date
0       VN120001438              31/12/2021
1       VN120001439  

results = pd.merge(df1, df2, on=['v_contract_number'],how='left')
print(results)

# results
  v_contract_number maturity date d_revised_maturity_date
0       VN120001438    31/12/2021              31/12/2021
1       VN120001439    31/12/2021

What I want is if 'd_revised_maturity_date' is null then null = 'maturity date'

My expected result is:

  v_contract_number maturity date d_revised_maturity_date
0       VN120001438    31/12/2021              31/12/2021
1       VN120001439    31/12/2021              31/12/2021

I can do this task in excel with iferror but I don't know how to do in python.

Thanks and best regards

Upvotes: 1

Views: 885

Answers (5)

alec22
alec22

Reputation: 762

df['d_revised_maturity_date'].fillna(df['maturity date'], inplace=True)

Should work for you

Upvotes: 1

O'Lagan
O'Lagan

Reputation: 21

results['d_revised_maturity_date'] = results['maturity date'].apply(lambda x: x if x == None else x)

Upvotes: 0

pb.
pb.

Reputation: 135

A combination of np.where() can be used to your advantage.

np.where(pd.isna(df["d_revised_maturity_date"]), df["maturity date"], df["d_revised_maturity_date"])

Upvotes: 0

Mustafa Aydın
Mustafa Aydın

Reputation: 18306

After you merged, you can use mask:

d_date = results.d_revised_maturity_date
results.d_revised_maturity_date = d_date.mask(d_date.eq(" "), results["maturity date"])

This will fill the empty slots of d_revised_maturity_date (found with equal to " ") with the corresponding values of maturity date column,

to get

>>> results

  v_contract_number maturity date d_revised_maturity_date
0       VN120001438    31/12/2021              31/12/2021
1       VN120001439    31/12/2021              31/12/2021

(If you're looking to replace any kind of whitespace in d_revised_maturity_date column, you can change d_date.eq(" ") above to

d_date.str.fullmatch(r"\s*")

which includes fully empty strings, one or more spaces.)

Upvotes: 2

Albin Paul
Albin Paul

Reputation: 3419

You could use fillna to fill na/nan values with another column.

df['d_revised_maturity_date'].fillna(df['maturity date'], inplace=True)

Upvotes: 0

Related Questions