Tag: operations

I am trying to understand how to make code works faster by converting for-loops to vectorized operations. As an example I use the following dataframe:

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

Data is presented in json format data.json:

(
 {
   "Date": "9/20/2020 8:50",
   "UE": 1,
   "DL": 154
 },
 {
   "Date": "9/20/2020 8:50",
   "UE": 2,
   "DL": 123
 },
 {
   "Date": "9/20/2020 8:50",
   "UE": 3,
   "DL": 132
 },
 {
   "Date": "9/20/2020 8:57",
   "UE": 1,
   "DL": 141
 },
 {
   "Date": "9/20/2020 8:57",
   "UE": 8,
   "DL": 151
 },
 {
   "Date": "9/20/2020 8:57",
   "UE": 2,
   "DL": 155
 },
 {
   "Date": "9/20/2020 9:12",
   "UE": 1,
   "DL": 151
 },
 {
   "Date": "9/20/2020 9:12",
   "UE": 5,
   "DL": 154
 },
 {
   "Date": "9/20/2020 9:12",
   "UE": 3,
   "DL": 144
 },
 {
   "Date": "9/20/2020 9:20",
   "UE": 1,
   "DL": 134
 },
 {
   "Date": "9/20/2020 9:20",
   "UE": 4,
   "DL": 155
 },
 {
   "Date": "9/20/2020 9:20",
   "UE": 3,
   "DL": 153
 }
)

I upload data as the following:

def upload_data(file):
    df = pd.read_json(file)
    df('Date') = pd.to_datetime(df('Date'), format="%Y-%d-%m %H:%M:%S") 
    df('CQI') = 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('CQI') = (df('Date') != df('Date').shift()).cumsum()

Finally I create dataframe with the results:

def create_df_with_reslts(df):
    df_results = pd.DataFrame().reindex_like(df)
    df_results('Results') = 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 date format
    date_format = '%Y-%m-%d %H:%M:%S'
    for index, row in df.iterrows():
        date_string = row('Date')
        try:
            df_results('Date')(index) = True
        except ValueError:
            df_results('Date')(index) = False    

    # checking that number of UEs is between 1 and 500
    df_results('UE') = np.where((df('UE') >=1) & (df('UE') <=500), True, False)
    
    # finding bordes for CQI's spans
    previous_row = df('Date').astype(str)(0)
    CQI_index = 0
    CQI_list = ()
    for index, row in df.iterrows():
        # for same CQI
        if row('Date') == previous_row:
            CQI_index += 1
            previous_row = row('Date')
        # for next CQI in table
        else:
            CQI_list.append(CQI_index)
            CQI_index = 1
            previous_row = row('Date')
    CQI_list.append(CQI_index)
    
    # checking whether or not borders are correct
    k=0
    for i in range(len(CQI_list)):
        if CQI_list(i) == 6 or CQI_list(i) == 8:
            for j in range(CQI_list(i)):
                df_results('CQI')(j+k) = True     
        else:
            for j in range(CQI_list(i)):
                df_results('CQI')(j+k) = False   
        j=CQI_list(i)
        k=k+j
    
    # Values of DL corresponds to uniform distribution with epsilon 10%
    list_of_columns = ('DL')   
    for n in range(len(list_of_columns)):
        # find the highest number for each CQI (CQI_MAX)
        k=0
        max_list = ()  
        for i in range(len(CQI_list)):
            X = df(list_of_columns(n))(k:k+CQI_list(i)+1)
            max_list.append(X(X == X.max()).iloc(0))
            k=k+CQI_list(i)
        # check that each value in (max_list*10/100, max_list)
        k=0
        for i in range(len(CQI_list)):
            for j in range(CQI_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=CQI_list(i)
            k=k+j
            
    # final results for each column
    df_results('Results') = 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.
So my question: is it possible to speedup and vectorize other parts of the code?