Contents

Banner


Workshop 1.3: Basics of Data Analysis with Pandas


Importing the Pandas Library

This entire section of the workshop is based on the Pandas Python Library. Therefore, it makes sense to start by importing the library.

If you have not installed pandas yet, you can install it via pip by running the following code in a notebook cell:

%pip install pandas

import pandas as pd

Representing data in an Organized way: Dataframe

Pandas Structures

Series

A Pandas Series is a one-dimensional array-like object that can hold any data type, with a single Series holding multiple data types if needed. The axis labels area refered to as index.

They can be created from a range of different Python data structures, including a list, ndarry, dictionary or scalar value.

  • If creating from an list like below we can either specify the index or one can be automatically created.

data = ["Item 1", "Item 2", "Item 3"]
pd.Series(data, index=[1,2,3])
#pd.Series(data, index=["A","B","C"])
1    Item 1
2    Item 2
3    Item 3
dtype: object
  • When creating from a dictionary an index does not need to be supplied and will be infered from the Dictionary keys:

data = {"A": "Item 1", "B": "Item 2", "C": "Item 3"}
pd.Series(data)
A    Item 1
B    Item 2
C    Item 3
dtype: object
  • You can also attach names to a Series by using the parameter name. This can help with later understanding.

data = {"A": "Item 1", "B": "Item 2", "C": "Item 3"}
examples_series = pd.Series(data, name="Dictionary Series")
print(examples_series)
print('Name of my Series: ',examples_series.name)
A    Item 1
B    Item 2
C    Item 3
Name: Dictionary Series, dtype: object
Name of my Series:  Dictionary Series

You can find more details about Pandas Series here:

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.html

DataFrame

A Pandas DataFrame is a two-dimensional tabular data structure with labeled axes (rows and columns). Similar to a table.

A DataFrame can be considered to be make up for multiple Series, with each row being its own Series, and as with Series not each column in an DataFrame is necessarily the same type of data.

DataFrames can be created from a range of input types including Pythos data structures such as lists, tuples, dictionaries, Series, ndarrays, or other DataFrames.

As well as the index that a Series has, DataFrames have a second index called ‘columns’, which contains the names assigned to each column in the DataFrame.

data = {"Name": ["Item 1", "Item 2", "Item 3"], "Value": ["6.0", "3.2", "11.9"], "Count": [111, 720, 82]}
pd.DataFrame(data)
Name Value Count
0 Item 1 6.0 111
1 Item 2 3.2 720
2 Item 3 11.9 82
  • In the example above the columns are infered from the keys of the dictionary and the index is autogenearted. If needed, we can also specify index values by using the index parameter:

import pandas as pd
data = {"Name": ["Item 1", "Item 2", "Item 3"], "Value": ["6.0", "3.2", "11.9"], "Count": [111, 720, 82]}
pd.DataFrame(data, index=["Item 1", "Item 2", "Item 3"])
Name Value Count
Item 1 Item 1 6.0 111
Item 2 Item 2 3.2 720
Item 3 Item 3 11.9 82
  • You can also create a DataFrame from a group of Series:

data = {"A": "Item 1", "B": "1", "C": "12.3"}
data2 = {"A": "Item 4", "B": "6", "C": "17.1"}
pd.DataFrame([data, data2])
A B C
0 Item 1 1 12.3
1 Item 4 6 17.1
  • You can also choose to use a column as the index if you wish:

data = {"A": "Item 1", "B": "1", "C": "12.3"}
data2 = {"A": "Item 4", "B": "6", "C": "17.1"}
df = pd.DataFrame([data, data2])
df.set_index("A")
B C
A
Item 1 1 12.3
Item 4 6 17.1

You can find more details about Pandas DataFrames here:

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html


Importing data as a Pandas DataFrame

In the previous section, we showed how to create a Pandas DataFrame from Python data structures such as Series and Dictionaries.

In addition to this, Pandas contains several READ methods that allow us to convert data stored in different formats such as JSON, EXCEL(CSV, XLSX), SQL, HTML, XML, and PICKLE.

Importing JSON files

We already showed to you how to import a JSON file using the read_json method.

Additionally to the pandas library we imported at the beginning of the session, we will need to import the JSON module from pandas.io in order to be able to use the read_json method.

from pandas.io import json

Now we should be able to read our JSON file (List of Dictionaries). As you can see in the code below, the read_json method returns a Pandas DataFrame.

json_df = json.read_json(path_or_buf='../data/techniques_to_events_mapping.json')
print(type(json_df))
json_df.head(n=1)
<class 'pandas.core.frame.DataFrame'>
technique_id x_mitre_is_subtechnique technique tactic platform data_source data_component name source relationship target event_id event_name event_platform audit_category audit_sub_category log_channel log_provider filter_in
0 T1547.004 True Winlogon Helper DLL [persistence, privilege-escalation] [Windows] windows registry windows registry key modification Process modified Windows registry key value process modified windows registry key value 13 RegistryEvent (Value Set). Windows RegistryEvent None Microsoft-Windows-Sysmon/Operational Microsoft-Windows-Sysmon NaN

Each dictionary within the JSON file we read previously is stored in different lines. What if each dictionary is stored in one line of our JSON file? This is the case of pre-recorded datasets from our Security Datasets OTR Project.

In this case we will need to set the parameter lines to True.

json_df2 = json.read_json(path_or_buf='../data/empire_shell_net_localgroup_administrators_2020-09-21191843.json',lines = True)
print(type(json_df2))
json_df2.head(n=1)
<class 'pandas.core.frame.DataFrame'>
Keywords SeverityValue TargetObject EventTypeOrignal EventID ProviderGuid ExecutionProcessID host Channel UserID ... SourceIsIpv6 DestinationPortName DestinationHostname Service Details ShareName EnabledPrivilegeList DisabledPrivilegeList ShareLocalPath RelativeTargetName
0 -9223372036854775808 2 HKU\S-1-5-21-4228717743-1032521047-1810997296-... INFO 12 {5770385F-C22A-43E0-BF4C-06F5698FFBD9} 3172 wec.internal.cloudapp.net Microsoft-Windows-Sysmon/Operational S-1-5-18 ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

1 rows × 155 columns

If your JSON file contains columns that store dates, you can use the parameter convert_dates to convert strings into values with date format. For example, lets check the type of value for the first record of the column @timestamp.

type(json_df2.iloc[0]['@timestamp'])
str

As you can see in the output of the previous cell, the type of value is str or string. Let’s read the JSON file setting the parameter convert_dates with a list that contains the names of the columns that store dates.

json_df2_dates = json.read_json(path_or_buf='../data/empire_shell_net_localgroup_administrators_2020-09-21191843.json',
                          lines = True,convert_dates=['@timestamp'])
type(json_df2_dates.iloc[0]['@timestamp'])
pandas._libs.tslibs.timestamps.Timestamp

Importing CSV files

Another useful format in InfoSec is CSV (Comma Separated Values). To import a CSV file we will use the read_csv method.

csv_df = pd.read_csv("../data/process_tree.csv")
print(type(csv_df))
csv_df.head(n=1)
<class 'pandas.core.frame.DataFrame'>
Unnamed: 0 TenantId Account EventID TimeGenerated Computer SubjectUserSid SubjectUserName SubjectDomainName SubjectLogonId ... NewProcessName TokenElevationType ProcessId CommandLine ParentProcessName TargetLogonId SourceComputerId TimeCreatedUtc NodeRole Level
0 0 802d39e1-9d70-404d-832c-2de5e2478eda MSTICAlertsWin1\MSTICAdmin 4688 2019-01-15 05:15:15.677 MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 MSTICAdmin MSTICAlertsWin1 0xfaac27 ... C:\Diagnostics\UserTmp\ftp.exe %%1936 0xbc8 .\ftp -s:C:\RECYCLER\xxppyy.exe C:\Windows\System32\cmd.exe 0x0 46fe7078-61bb-4bed-9430-7ac01d91c273 2019-01-15 05:15:15.677 source 0

1 rows × 21 columns

If your CSV file contains columns that store dates, you can use the parameter parse_dates to convert strings into values with date format. For example, lets check the type of value for the first record of the column TimeGenerated.

print(type(csv_df.iloc[0]["TimeGenerated"]))
<class 'str'>

As you can see in the output of the previous cell, the type of value is str or string. Let’s read the CSV file setting the parameter parse_dates with a list that contains the names of the columns that store dates.

csv_df_date = pd.read_csv("../data/process_tree.csv", parse_dates=["TimeGenerated"])
print(type(csv_df_date.iloc[0]["TimeGenerated"]))
<class 'pandas._libs.tslibs.timestamps.Timestamp'>

Notes on CSV Files

Other useful options for CSV include:

pd.read_csv(
  file_path,
  index_col=0,      # if CSV already has an index col
  header=row_num,   # which row headers are found in (def = first row)
  on_bad_lines="warn", # warn but don't fail on line parsing (other options are "error", "skip"
)

Importing PICKLE files

Another useful format in InfoSec is PICKLE. This type of files can be used to serialize Python object structures such as dictionaries, tuples, and lists. To import a PICKLE file we will use the read_pickle method.

pkl_df = pd.read_pickle("../data/host_logons.pkl")
print(type(pkl_df))
pkl_df.head(n=1)
<class 'pandas.core.frame.DataFrame'>
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
0 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:56:34.307 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:56:34.307

Importing Remote Files

Most read_* methods accept a path to the local file system and some of them accept paths to remote files. Let’s check an example with a remote CSV file.

