most efficient method to use pandas pivot table over large file

Question

I am iterating over many exported security event logs pulled from a windows host, example dataframe like below:

"MachineName","EventID","EntryType","Source","TimeGenerated","TimeWritten","UserName","Message" "mycompname","5156","SuccessAudit","Microsoft-Windows-Security-Auditing","4/26/2017 10:47:41 AM","4/26/2017 10:47:41 AM",,"The Windows Filtering Platform has permitted a connection.    Application Information:   Process ID:  4   Application Name: System    Network Information:   Direction:  %%14592   Source Address:  192.168.10.255   Source Port:  137   Destination Address: 192.168.10.238   Destination Port:  137   Protocol:  17    Filter Information:   Filter Run-Time ID: 83695   Layer Name:  %%14610   Layer Run-Time ID: 44" "mycompname","4688","SuccessAudit","Microsoft-Windows-Security-Auditing","4/26/2014 10:47:03 AM","4/26/2014 10:47:03 AM",,"A new process has been created.    Subject:   Security ID:  S-1-5-18   Account Name:  mycompname$   Account Domain:  mydomain   Logon ID:  0x3e7    Process Information:   New Process ID:  0x1b04   New Process Name: C:\Windows\SysWOW64\Macromed\Flash\FlashPlayerUpdateService.exe   Token Elevation Type: %%1936   Creator Process ID: 0x300   Process Command Line: C:\windows\SysWOW64\Macromed\Flash\FlashPlayerUpdateService.exe    Token Elevation Type indicates the type of token that was assigned to the new process in accordance with User Account Control policy.    Type 1 is a full token with no privileges removed or groups disabled.  A full token is only used if User Account Control is disabled or if the user is the built-in Administrator account or a service account.    Type 2 is an elevated token with no privileges removed or groups disabled.  An elevated token is used when User Account Control is enabled and the user chooses to start the program using Run as administrator.  An elevated token is also used when an application is configured to always require administrative privilege or to always require maximum privilege, and the user is a member of the Administrators group.    Type 3 is a limited token with administrative privileges removed and administrative groups disabled.  The limited token is used when User Account Control is enabled, the application does not require administrative privilege, and the user does not choose to start the program using Run as administrator." "mycompname","4673","SuccessAudit","Microsoft-Windows-Security-Auditing","4/26/2014 10:47:00 AM","4/26/2014 10:47:00 AM",,"A privileged service was called.    Subject:   Security ID:  S-1-5-18   Account Name:  mycompname$   Account Domain:  mydomain   Logon ID:  0x3e7    Service:   Server: NT Local Security Authority / Authentication Service   Service Name: LsaRegisterLogonProcess()    Process:   Process ID: 0x308   Process Name: C:\Windows\System32\lsass.exe    Service Request Information:   Privileges:  SeTcbPrivilege" 

I am converting it to extract key:value pairs out of the "Message" Column and convert the keys to columns like below

def myfunc(folder):     file = ''.join(glob2.glob(folders + "\\*security*"))     df = pd.read_csv(file)      df.message = df.message.replace(["[ ]{6}", "[ ]{3}"],[","," ||"], regex=True)     message_results = df.message.str.extractall(r"\|([^\|]*?):(.*?)\|").reset_index()     message_results.columns = ["org_index", "match", "keys", "vals"]     # PART THAT TAKES THE LONGEST     p = pd.pivot_table(message_results, values="vals", columns=['keys'], index=["org_index"], aggfunc=np.sum)     df = df.join(p).fillna("NONE") 

Output of above function:

