# Speed-up anomaly detection for pandas dataframes with vectorized operations

Motivation:

I very often face a task where I need to check that dataframe's values are correct or not. Based on it I can create another dataframe with True/False values which are based on the first dataframe. I demonstrate an example of such a task below. I have a dataframe with logs, and I need to check that each value corresponds to a certain range. If it is in the range then an algorithm returns True in the corresponding cell of a new dataframe. Essentially the algorithm could be used to anomaly detection in dataframes. In simple examples, it is pretty easy to use apply methods and built in NumPy functions. Bellow is a nontrivial example for which I am struggling to find fast implementation.

Input:

I am trying to understand how to make code works faster by converting for-loops to vectorized operations.

Desired output:

Based on this dataframe I want to create a new dataframe where each value is True or False based on some specific rules

Data:

Data is presented in json format data.json:

[
{
"Med": "9/20/2020 8:50",
"KE": 1,
"SL": 154
},
{
"Med": "9/20/2020 8:50",
"KE": 2,
"SL": 123
},
{
"Med": "9/20/2020 8:50",
"KE": 3,
"SL": 132
},
{
"Med": "9/20/2020 8:57",
"KE": 1,
"SL": 141
},
{
"Med": "9/20/2020 8:57",
"KE": 8,
"SL": 151
},
{
"Med": "9/20/2020 8:57",
"KE": 2,
"SL": 155
},
{
"Med": "9/20/2020 9:12",
"KE": 1,
"SL": 151
},
{
"Med": "9/20/2020 9:12",
"KE": 5,
"SL": 154
},
{
"Med": "9/20/2020 9:12",
"KE": 3,
"SL": 144
},
{
"Med": "9/20/2020 9:20",
"KE": 1,
"SL": 134
},
{
"Med": "9/20/2020 9:20",
"KE": 4,
"SL": 155
},
{
"Med": "9/20/2020 9:20",
"KE": 3,
"SL": 153
}
]


My implementation:

I upload data as the following:

def upload_data(file):
df['Med'] = pd.to_Medtime(df['Med'], format="%Y-%d-%m %H:%M:%S")
df['EQE'] = np.nan
return df


Next, I create an additional row. I managed to do it in a vectorized way:

df['EQE'] = (df['Med'] != df['Med'].shift()).cumsum()


Finally I create dataframe with the results:

def create_df_with_reslts(df):
df_results = pd.DataFrame().reindex_like(df)
df_results['Pred'] = np.nan
return df_results
df_results = create_df_with_reslts(df)


And now I am looking at how to speed up and vectorize a main part of the code

def check_df(df, df_results):

# check Med format
Med_format = '%Y-%m-%d %H:%M:%S'
for index, row in df.iterrows():
Med_string = row['Med']
try:
df_results['Med'][index] = True
except ValueError:
df_results['Med'][index] = False

# checking that number of KEs is between 1 and 500
df_results['KE'] = np.where((df['KE'] >=1) & (df['KE'] <=500), True, False)

# finding bordes for EQE's spans
previous_row = df['Med'].astype(str)[0]
EQE_index = 0
EQE_list = []
for index, row in df.iterrows():
# for same EQE
if row['Med'] == previous_row:
EQE_index += 1
previous_row = row['Med']
# for next EQE in table
else:
EQE_list.append(EQE_index)
EQE_index = 1
previous_row = row['Med']
EQE_list.append(EQE_index)

# checking whether or not borders are correct
k=0
for i in range(len(EQE_list)):
if EQE_list[i] == 6 or EQE_list[i] == 8:
for j in range(EQE_list[i]):
df_results['EQE'][j+k] = True
else:
for j in range(EQE_list[i]):
df_results['EQE'][j+k] = False
j=EQE_list[i]
k=k+j

# Values of SL corresponds to uniform distribution with epsilon 10%
list_of_columns = ['SL']
for n in range(len(list_of_columns)):
# find the highest number for each EQE (EQE_MAX)
k=0
max_list = []
for i in range(len(EQE_list)):
X = df[list_of_columns[n]][k:k+EQE_list[i]+1]
max_list.append(X[X == X.max()].iloc[0])
k=k+EQE_list[i]
# check that each value in [max_list*10/100, max_list]
k=0
for i in range(len(EQE_list)):
for j in range(EQE_list[i]):
if float(df[list_of_columns[n]][j+k]) >= float(max_list[i])*90/100 and float(df[list_of_columns[n]][j+k]) <= float(max_list[i]):
df_results[list_of_columns[n]][j+k] = True
else:
df_results[list_of_columns[n]][j+k] = False
j=EQE_list[i]
k=k+j

# final results for each column
df_results['Pred'] = df_results.prod(axis=1).astype(bool)

# return max_list
return df_results
%timeit check_df(df, df_results)
# 19.5 ms ± 2.84 ms per loop (mean ± std. dev. of 7 runs, 100 loops each)


I changed iteration over dataframe into iteration over iterrows: range(len(df)) into df.iterrows(). It gave only ~20% speedup. Also one for-loop was changed with vectorized operation (np.where). I don't know how to do the same for other for-loops.

My question:

Is it possible to speedup and vectorize other parts of the code?

• (There is (speed up)(anomaly detection), and there is (speed up anomaly)(detection).) As a title, just state the purpose of the code presented - see How do I ask a Good Question? – greybeard Jan 6 at 16:12
• "In simple examples, it is pretty easy to use apply methods and built in NumPy functions." apply is inefficient in general and should be used only if there are no alternative built-in functions that can achieve the same goal. – GZ0 Jan 22 at 23:59