csv_remote = pd.read_csv(filepath_or_buffer='https://raw.githubusercontent.com/OTRF/OSSEM-DM/main/use-cases/mitre_attack/attack_events_mapping.csv')
print(type(csv_remote))
csv_remote.head(n=1)
<class 'pandas.core.frame.DataFrame'>
Data Source Component Source Relationship Target EventID Event Name Event Platform Log Provider Log Channel Audit Category Audit Sub-Category Enable Commands GPO Audit Policy
0 User Account user account authentication user attempted to authenticate from port 4624 An account was successfully logged on. Windows Microsoft-Windows-Security-Auditing Security Logon/Logoff Logon auditpol /set /subcategory:Logon /success:enab... Computer Configuration -> Windows Settings -> ...

You can find more details about Pandas’ read_* methods here:

https://pandas.pydata.org/docs/user_guide/io.html


Interacting with DataFrames

import pandas as pd

# We're going to read another data set in with more variety
logons_full_df = pd.read_pickle("../data/host_logons.pkl")
net_full_df = pd.read_pickle("../data/az_net_comms_df.pkl")

# also create a demo version with just 20 rows
logons_df = logons_full_df[logons_full_df.index.isin(
    [8, 31, 68, 111, 146, 73, 135, 46, 12, 93, 110, 36, 9, 142, 29, 130, 74, 100, 155, 70]
)]
logons_df.head(5)
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
8 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:44:10.343 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:44:10.343
9 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:11.867 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:40:11.867
12 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:03.870 MSTICAlertsWin1 - - S-1-0-0 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 0 - - 2019-02-12 04:40:03.870
29 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:11.620 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:40:11.620
31 MSTICAlertsWin1\MSTICAdmin 4624 2019-02-11 22:47:53.750 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xc54c7b9 4 - MSTICAlertsWin1 2019-02-11 22:47:53.750

Size/Shape of a DataFrame

print("shape = rows x columns")
logons_df.shape
shape = rows x columns
(20, 15)
len(logons_df)
20

Single row of DataFrame == Series

display(logons_df.iloc[0].head())
print("Type of single row - logons_df.iloc[0])", type(logons_df.iloc[0])) # First row
Account                   NT AUTHORITY\SYSTEM
EventID                                  4624
TimeGenerated      2019-02-12 04:44:10.343000
Computer                      MSTICAlertsWin1
SubjectUserName              MSTICAlertsWin1$
Name: 8, dtype: object
Type of single row - logons_df.iloc[0]) <class 'pandas.core.series.Series'>

Intersection of a row and column is a simple type - the cell content

print("\nIntersection - logons_df.iloc[0].Account")
print("Type:", type(logons_df.iloc[0].Account))
print("Value:", logons_df.iloc[0].Account)
Intersection - logons_df.iloc[0].Account
Type: <class 'str'>
Value: NT AUTHORITY\SYSTEM

Selecting Columns

df.column_name
df[column_name]

Selecting a single column

logons_df.Account.head()
8            NT AUTHORITY\SYSTEM
9            NT AUTHORITY\SYSTEM
12           NT AUTHORITY\SYSTEM
29           NT AUTHORITY\SYSTEM
31    MSTICAlertsWin1\MSTICAdmin
Name: Account, dtype: object

More general syntax (and mandatory if column name has spaces or other illegal chars, like “.”, “-“)

logons_df["Account"].head()
8            NT AUTHORITY\SYSTEM
9            NT AUTHORITY\SYSTEM
12           NT AUTHORITY\SYSTEM
29           NT AUTHORITY\SYSTEM
31    MSTICAlertsWin1\MSTICAdmin
Name: Account, dtype: object

To select multiple columns you use a Python list as the column selector

my_cols = ["Account", "TimeGenerated"]
logons_df[my_cols].head()
Account TimeGenerated
8 NT AUTHORITY\SYSTEM 2019-02-12 04:44:10.343
9 NT AUTHORITY\SYSTEM 2019-02-12 04:40:11.867
12 NT AUTHORITY\SYSTEM 2019-02-12 04:40:03.870
29 NT AUTHORITY\SYSTEM 2019-02-12 04:40:11.620
31 MSTICAlertsWin1\MSTICAdmin 2019-02-11 22:47:53.750

Or an inline/literal list

Note the double “[[” “]]” - indicating a [list], within the [] indexer syntax

logons_df[["Account", "TimeGenerated"]].head()
Account TimeGenerated
8 NT AUTHORITY\SYSTEM 2019-02-12 04:44:10.343
9 NT AUTHORITY\SYSTEM 2019-02-12 04:40:11.867
12 NT AUTHORITY\SYSTEM 2019-02-12 04:40:03.870
29 NT AUTHORITY\SYSTEM 2019-02-12 04:40:11.620
31 MSTICAlertsWin1\MSTICAdmin 2019-02-11 22:47:53.750

Use the columns property to get the column names

logons_df.columns
Index(['Account', 'EventID', 'TimeGenerated', 'Computer', 'SubjectUserName',
       'SubjectDomainName', 'SubjectUserSid', 'TargetUserName',
       'TargetDomainName', 'TargetUserSid', 'TargetLogonId', 'LogonType',
       'IpAddress', 'WorkstationName', 'TimeCreatedUtc'],
      dtype='object')

Indexes - brief introduction

Pandas default index is a monotonically-increasing integer (a Python range)

logons_df.index
Int64Index([  8,   9,  12,  29,  31,  36,  46,  68,  70,  73,  74,  93, 100,
            110, 111, 130, 135, 142, 146, 155],
           dtype='int64')

