python-3.xstringdataframedata-preprocessing

Conversion of hexadecimal value represented as string into int in column in dataframe


I am working on a dataset where two of its attributes sport and dport have hexadecimal values such as 'Ox0303' and 'Ox5000' along with integer values. The datatype of these attributes is object type. When I am replacing these hexadecimal values with their respective decimal value in the following way:

df1['sport']= df1['sport'].replace(to_replace='Ox0303',value = 771, inplace=True)  
df1['sport']= df1['sport'].replace(to_replace='-1',value = 0, inplace=True)

it is setting the value to None in the dataframe.

sport and dport values after replacing values:

sport and dport values after replacing values

I want to set dtype of these columns as int and to replace hexadecimal values with their respective decimal values. Pls help


Solution

  • Consider this dataframe:

              saddr   sport        daddr dport
    0   192.168.1.1  0x0303  192.168.1.1    -1
    1  192.168.1.10  0x0301  192.168.1.2  0xFF
    2  192.168.1.20    0x03  192.168.1.3  0x01
    

    The values in sport and dport columns are strings. To convert them to integers you can do:

    from ast import literal_eval
    
    df["dport"] = df["dport"].apply(literal_eval)
    df["sport"] = df["sport"].apply(literal_eval)
    
    print(df)
    

    Prints:

              saddr  sport        daddr  dport
    0   192.168.1.1    771  192.168.1.1     -1
    1  192.168.1.10    769  192.168.1.2    255
    2  192.168.1.20      3  192.168.1.3      1