<jupyter_start><jupyter_text>Read full dataset into Pandas and save as pickle<jupyter_code>import pandas as pd
import numpy as np
%load_ext watermark
%watermark -iv
# all of the names from the features file
names = [ "srcip", "sport", "dstip", "dsport", "proto", "state",
    "dur", "sbytes", "dbytes", "sttl", "dttl", "sloss", "dloss",
    "service", "sload", "dload", "spkts", "dpkts", "swin", "dwin",
    "stcpb", "dtcpb", "smeansz", "dmeansz", "trans_depth", "res_bdy_len",
    "sjit", "djit", "stime", "ltime", "sintpkt", "dintpkt", "tcprtt",
    "synack", "ackdat", "is_sm_ips_ports", "ct_state_ttl",
    "ct_flw_http_mthd", "is_ftp_login", "ct_ftp_cmd", "ct_srv_src",
    "ct_srv_dst", "ct_dst_ltm", "ct_src_ltm", "ct_src_dport_ltm",
    "ct_dst_sport_ltm", "ct_dst_src_ltm", "attack_cat", "label"
]
len(names)<jupyter_output><empty_output><jupyter_text>Read the data in as raw stringsMainly because pandas.read_csv kept barfing<jupyter_code># read all columns as strings because pandas kept puking 
# chunking to speed things up 
if False:
    full_df = pd.DataFrame(columns=names)

    CHUNKSIZE = 10000
    chunks = [None] * CHUNKSIZE

    with open('../data/full.csv', 'r') as fp:
        reader = csv.DictReader(fp)
        index = 0
        chunk_idx = 0 
        for row in reader:
        
            rowdata = np.array(list(row.values())).reshape(1,-1)
            idf = pd.DataFrame(data=rowdata, index=[index], columns=row.keys())
            chunks[chunk_idx] = idf
            index += 1
        
            chunk_idx += 1
            if chunk_idx == CHUNKSIZE:
                full_df = full_df.append(pd.concat(chunks, axis=0))
                chunk_idx = 0
            
    if chunk_idx != 0:
        chunks = chunks[:chunk_idx]
        full_df = full_df.append(pd.concat(chunks, axis=0))

    pd.to_pickle(full_df, '../data/full_string_df')
else:
    full_df = pd.read_pickle('../data/full_string_df')
full_df.shape<jupyter_output><empty_output><jupyter_text>Convert the integer columns<jupyter_code>integers = [ "sport", "dsport", 
     "sbytes", "dbytes", "sttl", "dttl", "sloss", "dloss",
     "spkts", "dpkts", "swin", "dwin",
    "stcpb", "dtcpb", "smeansz", "dmeansz", "trans_depth", "res_bdy_len",
     "stime", "ltime",  "is_sm_ips_ports", "ct_state_ttl",
    "ct_flw_http_mthd", "is_ftp_login", "ct_ftp_cmd", "ct_srv_src",
    "ct_srv_dst", "ct_dst_ltm", "ct_src_ltm", "ct_src_dport_ltm",
    "ct_dst_sport_ltm", "ct_dst_src_ltm", "label"
]

strings = [ "srcip",  "dstip", "proto", "state", "service", "attack_cat"
]

floats = [  "dur",  "sload", "dload", 
    "sjit", "djit", "sintpkt", "dintpkt", "tcprtt",
    "synack", "ackdat"
]

nulls = [ '-', ' ', '' ]
full_df.head()
# all the non-sloppy columns convert easily
clean_ints = []

for column in integers:
    null_count = 0 
    for s in full_df[column]:
        if s in nulls:
            null_count += 1
            break
    if null_count:
        null_count = 0
    else:
        clean_ints.append(column)
        
for column in clean_ints:
    full_df[column] = full_df[column].astype(np.int)
    
# for the last few, need a special converter 

def tolerant_convert_int(s):
    if s in nulls:
        return 0
    return int(s, 0)

dirty_ints = [ column for column in integers if column not in clean_ints ]
dirty_ints
for column in dirty_ints:
    full_df[column] = full_df[column].map(tolerant_convert_int)
full_df[dirty_ints].info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 2540044 entries, 0 to 2540043
Data columns (total 5 columns):
sport               int64
dsport              int64
ct_flw_http_mthd    int64
is_ftp_login        int64
ct_ftp_cmd          int64
dtypes: int64(5)
memory usage: 116.3 MB<jupyter_text>Pickle file checkpoint<jupyter_code>pd.to_pickle(full_df, '../data/converted_ints')
full_df = pd.read_pickle('../data/converted_ints')<jupyter_output><empty_output><jupyter_text>Convert the string columns<jupyter_code>for column in strings:
    full_df[column] = full_df[column].str.replace(' ','')
strings
categories = strings[2:]
categories
full_df['proto'] = full_df['proto'].astype('category')
full_df['state'] = full_df['state'].astype('category')
full_df['service'] = full_df['service'].astype('category')
full_df['attack_cat'].value_counts()
# From value counts above: Normal category is an empty string and Backdoor attack spelled two ways
full_df.loc[full_df.eval('label == 0'), 'attack_cat'] = 'normal'
full_df.loc[full_df.eval("attack_cat == 'Backdoor'"), 'attack_cat'] = 'Backdoors'
full_df['attack_cat'] = full_df['attack_cat'].str.lower()
full_df['attack_cat'].value_counts()
full_df['attack_cat'] = full_df['attack_cat'].astype('category')<jupyter_output><empty_output><jupyter_text>Pickle file checkpoint<jupyter_code>pd.to_pickle(full_df, '../data/converted_strings')
full_df = pd.read_pickle('../data/converted_strings')<jupyter_output><empty_output><jupyter_text>Convert float columns<jupyter_code># all that should be left is floats!
floats
for column in floats:
    full_df[column] = pd.to_numeric(full_df[column])
pd.to_pickle(full_df, '../data/full_pandas_final')
full_df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 2540044 entries, 0 to 2540043
Data columns (total 49 columns):
srcip               object
sport               int64
dstip               object
dsport              int64
proto               category
state               category
dur                 float64
sbytes              int64
dbytes              int64
sttl                int64
dttl                int64
sloss               int64
dloss               int64
service             category
sload               float64
dload               float64
spkts               int64
dpkts               int64
swin                int64
dwin                int64
stcpb               int64
dtcpb               int64
smeansz             int64
dmeansz             int64
trans_depth         int64
res_bdy_len         int64
sjit                float64
djit                float64
stime               int64
ltime               int64
sintpkt             float64
dintpkt             float64
tcprtt              float64
sy[...]
{'fraction_non_alphanumeric': 0.1278805582603051, 'fraction_numerical': 0.021421616358325218, 'mean_word_length': 3.0176010430247717, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '294800', 'license': 'MIT', 'n_tokens_mistral': 2339, 'n_tokens_neox': 2196, 'n_words': 475, 'path': 'src/PandasFull.ipynb', 'repo_name': 'j-carson/ml-nids', 'size': '6162'}