Unformatted data entries

Question

I have been working recently on an independent project using a database for Cybersecurity Attack classification. I imported the database using Pandas (Python) and before starting the processing step, I have noticed that some of the entries contain the following symbols: "-", "0x000b", "0xc0a8", and many others that it is difficult to track them and see how many of these unformatted data are present, specially when the database is so big. Is there a way to take the whole dataframe, spot all the possible unformatted and error data and substitute them by NaN, to treat them later as missing values?

Thanks in advance!

Answer 1

Those symbols you are talking about are hex values. They could actually be useful to keep in the dataset, as the tcpdump tool outputs them if the right options are set.

Despite that, you can replace occurrences of them using pandas and a simple regular expression. A regular expression that matches hex values is: 0[xX][0-9a-fA-F]+ - see a live demo of that here.

I downloaded one of the CSV files and did it like this:

import pandas as pd

filename = "./UNSW-NB15_1.csv"
df = pd.read_csv(filename, nrows=5000)     # read first 5000 lines
df.to_html("with_hex.html")                # open in a browser to inspect (e.g. line 211)

Now use the DataFrame replace and set the regex flag, and insert whatever you want to replace the matches with (here "NaN").

clean = df.replace("0[xX][0-9a-fA-F]+", value="NaN", regex=True)

I used this to have a look at the cleaned versions of the data quickly in a browser (notice line 211):

clean.to_html("clean.html")

---

You can use the same trick to replace almost anything, so your fields with '-' can be replaced with:

# "inplace" means it overwrites the values, no new dataframe is returned
clean.replace("-", "NaN", regex=False, inplace=True)

Answer 2

Those are hex numbers, aka base 16 digits. Hex numbers in Python are represented as strings that start with "0x". Either keep them as hex or convert them to base 10 integers:

int("0x000b", 16) #=> 11
int("0xc0a8", 16) #=> 49320