0

I have a csv file that I'm converting in a panda in Python 3.7. I want then to check if certain cells have NaN(i.e. there are empty in my case) and only in this case, I want to replace the content of the cell with another value.

I'm selecting the cell with values inside other cells in other columns (columns family_name and first_name) on the same row. Here is a MWE:

import csv
import pandas as pd
import numpy as np
df = pd.DataFrame({"family_name":["smith", "duboule", "dupont"], "first_name":["john","jean-paul", "luc"], "weight":[70, 85, pd.np.nan]})
value_to_replace = 90
if df["weight"][(df["family_name"] == "dupont") & (df["first_name"] == "luc")] == pd.np.nan:
    df["weight"][(df["family_name"] == "dupont") & (df["first_name"] == "luc")] = value_to_replace

I get the following error:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/mymac/anaconda3/lib/python3.7/site-packages/pandas/core/generic.py", line 1576, in __nonzero__
    .format(self.__class__.__name__))
ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

I also tried the to add .bool() == True in the following form but I got the same error message: 

if pd.isna(df["weight"][(df["family_name"] == family_name) & (df["first_name"] == first_name)]).bool() == True:
    df["weight"][(df["family_name"] == "dupont") & (df["first_name"] == "luc")] = value_to_replace
ecjb
  • 5,169
  • 12
  • 43
  • 79
  • this is a `Series`: `df["weight"][(df["family_name"] == "dupont") & (df["first_name"] == "luc")]`. The error message is pretty straightforward, and suggests several ways to avoid the error. – David Zemens Mar 14 '19 at 18:48
  • Possible duplicate of [Truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()](https://stackoverflow.com/questions/36921951/truth-value-of-a-series-is-ambiguous-use-a-empty-a-bool-a-item-a-any-o) – David Zemens Mar 14 '19 at 18:49
  • Thank you for your comment @DavidZemens. I saw those posts already but couldn't find an answer to the problem. I'll be very thankful If you can give a straightforward answer to the `MWE` – ecjb Mar 14 '19 at 18:52
  • Unclear what you're asking. Have you tried any of the recommendations from the error message? Also, unclear why you state "I want then to check if certain cells have NaN", but that's not actually what you seem to be doing in example code. – David Zemens Mar 14 '19 at 18:55
  • 1
    I mean, you could miss all this stuff and just to `df['weight'].fillna(value_to_replace, inplace=True)` if you simply want to replace the `NaN` weight values. – David Zemens Mar 14 '19 at 18:57
  • @DavidZemens. In the real database, there are 50 id with several missing value. I want to replace it only for corresponding id and not the other one – ecjb Mar 14 '19 at 19:00

2 Answers2

2

Use np.where

Works like following: np.where(condition, true value, false value)

df['weight'] = np.where((df.family_name == 'dupont') & (df.first_name == 'luc'), value_to_replace, df.weight)

print(df)
  family_name first_name  weight
0       smith       john    70.0
1     duboule  jean-paul    85.0
2      dupont        luc    90.0

Edit after OP's comment
Only if weight is NaN, you can use .isnull:

df['weight'] = np.where((df.family_name == 'dupont') & (df.first_name == 'luc') & (df.weight.isnull()), value_to_replace, df.weight)
Erfan
  • 40,971
  • 8
  • 66
  • 78
  • Many thanks for your answer @Erfan. The code was not clear and I edited it. First, I want to check if the content of the Cell is `NaN`. Only if it is the case I want to replace it – ecjb Mar 14 '19 at 19:03
1

remove all your if statement, and use this

df.loc[ (df["family_name"] == "dupont") & 
        (df["first_name"] == "luc") & 
        (df["weight"].isnull()), 'weight'] = value_to_replace

I suggest you to read the pandas API to learn how loc select/edit data

Terry
  • 2,761
  • 2
  • 14
  • 28
  • Thank you for your answer @Terry. The code was not clear and I edited it. First, I want to check if the content of the Cell is NaN. Only if it is the case I want to replace it with the value – ecjb Mar 14 '19 at 19:05
  • Many thanks for your anwer @Terry. I upvoted your answer – ecjb Mar 14 '19 at 19:17