MachineName,EventID,EntryType,Source,TimeGenerated,TimeWritten,UserName,Message, Application Information, Filter Information, Network Information, Process, Process Information, Service, Service Request Information, Subject,Account Domain,Account Name,Application Name,Creator Process ID,Destination Address,Destination Port,Direction,Filter Run-Time ID,Layer Name,Logon ID,New Process ID,New Process Name,Process Command Line,Process ID,Process Name,Protocol,Security ID,Server,Service Name,Source Address,Source Port,Token Elevation Type mycompname,5156,SuccessAudit,Microsoft-Windows-Security-Auditing,4/26/2017 10:47:41 AM,4/26/2017 10:47:41 AM,NONE,The Windows Filtering Platform has permitted a connection. || Application Information: ||Process ID:  4 ||Application Name: System || Network Information: ||Direction:  %%14592 ||Source Address:  192.168.10.255 ||Source Port:  137 ||Destination Address: 192.168.10.238 ||Destination Port:  137 ||Protocol:  17 || Filter Information: ||Filter Run-Time ID: 83695 ||Layer Name:  %%14610 ||Layer Run-Time ID: 44, , , ,NONE,NONE,NONE,NONE,NONE,NONE,NONE, System ,NONE, 192.168.10.238 ,  137 ,  %%14592 , 83695 ,  %%14610 ,NONE,NONE,NONE,NONE,  4 ,NONE,  17 ,NONE,NONE,NONE,  192.168.10.255 ,  137 ,NONE mycompname,4688,SuccessAudit,Microsoft-Windows-Security-Auditing,4/26/2017 10:47:03 AM,4/26/2017 10:47:03 AM,NONE,"A new process has been created. || Subject: ||Security ID:  S-1-5-18 ||Account Name:  mycompname$ ||Account Domain:  mydomain ||Logon ID:  0x3e7 || Process Information: ||New Process ID:  0x1b04 ||New Process Name: C:\Windows\SysWOW64\Macromed\Flash\FlashPlayerUpdateService.exe ||Token Elevation Type: %%1936 ||Creator Process ID: 0x300 ||Process Command Line: C:\windows\SysWOW64\Macromed\Flash\FlashPlayerUpdateService.exe || Token Elevation Type indicates the type of token that was assigned to the new process in accordance with User Account Control policy. || Type 1 is a full token with no privileges removed or groups disabled.  A full token is only used if User Account Control is disabled or if the user is the built-in Administrator account or a service account. || Type 2 is an elevated token with no privileges removed or groups disabled.  An elevated token is used when User Account Control is enabled and the user chooses to start the program using Run as administrator.  An elevated token is also used when an application is configured to always require administrative privilege or to always require maximum privilege, and the user is a member of the Administrators group. || Type 3 is a limited token with administrative privileges removed and administrative groups disabled.  The limited token is used when User Account Control is enabled, the application does not require administrative privilege, and the user does not choose to start the program using Run as administrator.",NONE,NONE,NONE,NONE, ,NONE,NONE, ,  mydomain ,  MEADWK4216DC190$ ,NONE, 0x300 ,NONE,NONE,NONE,NONE,NONE,  0x3e7 ,  0x1b04 , C:\Windows\SysWOW64\Macromed\Flash\FlashPlayerUpdateService.exe , C:\windows\SysWOW64\Macromed\Flash\FlashPlayerUpdateService.exe ,NONE,NONE,NONE,  S-1-5-18 ,NONE,NONE,NONE,NONE, %%1936  mycompname,4673,SuccessAudit,Microsoft-Windows-Security-Auditing,4/26/2017 10:47:00 AM,4/26/2017 10:47:00 AM,NONE,A privileged service was called. || Subject: ||Security ID:  S-1-5-18 ||Account Name:  mycompname$ ||Account Domain:  mydomain ||Logon ID:  0x3e7 || Service: ||Server: NT Local Security Authority / Authentication Service ||Service Name: LsaRegisterLogonProcess() || Process: ||Process ID: 0x308 ||Process Name: C:\Windows\System32\lsass.exe || Service Request Information: ||Privileges:  SeTcbPrivilege,NONE,NONE,NONE, ,NONE, , , ,  mydomain ,  mycompname$ ,NONE,NONE,NONE,NONE,NONE,NONE,NONE,  0x3e7 ,NONE,NONE,NONE, 0x308 , C:\Windows\System32\lsass.exe ,NONE,  S-1-5-18 , NT Local Security Authority / Authentication Service , LsaRegisterLogonProcess() ,NONE,NONE,NONE 

The functionality of the program works but is incredibly slow on the p = pivot_table portion of code on larger sets of data (roughly 150000 lines).

I am currently using concurrent.futures.ThreadPoolExecutor(maxworkers=1000) iterating over each reading of the file like below:

with concurrent.futures.ThreadPoolExecutor(max_workers=1000) as pool:     for folder in path:         if os.path.isdir(folder):             try:                 print(folder)                 pool.submit(myfunc(folder), 1000)             except:                 print('error')  

How can I speed up my the pivot table portion of my function?

Also, is there any method to speed up the pivot_table call from pandas?

Any assistance with this would be greatly appreciated. Thank you.

Answer 1

Syntax error

Your code has a number of syntactical errors

pool.submit(myfunc(folder), 1000) 

The pool.submit method takes a function as a first argument.

From what I see your function myfunc does not return anything, and definitely not a function.

Even so, from my understanding, you are trying to launch 1000 workers who all read the same folder and then creates dataframes.

Parallelization problem

In any threading scenario, the number of workers should be close to the number of cores available on the machine you are running. This is common sense, I will not quote anything.

Spawning 1000 workers is a lot of overhead and is a probable source of your slow function. Also all your workers seem to be doing the exact same thing, which of course means you do the same work 1000 times.

My guess at the actual pivot problem

So from what you write, code aside, I understand that you are trying to create a huge key-space that allows you to slice into any metric and drill down into the dataset.

You are doing this using a single column from what I see. You should be splitting these out into separate columns. As hinted by commenters, pandas has categorical columns that could be used, but even without them, the index for the key-space will be much smaller if the key parts are in separate columns. Your current dataset most likely has a separate key for almost each line, thus not aggregating more than a a few lines together, leaving the pivot table the same size as the original dataset.

TLDR;

Split your key column into multiple columns, preferably categorical ones.