Update dataframe value conditionaly with its own value

Admin 1
Viewed 67

I have DF with float street numbers, sometime it is "NaN" or "x-y" (ex: 30-32) but often x.y (ex: 32.0 instead of 30) I need to change this to int (if there is no "-" in the number of course). Ive tried

chunk.loc["-" not in chunk["Street Number"] & chunk["Street Number"].notna(), 'Street Number'] = chunk["Street Number"].astype(int)

I know there is an issue after my "=" sign. How to update dataframe value conditionaly with its own value please ? Ive also tried with

chunk[["Street Number"]].astype(int)

There is no error

Sample of DF :

0 | NaN

1 | 1.0

2 | 6.0

3 | 170.0

4 | 61.0

5 | 51-52

I tried to force dtype "Street Number": np.uint16 but I got ValueError: Integer column has NA values in column 12

pandas python
2 Answers

Use changed Cedric H. solution, but in output are mixed strings, integers, floats (NaN)s:

def convert_street_number_values(x):
    try:
        x = float(x)
        if x.is_integer():
            return int(x)
        else:
            return x
    except Exception:
        return x

chunk = pd.DataFrame({'Street Number':[np.nan, '1.0', '6.0', '170.0', '61.0', '51-52']})

chunk['Street Number'] = chunk['Street Number'].apply(convert_street_number_values)
print (chunk)
  Street Number
0           NaN
1             1
2             6
3           170
4            61
5         51-52

EDIT:

Problem is if multiple different groups - then pandas convert column to integers, but if there is at least one NaNs value it convert back to floats.

Solution working nice for all data is converting to strings:

def convert_street_number_values(x):
    try:
        x = float(x)
        if x.is_integer():
            return str(int(x))
        else:
            return str(x)
    except Exception:
        return str(x)

Because converting to integers with missing values is possible using Nullable Integer Data Type:

chunk['Street Number'] = chunk['Street Number'].astype('Int64')

but it failed if at least string value.

So if convert all non numeric to NaNs it working:

chunk['Street Number'] = pd.to_numeric(chunk['Street Number'], errors='coerce').astype('Int64')
user_29010020

I would do something like this:

def convert_street_number_values(v):
    # Add more complex 'rules' as you need
    try:
        return int(v)
    except Exception:
        return v

df['Steet Number'].apply(convert_street_number_values)
user_4208920
Related