df.loc[index_value]
vs.
df.iloc[row#]

# Access a row at an index location
logons_df.loc[8]
Account                     NT AUTHORITY\SYSTEM
EventID                                    4624
TimeGenerated        2019-02-12 04:44:10.343000
Computer                        MSTICAlertsWin1
SubjectUserName                MSTICAlertsWin1$
SubjectDomainName                     WORKGROUP
SubjectUserSid                         S-1-5-18
TargetUserName                           SYSTEM
TargetDomainName                   NT AUTHORITY
TargetUserSid                          S-1-5-18
TargetLogonId                             0x3e7
LogonType                                     5
IpAddress                                     -
WorkstationName                               -
TimeCreatedUtc       2019-02-12 04:44:10.343000
Name: 8, dtype: object
# Access a row at a physical row location
logons_df.iloc[8]
Account                     NT AUTHORITY\SYSTEM
EventID                                    4624
TimeGenerated        2019-02-14 04:20:54.370000
Computer                        MSTICAlertsWin1
SubjectUserName                               -
SubjectDomainName                             -
SubjectUserSid                          S-1-0-0
TargetUserName                           SYSTEM
TargetDomainName                   NT AUTHORITY
TargetUserSid                          S-1-5-18
TargetLogonId                             0x3e7
LogonType                                     0
IpAddress                                     -
WorkstationName                               -
TimeCreatedUtc       2019-02-14 04:20:54.370000
Name: 70, dtype: object

Setting another column as index

df.set_index(column_name)

indexed_logons_df = logons_df.set_index("Account")

print("Default index")
display(logons_df.head(3))

print("Indexed by Account column")
display(indexed_logons_df.head(3))
Default index
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
8 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:44:10.343 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:44:10.343
9 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:11.867 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:40:11.867
12 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:03.870 MSTICAlertsWin1 - - S-1-0-0 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 0 - - 2019-02-12 04:40:03.870
Indexed by Account column
EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
Account
NT AUTHORITY\SYSTEM 4624 2019-02-12 04:44:10.343 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:44:10.343
NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:11.867 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:40:11.867
NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:03.870 MSTICAlertsWin1 - - S-1-0-0 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 0 - - 2019-02-12 04:40:03.870

Locating rows by index value

(note index is NOT unique)

display(indexed_logons_df.loc["MSTICAlertsWin1\\MSTICAdmin"].head(3))
EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
Account
MSTICAlertsWin1\MSTICAdmin 4624 2019-02-11 22:47:53.750 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xc54c7b9 4 - MSTICAlertsWin1 2019-02-11 22:47:53.750
MSTICAlertsWin1\MSTICAdmin 4624 2019-02-11 09:58:48.773 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xbd57571 4 - MSTICAlertsWin1 2019-02-11 09:58:48.773
MSTICAlertsWin1\MSTICAdmin 4624 2019-02-15 03:56:57.070 MSTICAlertsWin1 - - S-1-0-0 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0x1096a6d 3 131.107.147.209 IANHELLE-DEV17 2019-02-15 03:56:57.070

Physical row indexing works as before - not affected by index

indexed_logons_df.iloc[1]
EventID                                    4624
TimeGenerated        2019-02-12 04:40:11.867000
Computer                        MSTICAlertsWin1
SubjectUserName                MSTICAlertsWin1$
SubjectDomainName                     WORKGROUP
SubjectUserSid                         S-1-5-18
TargetUserName                           SYSTEM
TargetDomainName                   NT AUTHORITY
TargetUserSid                          S-1-5-18
TargetLogonId                             0x3e7
LogonType                                     5
IpAddress                                     -
WorkstationName                               -
TimeCreatedUtc       2019-02-12 04:40:11.867000
Name: NT AUTHORITY\SYSTEM, dtype: object

Accessing individual (“cell”) values

A single value

Like many things in pandas there are several ways to do something!

df.iloc[expr].ColumnName

iloc to specify a row number + column selector

df.at[index_expr, ColumnName]

at with an index expression + column name

df.iat[row#, col#]

iat is like iloc but in 2 dimensions

print("iloc + named column", logons_df.iloc[0].Account)
print("at - row idx + named column", logons_df.at[8, "Account"])
print("iat - row idx + column idx", logons_df.iat[8, 1])
iloc + named column NT AUTHORITY\SYSTEM
at - row idx + named column NT AUTHORITY\SYSTEM
iat - row idx + column idx 4624

Retrieving values from a pandas series

print(logons_df.Account.head().values)

print(list(logons_df.Account.head().values))
['NT AUTHORITY\\SYSTEM' 'NT AUTHORITY\\SYSTEM' 'NT AUTHORITY\\SYSTEM'
 'NT AUTHORITY\\SYSTEM' 'MSTICAlertsWin1\\MSTICAdmin']
['NT AUTHORITY\\SYSTEM', 'NT AUTHORITY\\SYSTEM', 'NT AUTHORITY\\SYSTEM', 'NT AUTHORITY\\SYSTEM', 'MSTICAlertsWin1\\MSTICAdmin']

pandas I/O functions

We covered import from CSV and JSON.

Some notes:

  • CSV is universal but a bit nasty and very inefficient.

  • Pickle is good but has changing different format across different Python version

Other good options are:

  • Parquet

  • HDF

  • Feather

DataFrame input functions

for func_name in dir(pd):
    if func_name.startswith("read_"):
        doc = getattr(pd, func_name).__doc__.split("\n")
        print(func_name, ":" + " " * (20 - len(func_name)) , doc[1].strip())
read_clipboard :       Read text from clipboard and pass to read_csv.
read_csv :             Read a comma-separated values (csv) file into DataFrame.
read_excel :           Read an Excel file into a pandas DataFrame.
read_feather :         Load a feather-format object from the file path.
read_fwf :             Read a table of fixed-width formatted lines into DataFrame.
read_gbq :             Load data from Google BigQuery.
read_hdf :             Read from the store, close it if we opened it.
read_html :            Read HTML tables into a ``list`` of ``DataFrame`` objects.
read_json :            Convert a JSON string to pandas object.
read_orc :             Load an ORC object from the file path, returning a DataFrame.
read_parquet :         Load a parquet object from the file path, returning a DataFrame.
read_pickle :          Load pickled pandas object (or any object) from file.
read_sas :             Read SAS files stored as either XPORT or SAS7BDAT format files.
read_spss :            Load an SPSS file from the file path, returning a DataFrame.
read_sql :             Read SQL query or database table into a DataFrame.
read_sql_query :       Read SQL query into a DataFrame.
read_sql_table :       Read SQL database table into a DataFrame.
read_stata :           Read Stata file into DataFrame.
read_table :           Read general delimited file into DataFrame.
read_xml :             Read XML document into a ``DataFrame`` object.

DataFrame output functions

df = pd.DataFrame
for func_name in dir(df):
    if func_name.startswith("to_"):
        doc = getattr(df, func_name).__doc__.split("\n")
        print(func_name, ":" + " " * (20 - len(func_name)) , doc[1].strip())
to_clipboard :         Copy object to the system clipboard.
to_csv :               Write object to a comma-separated values (csv) file.
to_dict :              Convert the DataFrame to a dictionary.
to_excel :             Write object to an Excel sheet.
to_feather :           Write a DataFrame to the binary Feather format.
to_gbq :               Write a DataFrame to a Google BigQuery table.
to_hdf :               Write the contained data to an HDF5 file using HDFStore.
to_html :              Render a DataFrame as an HTML table.
to_json :              Convert the object to a JSON string.
to_latex :             Render object to a LaTeX tabular, longtable, or nested table/tabular.
to_markdown :          Print DataFrame in Markdown-friendly format.
to_numpy :             Convert the DataFrame to a NumPy array.
to_parquet :           Write a DataFrame to the binary parquet format.
to_period :            Convert DataFrame from DatetimeIndex to PeriodIndex.
to_pickle :            Pickle (serialize) object to file.
to_records :           Convert DataFrame to a NumPy record array.
to_sql :               Write records stored in a DataFrame to a SQL database.
to_stata :             Export DataFrame object to Stata dta format.
to_string :            Render a DataFrame to a console-friendly tabular output.
to_timestamp :         Cast to DatetimeIndex of timestamps, at *beginning* of period.
to_xarray :            Return an xarray object from the pandas object.
to_xml :               Render a DataFrame to an XML document.

Export to Excel - typically need openpyxl installed (and Excel or similar)

But you don’t really need Excel any more when you have pandas!

logons_df.to_excel("../data/excel_sample.xlsx")

!start ../data/excel_sample.xlsx

read_json vs json_normalize

We saw earlier how pandas can read json formatted as records.

json_text = """
[
    {"Computer":"MSTICAlertsWin1","Account":"MSTICAdmin","NewProcessName":"ftp.exe"},
    {"Computer":"MSTICAlertsWin1","Account":"MSTICAdmin","NewProcessName":"reg.exe"},
    {"Computer":"MSTICAlertsWin1","Account":"MSTICAdmin","NewProcessName":"cmd.exe"},
    {"Computer":"MSTICAlertsWin1","Account":"MSTICAdmin","NewProcessName":"rundll32.exe"},
    {"Computer":"MSTICAlertsWin1","Account":"MSTICAdmin","NewProcessName":"rundll32.exe"}
]
"""
pd.read_json(json_text)
Computer Account NewProcessName
0 MSTICAlertsWin1 MSTICAdmin ftp.exe
1 MSTICAlertsWin1 MSTICAdmin reg.exe
2 MSTICAlertsWin1 MSTICAdmin cmd.exe
3 MSTICAlertsWin1 MSTICAdmin rundll32.exe
4 MSTICAlertsWin1 MSTICAdmin rundll32.exe

For nested structures you need json_normalize

But json_normalize expects a Python dict, not JSON

json_nested_text = """
[
    {
        "Computer":"MSTICAlertsWin1",
        "SubRecord": {"NewProcessName":"ftp.exe", "pid": 1}
    },
    {
        "Computer":"MSTICAlertsWin1",
        "SubRecord": {"NewProcessName":"reg.exe", "pid": 2}
    },
    {
        "Computer":"MSTICAlertsWin1",
        "SubRecord": {"NewProcessName":"cmd.exe", "pid": 3}
    }
]
"""

try:
    pd.json_normalize(json_nested_text)
except Exception as err:
    print("oh-oh - raw JSON!:", err)

import json

pd.json_normalize(json.loads(json_nested_text))
oh-oh - raw JSON!: 'str' object has no attribute 'values'
Computer SubRecord.NewProcessName SubRecord.pid
0 MSTICAlertsWin1 ftp.exe 1
1 MSTICAlertsWin1 reg.exe 2
2 MSTICAlertsWin1 cmd.exe 3

read_html to read tables from web pages

Tables in the web page are returned as a list of DataFrames

pd.read_html("https://attack.mitre.org/tactics/enterprise/")[0]
ID Name Description
0 TA0043 Reconnaissance The adversary is trying to gather information ...
1 TA0042 Resource Development The adversary is trying to establish resources...
2 TA0001 Initial Access The adversary is trying to get into your network.
3 TA0002 Execution The adversary is trying to run malicious code.
4 TA0003 Persistence The adversary is trying to maintain their foot...
5 TA0004 Privilege Escalation The adversary is trying to gain higher-level p...
6 TA0005 Defense Evasion The adversary is trying to avoid being detected.
7 TA0006 Credential Access The adversary is trying to steal account names...
8 TA0007 Discovery The adversary is trying to figure out your env...
9 TA0008 Lateral Movement The adversary is trying to move through your e...
10 TA0009 Collection The adversary is trying to gather data of inte...
11 TA0011 Command and Control The adversary is trying to communicate with co...
12 TA0010 Exfiltration The adversary is trying to steal data.
13 TA0040 Impact The adversary is trying to manipulate, interru...

Selecting/Searching

Specific row (or col) by number

df.iloc[row#]/df.iloc[row-range]

logons_df.iloc[2].Account
'NT AUTHORITY\\SYSTEM'
logons_df.iloc[3:6]
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
29 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:11.620 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:40:11.620
31 MSTICAlertsWin1\MSTICAdmin 4624 2019-02-11 22:47:53.750 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xc54c7b9 4 - MSTICAlertsWin1 2019-02-11 22:47:53.750
36 MSTICAlertsWin1\MSTICAdmin 4624 2019-02-11 09:58:48.773 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xbd57571 4 - MSTICAlertsWin1 2019-02-11 09:58:48.773

You can go full numpy and use iloc with int indexing

logons_df.iloc[2, 0]
'NT AUTHORITY\\SYSTEM'

Select by content - “Boolean indexing”

Basic operators

==
!=
>, <, >=, <=

logons_df["Account"] == "MSTICAlertsWin1\\MSTICAdmin"
8      False
9      False
12     False
29     False
31      True
36      True
46     False
68     False
70     False
73     False
74     False
93     False
100    False
110    False
111    False
130    False
135    False
142    False
146    False
155     True
Name: Account, dtype: bool

Use boolean result of expression to filter DataFrame

df[bool_expr]

Note

df[bool_expr] == df.loc[bool_expr]

logons_df.loc[logons_df["Account"] == "MSTICAlertsWin1\\MSTICAdmin"]
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
31 MSTICAlertsWin1\MSTICAdmin 4624 2019-02-11 22:47:53.750 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xc54c7b9 4 - MSTICAlertsWin1 2019-02-11 22:47:53.750
36 MSTICAlertsWin1\MSTICAdmin 4624 2019-02-11 09:58:48.773 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xbd57571 4 - MSTICAlertsWin1 2019-02-11 09:58:48.773
155 MSTICAlertsWin1\MSTICAdmin 4624 2019-02-15 03:56:57.070 MSTICAlertsWin1 - - S-1-0-0 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0x1096a6d 3 131.107.147.209 IANHELLE-DEV17 2019-02-15 03:56:57.070

Other operators with boolean indexing

Operators vary depending on data type!!!

logons_df.dtypes
Account                      object
EventID                       int64
TimeGenerated        datetime64[ns]
Computer                     object
SubjectUserName              object
SubjectDomainName            object
SubjectUserSid               object
TargetUserName               object
TargetDomainName             object
TargetUserSid                object
TargetLogonId                object
LogonType                     int64
IpAddress                    object
WorkstationName              object
TimeCreatedUtc       datetime64[ns]
dtype: object

Pandas supports string functions - but

logons_df[logons_df["Account"].endswith("MSTICAdmin")]
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
~\AppData\Local\Temp/ipykernel_43952/3533411303.py in <module>
----> 1 logons_df[logons_df["Account"].endswith("MSTICAdmin")]

~\AppData\Roaming\Python\Python37\site-packages\pandas\core\generic.py in __getattr__(self, name)
   5460             if self._info_axis._can_hold_identifiers_and_holds_name(name):
   5461                 return self[name]
-> 5462             return object.__getattribute__(self, name)
   5463 
   5464     def __setattr__(self, name: str, value) -> None:

AttributeError: 'Series' object has no attribute 'endswith'

What is the logons_df["Account"] in our logons_df["Account"].endswith("MSTICAdmin") expression

logons_df["Account"]
8             NT AUTHORITY\SYSTEM
9             NT AUTHORITY\SYSTEM
12            NT AUTHORITY\SYSTEM
29            NT AUTHORITY\SYSTEM
31     MSTICAlertsWin1\MSTICAdmin
36     MSTICAlertsWin1\MSTICAdmin
46            NT AUTHORITY\SYSTEM
68            NT AUTHORITY\SYSTEM
70            NT AUTHORITY\SYSTEM
73           Window Manager\DWM-1
74           Window Manager\DWM-1
93            NT AUTHORITY\SYSTEM
100          Window Manager\DWM-2
110           NT AUTHORITY\SYSTEM
111           NT AUTHORITY\SYSTEM
130           NT AUTHORITY\SYSTEM
135           NT AUTHORITY\SYSTEM
142           NT AUTHORITY\SYSTEM
146           NT AUTHORITY\SYSTEM
155    MSTICAlertsWin1\MSTICAdmin
Name: Account, dtype: object

We need to tell pandas to apply string operation as a vector function to the series

df[df[column].str.contains(str_expr)]

logons_df["Account"].str.endswith("MSTICAdmin")
8      False
9      False
12     False
29     False
31      True
36      True
46     False
68     False
70     False
73     False
74     False
93     False
100    False
110    False
111    False
130    False
135    False
142    False
146    False
155     True
Name: Account, dtype: bool
logons_df[logons_df["Account"].str.endswith("MSTICAdmin")]
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
31 MSTICAlertsWin1\MSTICAdmin 4624 2019-02-11 22:47:53.750 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xc54c7b9 4 - MSTICAlertsWin1 2019-02-11 22:47:53.750
36 MSTICAlertsWin1\MSTICAdmin 4624 2019-02-11 09:58:48.773 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xbd57571 4 - MSTICAlertsWin1 2019-02-11 09:58:48.773
155 MSTICAlertsWin1\MSTICAdmin 4624 2019-02-15 03:56:57.070 MSTICAlertsWin1 - - S-1-0-0 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0x1096a6d 3 131.107.147.209 IANHELLE-DEV17 2019-02-15 03:56:57.070

Multiple conditions

& == AND
| == OR
~ == NOT

Always use parentheses around individual expressions in composite logical expressions!

logons_df[
    (logons_df["Account"].str.endswith("SYSTEM"))
    &
    (logons_df["TimeGenerated"] >= t1)
    &
    (logons_df["TimeGenerated"] <= t2)
]
logons_df[
    logons_df["Account"].str.endswith("MSTICAdmin")
]

# We want to add a time expression
t1 = pd.Timestamp("2019-02-12 04:00")
t2 = pd.to_datetime("2019-02-12 05:00")
t1, t2
(Timestamp('2019-02-12 04:00:00'), Timestamp('2019-02-12 05:00:00'))
logons_df[
    (logons_df["Account"].str.endswith("SYSTEM"))
    &
    (logons_df["TimeGenerated"] >= t1)
    &
    (logons_df["TimeGenerated"] <= t2)
]
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
8 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:44:10.343 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:44:10.343
9 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:11.867 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:40:11.867
12 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:03.870 MSTICAlertsWin1 - - S-1-0-0 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 0 - - 2019-02-12 04:40:03.870
29 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:11.620 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:40:11.620

Without parentheses - &, |, ~ have higher precedence

### Without parentheses - `&, |, ~` have higher precedence
logons_df[
    logons_df["Account"].str.contains("MSTICAdmin")
    &
    logons_df["TimeGenerated"] >= t1
    &
    logons_df["TimeGenerated"] <= t2
]
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
~\AppData\Local\Temp/ipykernel_43952/3190794489.py in <module>
      3     logons_df["Account"].str.contains("MSTICAdmin")
      4     &
----> 5     logons_df["TimeGenerated"] >= t1
      6     &
      7     logons_df["TimeGenerated"] <= t2

~\AppData\Roaming\Python\Python37\site-packages\pandas\core\ops\common.py in new_method(self, other)
     63         other = item_from_zerodim(other)
     64 
---> 65         return method(self, other)
     66 
     67     return new_method

~\AppData\Roaming\Python\Python37\site-packages\pandas\core\arraylike.py in __and__(self, other)
     57     @unpack_zerodim_and_defer("__and__")
     58     def __and__(self, other):
---> 59         return self._logical_method(other, operator.and_)
     60 
     61     @unpack_zerodim_and_defer("__rand__")

~\AppData\Roaming\Python\Python37\site-packages\pandas\core\series.py in _logical_method(self, other, op)
   4957         rvalues = extract_array(other, extract_numpy=True)
   4958 
-> 4959         res_values = ops.logical_op(lvalues, rvalues, op)
   4960         return self._construct_result(res_values, name=res_name)
   4961 

~\AppData\Roaming\Python\Python37\site-packages\pandas\core\ops\array_ops.py in logical_op(left, right, op)
    338     if should_extension_dispatch(lvalues, rvalues):
    339         # Call the method on lvalues
--> 340         res_values = op(lvalues, rvalues)
    341 
    342     else:

TypeError: unsupported operand type(s) for &: 'numpy.ndarray' and 'DatetimeArray'
logons_df[
    (logons_df["LogonType"].isin([0, 3, 5]))
    &
    (logons_df["TimeGenerated"].dt.hour >= 4)
    &
    (logons_df["TimeGenerated"].dt.day == 12)
]
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
8 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:44:10.343 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:44:10.343
9 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:11.867 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:40:11.867
12 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:03.870 MSTICAlertsWin1 - - S-1-0-0 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 0 - - 2019-02-12 04:40:03.870
29 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:11.620 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:40:11.620
110 NT AUTHORITY\SYSTEM 4624 2019-02-12 21:20:35.003 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 21:20:35.003
111 NT AUTHORITY\SYSTEM 4624 2019-02-12 21:05:29.523 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 21:05:29.523
130 NT AUTHORITY\SYSTEM 4624 2019-02-12 20:09:16.550 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 20:09:16.550
135 NT AUTHORITY\SYSTEM 4624 2019-02-12 20:30:34.990 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 20:30:34.990
142 NT AUTHORITY\SYSTEM 4624 2019-02-12 20:19:52.520 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 20:19:52.520

Boolean indexes are Pandas series - you can save and re-use

# create individual criteria
logon_type_3 = logons_df["LogonType"].isin([0, 3, 5])
hour_4 = logons_df["TimeGenerated"].dt.hour >= 4
day_12 = logons_df["TimeGenerated"].dt.day == 12
# use them together to filter
logons_df[logon_type_3 & hour_4 & day_12]
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
8 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:44:10.343 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:44:10.343
9 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:11.867 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:40:11.867
12 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:03.870 MSTICAlertsWin1 - - S-1-0-0 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 0 - - 2019-02-12 04:40:03.870
29 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:11.620 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:40:11.620
110 NT AUTHORITY\SYSTEM 4624 2019-02-12 21:20:35.003 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 21:20:35.003
111 NT AUTHORITY\SYSTEM 4624 2019-02-12 21:05:29.523 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 21:05:29.523
130 NT AUTHORITY\SYSTEM 4624 2019-02-12 20:09:16.550 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 20:09:16.550
135 NT AUTHORITY\SYSTEM 4624 2019-02-12 20:30:34.990 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 20:30:34.990
142 NT AUTHORITY\SYSTEM 4624 2019-02-12 20:19:52.520 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 20:19:52.520

isin operator/function

logons_df[logons_df["TargetUserName"].isin(["MSTICAdmin", "SYSTEM"])].head()
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
8 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:44:10.343 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:44:10.343
9 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:11.867 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:40:11.867
12 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:03.870 MSTICAlertsWin1 - - S-1-0-0 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 0 - - 2019-02-12 04:40:03.870
29 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:11.620 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:40:11.620
31 MSTICAlertsWin1\MSTICAdmin 4624 2019-02-11 22:47:53.750 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xc54c7b9 4 - MSTICAlertsWin1 2019-02-11 22:47:53.750

pandas query function

df.query(query_str)

Useful for simpler queries - and definitely nicer-looking but some limitations - only simple operators supported.

Good for quick things but I prefer the boolean stuff for more complex queries.

To reference Python variables prefix the variable name with “@” (see second example)

logons_df.query("TargetUserName == 'MSTICAdmin' and TargetLogonId == '0xc913737'")

logons_df.query("TargetUserName == 'MSTICAdmin' and TimeGenerated > @t1")
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
155 MSTICAlertsWin1\MSTICAdmin 4624 2019-02-15 03:56:57.070 MSTICAlertsWin1 - - S-1-0-0 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0x1096a6d 3 131.107.147.209 IANHELLE-DEV17 2019-02-15 03:56:57.070

The output of query is a DataFrame so you can also easily combine with boolean indexing

or part of a longer pandas expression.

(
    logons_df[logons_df["Account"].str.match("MST.*")]
    .query("TimeGenerated > @t1")
)
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
155 MSTICAlertsWin1\MSTICAdmin 4624 2019-02-15 03:56:57.070 MSTICAlertsWin1 - - S-1-0-0 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0x1096a6d 3 131.107.147.209 IANHELLE-DEV17 2019-02-15 03:56:57.070

Combing Column Select and filter

(
    logons_df[logons_df["Account"].str.contains("MSTICAdmin")]
    [["Account", "TimeGenerated"]]
)
Account TimeGenerated
31 MSTICAlertsWin1\MSTICAdmin 2019-02-11 22:47:53.750
36 MSTICAlertsWin1\MSTICAdmin 2019-02-11 09:58:48.773
155 MSTICAlertsWin1\MSTICAdmin 2019-02-15 03:56:57.070

Sorting and removing duplicates

df.sort_values(column|[column_list]], [ascending=True|False])

logons_df.sort_values("TimeGenerated", ascending=False).head(3)
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
146 NT AUTHORITY\SYSTEM 4624 2019-02-15 06:51:51.500 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-15 06:51:51.500
155 MSTICAlertsWin1\MSTICAdmin 4624 2019-02-15 03:56:57.070 MSTICAlertsWin1 - - S-1-0-0 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0x1096a6d 3 131.107.147.209 IANHELLE-DEV17 2019-02-15 03:56:57.070
68 NT AUTHORITY\SYSTEM 4624 2019-02-14 04:21:37.637 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-14 04:21:37.637

df.drop_duplicates()

(
    logons_df[["Account", "LogonType"]]
    .drop_duplicates()
    .sort_values("Account")
)
Account LogonType
31 MSTICAlertsWin1\MSTICAdmin 4
155 MSTICAlertsWin1\MSTICAdmin 3
8 NT AUTHORITY\SYSTEM 5
12 NT AUTHORITY\SYSTEM 0
73 Window Manager\DWM-1 2
100 Window Manager\DWM-2 2

Grouping and Aggregation

df.groupby(column|[column_list]])

logons_df.groupby("Account")
<pandas.core.groupby.generic.DataFrameGroupBy object at 0x000001D81E76DAC8>

You need an aggregator (or iterator) make use of grouping

Add an aggregation function: sum, count, mean, stdev, etc.

logons_df.groupby("Account").count()  # Yuk!
EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
Account
MSTICAlertsWin1\MSTICAdmin 3 3 3 3 3 3 3 3 3 3 3 3 3 3
NT AUTHORITY\SYSTEM 14 14 14 14 14 14 14 14 14 14 14 14 14 14
Window Manager\DWM-1 2 2 2 2 2 2 2 2 2 2 2 2 2 2
Window Manager\DWM-2 1 1 1 1 1 1 1 1 1 1 1 1 1 1

Tidy up by limiting and renaming columns

(
    logons_df[["TimeGenerated", "Account"]]
    .groupby("Account")
    .count()
    .rename(columns={"TimeGenerated": "LogonCount"})
)
LogonCount
Account
MSTICAlertsWin1\MSTICAdmin 3
NT AUTHORITY\SYSTEM 14
Window Manager\DWM-1 2
Window Manager\DWM-2 1

Iterating over groups - groupby returns an iterable

print("Numbers of rows in each group:")

for name, logon_group in logons_df.groupby("Account"):
    print(name, type(logon_group), "size", logon_group.shape)
Numbers of rows in each group:
MSTICAlertsWin1\MSTICAdmin <class 'pandas.core.frame.DataFrame'> size (3, 15)
NT AUTHORITY\SYSTEM <class 'pandas.core.frame.DataFrame'> size (14, 15)
Window Manager\DWM-1 <class 'pandas.core.frame.DataFrame'> size (2, 15)
Window Manager\DWM-2 <class 'pandas.core.frame.DataFrame'> size (1, 15)
print("\nCollect individual group DFs in dictionary")
df_dict = {name: df for name, df in logons_df.groupby("Account")}

print(df_dict.keys())
df_dict["NT AUTHORITY\SYSTEM"].head()
Collect individual group DFs in dictionary
dict_keys(['MSTICAlertsWin1\\MSTICAdmin', 'NT AUTHORITY\\SYSTEM', 'Window Manager\\DWM-1', 'Window Manager\\DWM-2'])
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
8 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:44:10.343 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:44:10.343
9 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:11.867 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:40:11.867
12 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:03.870 MSTICAlertsWin1 - - S-1-0-0 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 0 - - 2019-02-12 04:40:03.870
29 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:11.620 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:40:11.620
46 NT AUTHORITY\SYSTEM 4624 2019-02-10 05:10:54.300 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-10 05:10:54.300

Grouping with Multiple aggregation functions

.agg({"Column_1": "agg_func", "Column_2": "agg_func"})

import numpy as np

(
    logons_df[["TimeGenerated", "LogonType", "Account"]]
    .groupby("Account")
    .agg({"TimeGenerated": "max", "LogonType": "nunique"})
    .rename(columns={"TimeGenerated": "LastTime"})
)
LastTime LogonType
Account
MSTICAlertsWin1\MSTICAdmin 2019-02-15 03:56:57.070 2
NT AUTHORITY\SYSTEM 2019-02-15 06:51:51.500 2
Window Manager\DWM-1 2019-02-14 04:20:54.773 1
Window Manager\DWM-2 2019-02-12 22:22:21.240 1

Grouping with multiple columns

.groupby(["Account", "LogonType"])

(
    logons_full_df[["TimeGenerated", "EventID", "Account", "LogonType"]]      # DF input fields
    .groupby(["Account", "LogonType"])                                        # Grouping fields
    .agg({"TimeGenerated": "max", "EventID": "count"})                        # aggregate operations
    .rename(columns={"TimeGenerated": "LastTime", "EventID": "Count"})        # Rename output
)
LastTime Count
Account LogonType
MSTICAlertsWin1\MSTICAdmin 3 2019-02-15 03:57:00.207 8
4 2019-02-14 11:51:37.603 8
10 2019-02-15 03:57:02.593 2
MSTICAlertsWin1\ian 2 2019-02-12 20:29:51.030 2
3 2019-02-15 03:56:34.440 5
4 2019-02-12 20:41:17.310 1
NT AUTHORITY\IUSR 5 2019-02-14 04:20:56.110 2
NT AUTHORITY\LOCAL SERVICE 5 2019-02-14 04:20:54.803 2
NT AUTHORITY\NETWORK SERVICE 5 2019-02-14 04:20:54.630 2
NT AUTHORITY\SYSTEM 0 2019-02-14 04:20:54.370 2
5 2019-02-15 11:51:37.597 120
Window Manager\DWM-1 2 2019-02-14 04:20:54.773 4
Window Manager\DWM-2 2 2019-02-15 03:57:01.903 6

Using pd.Grouper to group by time interval

.groupby(["Account", pd.Grouper(key="TimeGenerated", freq="1D")])

(
    logons_full_df[["TimeGenerated", "EventID", "Account", "LogonType"]]
    .groupby(["Account", pd.Grouper(key="TimeGenerated", freq="1D")])
    .agg({"TimeGenerated": "max", "EventID": "count"})
    .rename(columns={"TimeGenerated": "LastTime", "EventID": "Count"})
)
LastTime Count
Account TimeGenerated
MSTICAlertsWin1\MSTICAdmin 2019-02-09 2019-02-09 23:26:47.700 1
2019-02-11 2019-02-11 22:47:53.750 4
2019-02-12 2019-02-12 20:19:44.767 7
2019-02-13 2019-02-13 23:07:23.823 2
2019-02-14 2019-02-14 11:51:37.603 1
2019-02-15 2019-02-15 03:57:02.593 3
MSTICAlertsWin1\ian 2019-02-12 2019-02-12 20:41:17.310 3
2019-02-13 2019-02-13 00:57:37.187 3
2019-02-15 2019-02-15 03:56:34.440 2
NT AUTHORITY\IUSR 2019-02-12 2019-02-12 04:40:12.360 1
2019-02-14 2019-02-14 04:20:56.110 1
NT AUTHORITY\LOCAL SERVICE 2019-02-12 2019-02-12 04:40:04.573 1
2019-02-14 2019-02-14 04:20:54.803 1
NT AUTHORITY\NETWORK SERVICE 2019-02-12 2019-02-12 04:40:04.207 1
2019-02-14 2019-02-14 04:20:54.630 1
NT AUTHORITY\SYSTEM 2019-02-09 2019-02-09 12:35:51.683 2
2019-02-10 2019-02-10 21:47:21.503 11
2019-02-11 2019-02-11 09:59:02.593 2
2019-02-12 2019-02-12 22:20:59.200 53
2019-02-13 2019-02-13 22:08:46.537 10
2019-02-14 2019-02-14 14:51:37.637 33
2019-02-15 2019-02-15 11:51:37.597 11
Window Manager\DWM-1 2019-02-12 2019-02-12 04:40:04.483 2
2019-02-14 2019-02-14 04:20:54.773 2
Window Manager\DWM-2 2019-02-12 2019-02-12 22:22:21.240 4
2019-02-15 2019-02-15 03:57:01.903 2

Adding and removing columns

df[column_name] = expr

new_df = logons_df.copy()

# Adding a static value
new_df["StaticValue"] = "A logon"

# Extracting a substring (there are several ways to do this)
new_df["NTDomain"] = new_df.Account.str.split("\\", 1, expand=True)[0]

# Transforming using an accessor
new_df["DayOfWeek"] = new_df.TimeGenerated.dt.day_name()

# Arithmetic calculations
new_df["BigEventID"] = new_df.EventID * 1000000
new_df["SameTimeTomorrow"] = new_df.TimeGenerated + pd.Timedelta("1D")

print("Old")
display(logons_df[["Account", "TimeGenerated", "EventID"]].head())
print("New")
new_df[[
    "Account", "TimeGenerated", "StaticValue", "NTDomain", "DayOfWeek", "BigEventID", "SameTimeTomorrow"
]].head()
Old
Account TimeGenerated EventID
8 NT AUTHORITY\SYSTEM 2019-02-12 04:44:10.343 4624
9 NT AUTHORITY\SYSTEM 2019-02-12 04:40:11.867 4624
12 NT AUTHORITY\SYSTEM 2019-02-12 04:40:03.870 4624
29 NT AUTHORITY\SYSTEM 2019-02-12 04:40:11.620 4624
31 MSTICAlertsWin1\MSTICAdmin 2019-02-11 22:47:53.750 4624
New
Account TimeGenerated StaticValue NTDomain DayOfWeek BigEventID SameTimeTomorrow
8 NT AUTHORITY\SYSTEM 2019-02-12 04:44:10.343 A logon NT AUTHORITY Tuesday 4624000000 2019-02-13 04:44:10.343
9 NT AUTHORITY\SYSTEM 2019-02-12 04:40:11.867 A logon NT AUTHORITY Tuesday 4624000000 2019-02-13 04:40:11.867
12 NT AUTHORITY\SYSTEM 2019-02-12 04:40:03.870 A logon NT AUTHORITY Tuesday 4624000000 2019-02-13 04:40:03.870
29 NT AUTHORITY\SYSTEM 2019-02-12 04:40:11.620 A logon NT AUTHORITY Tuesday 4624000000 2019-02-13 04:40:11.620
31 MSTICAlertsWin1\MSTICAdmin 2019-02-11 22:47:53.750 A logon MSTICAlertsWin1 Monday 4624000000 2019-02-12 22:47:53.750

assign function

Note this introduces a new column to the output - it does not update the dataframe.

df.assign(NewColumn=expr)

(
    new_df[["Account", "TimeGenerated", "DayOfWeek", "SameTimeTomorrow"]]
    .assign(
        SameTimeLastWeek=new_df.TimeGenerated - pd.Timedelta("1W"),
        When=new_df.StaticValue.str.cat(new_df.DayOfWeek, sep=" happened on "),
    )
)
Account TimeGenerated DayOfWeek SameTimeTomorrow SameTimeLastWeek When
8 NT AUTHORITY\SYSTEM 2019-02-12 04:44:10.343 Tuesday 2019-02-13 04:44:10.343 2019-02-05 04:44:10.343 A logon happened on Tuesday
9 NT AUTHORITY\SYSTEM 2019-02-12 04:40:11.867 Tuesday 2019-02-13 04:40:11.867 2019-02-05 04:40:11.867 A logon happened on Tuesday
12 NT AUTHORITY\SYSTEM 2019-02-12 04:40:03.870 Tuesday 2019-02-13 04:40:03.870 2019-02-05 04:40:03.870 A logon happened on Tuesday
29 NT AUTHORITY\SYSTEM 2019-02-12 04:40:11.620 Tuesday 2019-02-13 04:40:11.620 2019-02-05 04:40:11.620 A logon happened on Tuesday
31 MSTICAlertsWin1\MSTICAdmin 2019-02-11 22:47:53.750 Monday 2019-02-12 22:47:53.750 2019-02-04 22:47:53.750 A logon happened on Monday
36 MSTICAlertsWin1\MSTICAdmin 2019-02-11 09:58:48.773 Monday 2019-02-12 09:58:48.773 2019-02-04 09:58:48.773 A logon happened on Monday
46 NT AUTHORITY\SYSTEM 2019-02-10 05:10:54.300 Sunday 2019-02-11 05:10:54.300 2019-02-03 05:10:54.300 A logon happened on Sunday
68 NT AUTHORITY\SYSTEM 2019-02-14 04:21:37.637 Thursday 2019-02-15 04:21:37.637 2019-02-07 04:21:37.637 A logon happened on Thursday
70 NT AUTHORITY\SYSTEM 2019-02-14 04:20:54.370 Thursday 2019-02-15 04:20:54.370 2019-02-07 04:20:54.370 A logon happened on Thursday
73 Window Manager\DWM-1 2019-02-14 04:20:54.773 Thursday 2019-02-15 04:20:54.773 2019-02-07 04:20:54.773 A logon happened on Thursday
74 Window Manager\DWM-1 2019-02-14 04:20:54.773 Thursday 2019-02-15 04:20:54.773 2019-02-07 04:20:54.773 A logon happened on Thursday
93 NT AUTHORITY\SYSTEM 2019-02-13 20:11:41.150 Wednesday 2019-02-14 20:11:41.150 2019-02-06 20:11:41.150 A logon happened on Wednesday
100 Window Manager\DWM-2 2019-02-12 22:22:21.240 Tuesday 2019-02-13 22:22:21.240 2019-02-05 22:22:21.240 A logon happened on Tuesday
110 NT AUTHORITY\SYSTEM 2019-02-12 21:20:35.003 Tuesday 2019-02-13 21:20:35.003 2019-02-05 21:20:35.003 A logon happened on Tuesday
111 NT AUTHORITY\SYSTEM 2019-02-12 21:05:29.523 Tuesday 2019-02-13 21:05:29.523 2019-02-05 21:05:29.523 A logon happened on Tuesday
130 NT AUTHORITY\SYSTEM 2019-02-12 20:09:16.550 Tuesday 2019-02-13 20:09:16.550 2019-02-05 20:09:16.550 A logon happened on Tuesday
135 NT AUTHORITY\SYSTEM 2019-02-12 20:30:34.990 Tuesday 2019-02-13 20:30:34.990 2019-02-05 20:30:34.990 A logon happened on Tuesday
142 NT AUTHORITY\SYSTEM 2019-02-12 20:19:52.520 Tuesday 2019-02-13 20:19:52.520 2019-02-05 20:19:52.520 A logon happened on Tuesday
146 NT AUTHORITY\SYSTEM 2019-02-15 06:51:51.500 Friday 2019-02-16 06:51:51.500 2019-02-08 06:51:51.500 A logon happened on Friday
155 MSTICAlertsWin1\MSTICAdmin 2019-02-15 03:56:57.070 Friday 2019-02-16 03:56:57.070 2019-02-08 03:56:57.070 A logon happened on Friday

Drop columns

df.drop(columns=[column_list])

df.drop(columns=[column_list], inplace=True) # Beware!

(
    new_df[["Account", "TimeGenerated", "StaticValue", "NTDomain", "DayOfWeek"]]
    .head()
    .drop(columns=["NTDomain"])
)
Account TimeGenerated StaticValue DayOfWeek
8 NT AUTHORITY\SYSTEM 2019-02-12 04:44:10.343 A logon Tuesday
9 NT AUTHORITY\SYSTEM 2019-02-12 04:40:11.867 A logon Tuesday
12 NT AUTHORITY\SYSTEM 2019-02-12 04:40:03.870 A logon Tuesday
29 NT AUTHORITY\SYSTEM 2019-02-12 04:40:11.620 A logon Tuesday
31 MSTICAlertsWin1\MSTICAdmin 2019-02-11 22:47:53.750 A logon Monday

Some other quick ways of filtering out (in) columns

.filter(regex="Target.*", axis=1)

logons_df.columns
Index(['Account', 'EventID', 'TimeGenerated', 'Computer', 'SubjectUserName',
       'SubjectDomainName', 'SubjectUserSid', 'TargetUserName',
       'TargetDomainName', 'TargetUserSid', 'TargetLogonId', 'LogonType',
       'IpAddress', 'WorkstationName', 'TimeCreatedUtc'],
      dtype='object')
logons_df.filter(regex="Target.*", axis=1).head()
TargetUserName TargetDomainName TargetUserSid TargetLogonId
8 SYSTEM NT AUTHORITY S-1-5-18 0x3e7
9 SYSTEM NT AUTHORITY S-1-5-18 0x3e7
12 SYSTEM NT AUTHORITY S-1-5-18 0x3e7
29 SYSTEM NT AUTHORITY S-1-5-18 0x3e7
31 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xc54c7b9

Filter by Data Type

.select_dtypes(include="datetime")

logons_df.select_dtypes(include="datetime").head()  # also "number", "object"
TimeGenerated TimeCreatedUtc
8 2019-02-12 04:44:10.343 2019-02-12 04:44:10.343
9 2019-02-12 04:40:11.867 2019-02-12 04:40:11.867
12 2019-02-12 04:40:03.870 2019-02-12 04:40:03.870
29 2019-02-12 04:40:11.620 2019-02-12 04:40:11.620
31 2019-02-11 22:47:53.750 2019-02-11 22:47:53.750

Simple Joins

pd.concat([df_list])

(relational joins tomorrow)

Concatenating DFs

# Extract two DFs from subset of rows
df1 = logons_full_df[0:10]
df2 = logons_full_df[100:120]

print("Dimensions of DFs (rows, cols)")
print("df1:", df1.shape, "df2:", df2.shape)
display(df1.tail(3))
display(df2.tail(3))
Dimensions of DFs (rows, cols)
df1: (10, 15) df2: (20, 15)
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
7 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:43:56.327 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:43:56.327
8 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:44:10.343 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:44:10.343
9 NT AUTHORITY\SYSTEM 4624 2019-02-12 04:40:11.867 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 04:40:11.867
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
117 NT AUTHORITY\SYSTEM 4624 2019-02-12 21:49:11.777 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 21:49:11.777
118 NT AUTHORITY\SYSTEM 4624 2019-02-12 21:39:15.897 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 21:39:15.897
119 NT AUTHORITY\SYSTEM 4624 2019-02-12 20:11:06.790 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 20:11:06.790

Joining rows

pd.concat([df_1, df_2...])

joined_df = pd.concat([df1, df2])

print(joined_df.shape)
joined_df.tail(3)
(30, 15)
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
117 NT AUTHORITY\SYSTEM 4624 2019-02-12 21:49:11.777 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 21:49:11.777
118 NT AUTHORITY\SYSTEM 4624 2019-02-12 21:39:15.897 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 21:39:15.897
119 NT AUTHORITY\SYSTEM 4624 2019-02-12 20:11:06.790 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 20:11:06.790
joined_df.index
Int64Index([  0,   1,   2,   3,   4,   5,   6,   7,   8,   9, 100, 101, 102,
            103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115,
            116, 117, 118, 119],
           dtype='int64')

ignore_index=True causes Python to regenerate a new index

pd.concat(df_list, ignore_index=True)

df_list = [df1, df2]
joined_df = pd.concat(df_list, ignore_index=True)

print(joined_df.shape)
joined_df.tail(3)
(30, 15)
Account EventID TimeGenerated Computer SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId LogonType IpAddress WorkstationName TimeCreatedUtc
27 NT AUTHORITY\SYSTEM 4624 2019-02-12 21:49:11.777 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 21:49:11.777
28 NT AUTHORITY\SYSTEM 4624 2019-02-12 21:39:15.897 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 21:39:15.897
29 NT AUTHORITY\SYSTEM 4624 2019-02-12 20:11:06.790 MSTICAlertsWin1 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7 5 - - 2019-02-12 20:11:06.790
joined_df.index
RangeIndex(start=0, stop=30, step=1)

Joining columns (horizontal)

pd.concat([df_1, df_2...], axis="columns")

df_col_1 = logons_full_df[0:10].filter(regex="Subject.*")
df_col_2 = logons_full_df[0:12].filter(regex="Target.*")

print(df_col_1.shape, df_col_2.shape)
display(df_col_1.head())
display(df_col_2.head())
(10, 3) (12, 4)
SubjectUserName SubjectDomainName SubjectUserSid
0 MSTICAlertsWin1$ WORKGROUP S-1-5-18
1 - - S-1-0-0
2 - - S-1-0-0
3 - - S-1-0-0
4 - - S-1-0-0
TargetUserName TargetDomainName TargetUserSid TargetLogonId
0 SYSTEM NT AUTHORITY S-1-5-18 0x3e7
1 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xc90e957
2 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xc90ea44
3 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xc912d62
4 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xc913737
pd.concat([df_col_1, df_col_2], axis="columns")
SubjectUserName SubjectDomainName SubjectUserSid TargetUserName TargetDomainName TargetUserSid TargetLogonId
0 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7
1 - - S-1-0-0 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xc90e957
2 - - S-1-0-0 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xc90ea44
3 - - S-1-0-0 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xc912d62
4 - - S-1-0-0 MSTICAdmin MSTICAlertsWin1 S-1-5-21-996632719-2361334927-4038480536-500 0xc913737
5 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7
6 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7
7 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7
8 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7
9 MSTICAlertsWin1$ WORKGROUP S-1-5-18 SYSTEM NT AUTHORITY S-1-5-18 0x3e7
10 NaN NaN NaN IUSR NT AUTHORITY S-1-5-17 0x3e3
11 NaN NaN NaN SYSTEM NT AUTHORITY S-1-5-18 0x3e7

Statistics 101 with Pandas

In this part of the workshop we will use a statistical approach to perform data analysis. There are two basic types of statistical analysis: Descriptive and Inferential. During this workshop, we will focus on Descriptive Analysis.

For the purpose of this section, we will use a network compound Security Dataset that you can find here. Therefore, let’s start by importing the dataset.

import pandas as pd
import json

# Opeing the log file
zeek_data = open('../data/combined_zeek.log','r')
# Creating a list of dictionaries
zeek_list = []
for dict in zeek_data:
    zeek_list.append(json.loads(dict))
# Closing the log file
zeek_data.close()
# Creating a dataframe
zeek_df = pd.DataFrame(data = zeek_list)
zeek_df.head()
@stream @system @proc ts uid id_orig_h id_orig_p id_resp_h id_resp_p proto ... is_64bit uses_aslr uses_dep uses_code_integrity uses_seh has_import_table has_export_table has_cert_table has_debug_data section_names
0 conn bobs.bigwheel.local zeek 1.588205e+09 Cvf4XX17hSAgXDdGEd 10.0.1.6 54243.0 10.0.0.4 53.0 udp ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1 conn bobs.bigwheel.local zeek 1.588205e+09 CJ21Le4zsTUcyKKi98 10.0.1.6 56880.0 10.0.0.4 445.0 tcp ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
2 conn bobs.bigwheel.local zeek 1.588205e+09 CnOP7t1eGGHf6LFfuk 10.0.1.6 65108.0 10.0.0.4 53.0 udp ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
3 conn bobs.bigwheel.local zeek 1.588205e+09 CvxbPE3MuO7boUdSc8 10.0.1.6 138.0 10.0.1.255 138.0 udp ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
4 conn bobs.bigwheel.local zeek 1.588205e+09 CuRbE21APSQo2qd6rk 10.0.1.6 123.0 10.0.0.4 123.0 udp ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

5 rows × 148 columns

Data Types

Before we start reviewing different descriptive analysis techniques, it is important to understand the type of data we are collecting in order to apply these techniques accordingly.

Numerical data

This type of data represent the output of counting or measuring activities. Numerical data values are usually represented by numbers, and arithmetic calculations such as addition or subtraction do add context to our analysis.

  • The quantity of network packets transferred over our network is a good example of numerical data generated by counting activities. This type of numerical data is also known as discrete data.

zeek_df[['service','id_orig_h','orig_pkts']].head()
service id_orig_h orig_pkts
0 dns 10.0.1.6 1.0
1 gssapi,smb,krb 10.0.1.6 12.0
2 dns 10.0.1.6 1.0
3 NaN 10.0.1.6 1.0
4 NaN 10.0.1.6 1.0
  • The network connection duration is a good example of numerical data generated by measuring activities. This type of numerical data is also known as continuous data.

zeek_df[['service','id_orig_h','duration']].head()
service id_orig_h duration
0 dns 10.0.1.6 0.001528
1 gssapi,smb,krb 10.0.1.6 10.761077
2 dns 10.0.1.6 0.001599
3 NaN 10.0.1.6 NaN
4 NaN 10.0.1.6 0.003069

Categorical data

This type of data represents categories or qualities. Categorical data values are usually described using characters or strings of characters. Moreover, categorical data values can also be represented by numbers. Unlike numerical data, arithmetic operations such as addition or subtraction do not add any extra context.

  • The network protocol used creating a network connection is a good example of categorical data that describes a category, and does not give us any sense of order (We cannot compare among categories). This type of categorical data is also known as nominal data.

zeek_df[['service','id_orig_h','proto']].head()
service id_orig_h proto
0 dns 10.0.1.6 udp
1 gssapi,smb,krb 10.0.1.6 tcp
2 dns 10.0.1.6 udp
3 NaN 10.0.1.6 udp
4 NaN 10.0.1.6 udp
  • Another type of categorical data is known as ordinal data. Unlike nominal data, this type of data gives a sense of order (We can compare among categories). A good example of this type of data is the Integrity Level of a process: Low, Medium, High, System. Using the integrity level field as a reference, we can organize our processes from lower to high integrity level (Access Rights).

Descriptive Analysis for Categorical data

Categorical data types in Pandas

Pandas uses the category data type to represent both nominal and ordinal data. Let’s check the current type of data for the protocol field we reviewed previously:

zeek_df[['proto','service']].dtypes
proto      object
service    object
dtype: object

As you can see in the previous cell, the current type of data for protocol is string. We can change the type of data to cateogry using the astype method.

zeek_df = zeek_df.astype({'proto': 'category','service': 'category'})
zeek_df[['proto','service']].dtypes
proto      category
service    category
dtype: object

Describe Method

Using the describe method on categorical data will calculate the following statistics.

zeek_df['service'].describe()
count     521
unique     17
top       ssl
freq      378
Name: service, dtype: object

Frequency of Values

We can use the groupby, size and sort_values methods to calculate the frequency of network connections by network service.

zeek_df.groupby(['service']).size().sort_values(ascending=False)
service
ssl                           378
dns                            39
krb_tcp                        25
dce_rpc                        20
gssapi                         12
krbtgt/DMEVALS.LOCAL            9
krb,smb,gssapi                  7
krbtgt/dmevals                  6
gssapi,smb,krb                  6
http                            6
cifs/NASHUA                     4
host/nashua.dmevals.local       3
krb,smb,dce_rpc,gssapi          2
gssapi,smb,krb,dce_rpc          1
cifs/NEWYORK                    1
ldap/NEWYORK.dmevals.local      1
HTTP/NASHUA                     1
dtype: int64

Central Tendency

Central tendency metrics are values that intent to describe a whole group of values. One example of central tendency metric for categorical data is the mode or most frequent value. We can use the mode method to calculate it.

Another central tendency metric that we could use with categorical data is the median, but we can use it only with ordinal data.

zeek_df['service'].mode()
0    ssl
Name: service, dtype: category
Categories (17, object): ['HTTP/NASHUA', 'cifs/NASHUA', 'cifs/NEWYORK', 'dce_rpc', ..., 'krbtgt/DMEVALS.LOCAL', 'krbtgt/dmevals', 'ldap/NEWYORK.dmevals.local', 'ssl']

Correlation

We can use the crosstab method to create a crossed table with two or more factors.

pd.crosstab(index = zeek_df['service'], columns = zeek_df['proto'])
proto tcp udp
service
dce_rpc 20 0
dns 0 39
gssapi 12 0
gssapi,smb,krb 6 0
gssapi,smb,krb,dce_rpc 1 0
http 6 0
krb,smb,dce_rpc,gssapi 2 0
krb,smb,gssapi 7 0
krb_tcp 25 0
ssl 378 0

Descriptive Analysis for Numerical data

Numerical data type in Pandas

Pandas uses the numeric data type to represent both discrete and continuous data. The numeric data type includes integer and float Python data types.

numerical_data = zeek_df[['duration','orig_bytes','orig_pkts','resp_bytes','resp_pkts']]
numerical_data.dtypes
duration      float64
orig_bytes    float64
orig_pkts     float64
resp_bytes    float64
resp_pkts     float64
dtype: object

We can use the astype method to convert the numeric data type. For example, let’s change the data type for orig_pkts and resp_pkts to integer. We are using the Nullable Integer data type.

numerical_data_updated = numerical_data.astype({'orig_pkts':'Int64','resp_pkts': 'Int64'}, errors = 'ignore')
numerical_data_updated.dtypes
duration      float64
orig_bytes    float64
orig_pkts       Int64
resp_bytes    float64
resp_pkts       Int64
dtype: object

Describe Method

Using the describe method on numerical data will calculate the following statistics.

numerical_data_updated.describe()
duration orig_bytes orig_pkts resp_bytes resp_pkts
count 1025.000000 5.770000e+02 613.000000 5.770000e+02 613.000000
mean 4.569904 2.648355e+04 20.714519 7.068313e+04 27.353997
std 65.376324 2.709592e+05 255.897916 1.344514e+06 446.150463
min 0.000000 0.000000e+00 1.000000 0.000000e+00 0.000000
25% 0.000000 9.970000e+02 5.000000 1.514000e+03 5.000000
50% 0.002708 9.970000e+02 7.000000 1.823000e+03 6.000000
75% 0.010263 9.970000e+02 7.000000 1.823000e+03 6.000000
max 1901.216208 4.261160e+06 6281.000000 3.185056e+07 11017.000000

Frequency of Values

Similar to categorical data, we can use the groupby and size methods to calculate the frequency of values. However, sometimes the output might not be the desired, especially when working with continuous data. In this case, we might need to group our values into bins.

We can use the cut method to generate bins with our data.

# Creating a Series with duration data
duration_data = numerical_data_updated['duration']
# Adding duration_bin column
numerical_data_updated['duration_bin'] = pd.cut(duration_data, bins = 500)
# Counting network connections per bin (Top 15)
numerical_data_updated.groupby(['duration_bin']).size()[:15]
duration_bin
(-1.901, 3.802]     975
(3.802, 7.605]        3
(7.605, 11.407]      16
(11.407, 15.21]       9
(15.21, 19.012]       0
(19.012, 22.815]      2
(22.815, 26.617]      0
(26.617, 30.419]      0
(30.419, 34.222]      1
(34.222, 38.024]      2
(38.024, 41.827]      0
(41.827, 45.629]      4
(45.629, 49.432]      0
(49.432, 53.234]      0
(53.234, 57.036]      4
dtype: int64

We can use the hist method to visualize the distribution of frequencies.

# Filtering duration values less or equal to 0.02
numerical_data_updated[numerical_data_updated['duration'] <= 0.02].hist(column = 'duration')
array([[<AxesSubplot:title={'center':'duration'}>]], dtype=object)
../../_images/day1-3-Data-Analysis-with-Pandas Intro_197_1.png

Central Tendency

Central tendency metrics are values that intent to describe a whole group of values.

One example of central tendency metric for numerical data is the mode or most frequent value. We can use the mode method to calculate it.

zeek_df['duration'].mode()
0    0.0
dtype: float64

Another central tendency metric that we could use with numerical data is the mean or average value. We can use the mean method to calculate it.

zeek_df['duration'].mean()
4.569903998491241

The mean or average is a good central tendency metric when the distribution of our data is not shifted to one side (Right or Left) or not skewed. If the distribution of our data is skewed, there might be extreme values (Short or Large) in our data that affect the value of our mean. A central tendency metric that is not affected by extreme values is the median. We can use the median method to calculate it.

zeek_df['duration'].median()
0.002707958221435547

Shape of Distribution of Frequencies

In the previous section we mentioned that our data might contain extreme values (Short or Large) that affect the calculation of the mean or average of numerical data. These extreme values could also impact the shape of the distribution of frequencies of our data.

One metric that can help us to describe the shape of the distribution of frequencies is Kurtosis. This metric identifies whether the tails of a given distribution contains extreme values. A Kurtosis value greater than 3 might indicate the presence of large outliers. On the other hand, a Kurtosis value less than 3 might indicate the presence of small outliers.

We can use the kurtosis method to calculate it.

zeek_df['duration'].kurtosis()
706.6612212729053

Another metric that can help us to describe the shape of the distribution of frequencies of our data is Skewness. This metric identifies if the shape of our distribution of frequencies deviates from the symmetrical bell curve, or normal distribution. In other words, it identifies if the distribution of frequencies is shifted to the right or to the left.

A negative value for skewness indicates that our distribution of frequencies is left skewed (left tail). On the other hand, a positive value for skewness indicates that our distribution of frequencies is right skewed (right tail).

We can use the skew method to calculate it.

zeek_df['duration'].skew()
25.313750482239236

Variability

After calculating central tendency and shape metrics, we identified the presence of potential extreme values. These extreme values are different from most of our data values. This means that there exists variability among our data values.

Let’s start by visually describing the variability of our data using a box plot. We can use the boxplot method to graph one.

# Filtering duration values less or equal to 0.02
numerical_data_updated[numerical_data_updated['duration'] <= 0.02].boxplot(column = 'duration', vert = False, grid = False)
print(numerical_data_updated['duration'].describe())
count    1025.000000
mean        4.569904
std        65.376324
min         0.000000
25%         0.000000
50%         0.002708
75%         0.010263
max      1901.216208
Name: duration, dtype: float64
../../_images/day1-3-Data-Analysis-with-Pandas Intro_209_1.png

A very basic metric that we can use to describe the variability in our data is the Range of values, which is the difference between the maximum and minimum value. We can use the min and max methods to calculate the range.

range = zeek_df['duration'].max() - zeek_df['duration'].min()
range
1901.2162079811096

Another metric that we can use is the Interquartile Range (IQR) of values, which measures the variability or spread of the middle half of our data. We calculate it by subtracting the first quartile (25%) from the third quartile (75%). We can use the quantile method to calculate the first and third quartile of our data.

iqr = zeek_df['duration'].quantile(q = 0.75) - zeek_df['duration'].quantile(q = 0.25)
iqr
0.01026296615600586

The last variability metric that we would like to share with you is Standard Deviation. This value gives us an idea of, on average, how far are our values from the mean. We can use the std method to calculate the standard deviation of our data.

std_dev = zeek_df['duration'].std()
std_dev
65.37632416317761

Correlation

To graphically understand the relationship between 2 numerical variables, we can use a scatter plot. we can use the plot.scatter method to create a scatter plot.

# Filtering orig_bytes < 10000
zeek_df[zeek_df['orig_bytes'] < 10000].plot.scatter(x = 'orig_bytes', y = 'resp_bytes')
<AxesSubplot:xlabel='orig_bytes', ylabel='resp_bytes'>
../../_images/day1-3-Data-Analysis-with-Pandas Intro_217_1.png

Pandas also provies us with the corr method to calculate correlation coeficients (Default method: Pearson coefficient - Linear Relation). We can correlate 2 or more numerical variables.

numerical_data_updated.corr()
duration orig_bytes orig_pkts resp_bytes resp_pkts
duration 1.000000 0.088359 0.935223 0.903869 0.930104
orig_bytes 0.088359 1.000000 0.148155 0.075493 0.099968
orig_pkts 0.935223 0.148155 1.000000 0.980667 0.997377
resp_bytes 0.903869 0.075493 0.980667 1.000000 0.988010
resp_pkts 0.930104 0.099968 0.997377 0.988010 1.000000

End of Session

Break: 5 Minutes