Skip to content
TheChatArchiveThe Chat Archive
gpt-4.1-mini-2025-04-14· OpenAIAi2 WildChat-4.8M (AI2)

Dataframe column convert to int and fill NaN as -1

  1. User

    Dataframe column convert to int and fill NaN as -1

  2. Assistant

    You can convert a DataFrame column to integers and fill NaN values with -1 using pandas like this:

    import pandas as pd
    import numpy as np
    
    # Example DataFrame
    df = pd.DataFrame({
        'col': [1.0, 2.0, np.nan, 4.0]
    })
    
    # Fill NaN with -1, then convert to int
    df['col'] = df['col'].fillna(-1).astype(int)
    
    print(df)
    

    This will output:

       col
    0    1
    1    2
    2   -1
    3    4
    

    Explanation:

    • .fillna(-1) replaces all NaN values with -1.
    • .astype(int) converts the column to integer type.

    If you have mixed types or non-numeric values, you might want to convert first to numeric, handling errors:

    df['col'] = pd.to_numeric(df['col'], errors='coerce').fillna(-1).astype(int)
    
  3. User

    data column has value float as str . convert to int and fill NaN as -1

  4. Assistant

    If your DataFrame column contains floats as strings (e.g., "1.0", "2.5", "NaN") and you want to convert it to integers, filling any missing or non-convertible values with -1, you can do the following:

    import pandas as pd
    import numpy as np
    
    # Example DataFrame with floats as strings and some problematic values
    df = pd.DataFrame({
        'col': ['1.0', '2.5', 'NaN', '', '4.0', None]
    })
    
    # Convert to numeric, coerce errors to NaN, fill NaN with -1, then convert to int
    df['col'] = pd.to_numeric(df['col'], errors='coerce').fillna(-1).astype(int)
    
    print(df)
    

    Output:

       col
    0    1
    1    2
    2   -1
    3   -1
    4    4
    5   -1
    

    Explanation:

    1. pd.to_numeric(df['col'], errors='coerce') attempts to convert the strings to numeric values (float), converting unparseable entries (like 'NaN', empty string '', or None) to NaN.
    2. .fillna(-1) replaces those NaN with -1.
    3. .astype(int) converts the float values to integers (truncating decimals).

    If you want to round the floats before converting instead of truncating, replace .astype(int) with .round().astype(int):

    df['col'] = pd.to_numeric(df['col'], errors='coerce').fillna(-1).round().astype(int)
    
  5. User

    here is data frame pid , cert_num, 1,'G123456' 1,'M2912' 2,'GP102' 2,'50321' 3,NaN 3,'G09876' for column 'cert_num' if value is Not None then 0 else replace with 1 .

  6. Assistant

    You want to create a new column (or update cert_num) such that:

    • If cert_num is not None / not NaN → set value to 0
    • If cert_num is None / NaN → set value to 1

    Assuming your DataFrame looks like this:

    import pandas as pd
    import numpy as np
    
    df = pd.DataFrame({
        'pid': [1, 1, 2, 2, 3, 3],
        'cert_num': ['G123456', 'M2912', 'GP102', '50321', np.nan, 'G09876']
    })
    

    You can create/update a column based on whether cert_num is missing or not as follows:

    df['cert_num_flag'] = np.where(df['cert_num'].notna(), 0, 1)
    print(df)
    

    Output:

       pid cert_num  cert_num_flag
    0    1  G123456              0
    1    1    M2912              0
    2    2    GP102              0
    3    2    50321              0
    4    3      NaN              1
    5    3   G09876              0
    

    If you want to replace the existing cert_num column with 0 or 1 as above, you can do:

    df['cert_num'] = np.where(df['cert_num'].notna(), 0, 1)
    print(df)
    

    Result:

       pid  cert_num
    0    1         0
    1    1         0
    2    2         0
    3    2         0
    4    3         1
    5    3         0
    

    Explanation:

    • df['cert_num'].notna() returns True when cert_num is not missing.
    • np.where(condition, 0, 1) assigns 0 if condition is True, else 1.

    Let me know if you want to keep original values and just add a new flag column!