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.

You should use message_results.iloc[:10,:].to_records() to give us some practice data to play with right before the pivot. — Gabriel A, Jan 20 '18 at 06:18
How long is the call to `pivot_table` taking? Using your data replicated to 150000 rows my laptop clocked 382 ms... that may be faster due to data replication. — Alex, Jan 21 '18 at 08:57
I don't know if this will directly answer your question or not, but here is an insanely thorough Q&A on pivot tables: https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe/47152692 — JohnE, Jan 21 '18 at 16:16
I don't have any data to play with to see if this solution works, but often pandas speeds up massively for repeated strings when they are converted to categories. The pandas tutorial is quite detailed: https://pandas.pydata.org/pandas-docs/stable/categorical.html — jpp, Jan 22 '18 at 19:51
have you tried dask dataframe? http://dask.pydata.org/en/latest/dataframe-api.html#dask.dataframe.DataFrame.pivot_table — denfromufa, Jan 24 '18 at 22:39
If you're extracting key-value pairs, how about maintaining them as key-value pairs and skipping the pivot? Output your message_result as a dict, then create a df from the dict. — Silas, Jan 26 '18 at 18:14
I can't reproduce with the code provided but applying aggregation functions to object types is almost always slower than an equivalent aggregation against numeric types, so your performance is definitely bottlenecked within that pivot on account of that. That said, it's not clear what you would expect np.sum to return with these strings, so you are better served exploring something aside from a pivot_table to munge the data — Will Ayd, Feb 16 '18 at 17:09

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

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.

most efficient method to use pandas pivot table over large file

1 Answers1

Syntax error

Parallelization problem

My guess at the actual pivot problem