0
\$\begingroup\$

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 = pd.read_json(file)
    df['Med'] = pd.to_Medtime(df['Med'], format="%Y-%d-%m %H:%M:%S") 
    df['EQE'] = np.nan
    return df
df = upload_data('data.json')

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?

\$\endgroup\$
2
  • 2
    \$\begingroup\$ (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? \$\endgroup\$ – greybeard Jan 6 at 16:12
  • \$\begingroup\$ "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. \$\endgroup\$ – GZ0 Jan 22 at 23:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.