<jupyter_start><jupyter_text>0. Load the data<jupyter_code># imports:
import pandas as pd
import matplotlib.pyplot as plt
import os

PREFIX_PATH = '/home/sramkova/diploma_thesis_data/cicids2017/attacks'
PREFIX_PATH = PREFIX_PATH + '/' + '/'.join(os.getcwd().split('/')[-2:]) + '/'
INPUT_CSV = PREFIX_PATH + 'query_output_processing.csv'
print(INPUT_CSV)


raw_data = pd.read_csv(INPUT_CSV)

# check that all columns are correctly loaded
raw_data.head()
pd.set_option('display.max_rows', None)
raw_data.dtypes
pd.set_option('display.max_rows', 10)
# select only original connection columns (no neighbourhood):

raw_data = raw_data[['originated_ip', 
                     'responded_ip', 
                     'connection.uid', 
                     'connection.ts', 
                     'connection.resp_pkts', 
                     'connection.resp_ip_bytes', 
                     'connection.orig_p', 
                     'connection.duration',
                     'connection.resp_bytes',
                     'connection.orig_pkts',
                     'connection.proto',
                     'connection.service', 
                     'connection.orig_bytes',
                     'connection.conn_state',
                     'connection.resp_p',
                     'connection.orig_ip_bytes',
                     'dns_count',
                     'ssh_count', 
                     'http_count',
                     'ssl_count',
                     'files_count',
                    ]]
raw_data.head()<jupyter_output><empty_output><jupyter_text>1. Get to know the data 1.0 Basic statistics<jupyter_code># types and number of non-null values in columns:
raw_data.info()
# simple statistics of numerical attributes: 
raw_data.describe()
# source: https://www.geeksforgeeks.org/python-pandas-dataframe-describe-method/

# remove null values to avoid errors  
nonnull_data = raw_data.dropna()  
  
# percentile list 
perc =[.20, .40, .60, .80] 
  
# list of dtypes to include 
include =['object', 'float', 'int'] 
  
# calling describe method 
nonnull_data.describe(percentiles = perc, include = include)<jupyter_output><empty_output><jupyter_text>1.1 Show distinct counts<jupyter_code>distinct_counter = raw_data.apply(lambda x: len(x.unique()))
distinct_counter<jupyter_output><empty_output><jupyter_text>1.2 Visualize frequencies Get Frequency Counts of Categorical Columns: - connection.proto- connection.service- connection.conn_state(https://cmdlinetips.com/2018/02/how-to-get-frequency-counts-of-a-column-in-pandas-dataframe/)<jupyter_code>pd.reset_option('display.max_rows')
raw_data['connection.proto'].value_counts()
raw_data['connection.service'].value_counts(dropna=False) # display null values as well
raw_data['connection.conn_state'].value_counts()<jupyter_output><empty_output><jupyter_text>Get Frequency Counts of Non-categorical Columns: - originated_ip- responded_ip- connection.duration- connection.orig_pkts- connection.orig_ip_bytes- connection.resp_p- connection.orig_bytes- connection.resp_bytes- connection.resp_ip_bytes- connection.orig_p- connection.resp_pkts<jupyter_code>raw_data['originated_ip'].value_counts()
# or print relative frequencies of unique values:
# (sometimes percentage is a better criterion then the count)

raw_data['originated_ip'].value_counts(normalize=True)
raw_data['responded_ip'].value_counts()
raw_data['connection.duration'].value_counts()
pd.set_option('display.max_rows', None)
raw_data['connection.duration'].value_counts().nlargest(100)
# raw_data['connection.orig_pkts'].value_counts()
raw_data['connection.orig_pkts'].value_counts().nlargest(40)
# raw_data['connection.orig_ip_bytes'].value_counts()
# raw_data['connection.orig_ip_bytes'].value_counts().nlargest(40)
# raw_data['connection.resp_p'].value_counts()
pd.set_option('display.max_rows', 30)
raw_data['connection.resp_p'].value_counts()
pd.set_option('display.max_rows', None)
raw_data['connection.resp_p'].value_counts().nlargest(40)
# raw_data['connection.orig_bytes'].value_counts()
raw_data['connection.orig_bytes'].value_counts().nlargest(40)
# raw_data['connection.resp_bytes'].value_counts()
raw_data['connection.resp_bytes'].value_counts().nlargest(40)
# raw_data['connection.resp_ip_bytes'].value_counts()
raw_data['connection.resp_ip_bytes'].value_counts().nlargest(40)
pd.set_option('display.max_rows', 30)
raw_data['connection.orig_p'].value_counts()
# raw_data['connection.orig_p'].value_counts().nlargest(40)
# raw_data['connection.resp_pkts'].value_counts()
pd.set_option('display.max_rows', 30)
raw_data['connection.resp_pkts'].value_counts().nlargest(40)<jupyter_output><empty_output><jupyter_text>1.3 Application data counts<jupyter_code>raw_data['dns_count'].value_counts()
raw_data['ssh_count'].value_counts()
raw_data['http_count'].value_counts()
raw_data['ssl_count'].value_counts()
raw_data['files_count'].value_counts()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13238289205702647, 'fraction_numerical': 0.011405295315682282, 'mean_word_length': 4.367213114754098, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '302278', 'license': 'MIT', 'n_tokens_mistral': 1663, 'n_tokens_neox': 1577, 'n_words': 221, 'path': 'impl/jupyter_notebooks/cicids2017_attacks/2_thursday/web_attack_sql_injection/data_exploration.ipynb', 'repo_name': 'roa7n/graph_network_traffic_ml', 'size': '4910'}