Stack Exchange
Vectorized way for nested for loop situation with dataframes in Python
I'm a little new to Python, so I am struggling to reduce the time complexity of my problem.
I have created a data frame as follows:
  volume    SAs  HOEP     Date      Time         Diff_200    Diff_210   Diff_220    Diff_230    Diff_240    ... Diff_340    Diff_350    Diff_360    Diff_370    Diff_380    Diff_390    Diff_400    Difference_410  Difference_420  Difference_430
451.586788  12  0.00193 2022-06-20  23:00:00    251.586788  241.586788  231.586788  221.586788  211.586788  ... 111.586788  101.586788  91.586788   81.586788   71.586788   61.586788   51.586788   41.586788   31.586788   21.586788
398.799094  12  0.00481 2022-06-20  22:00:00    198.799094  188.799094  178.799094  168.799094  158.799094  ... 58.799094   48.799094   38.799094   28.799094   18.799094   8.799094    -1.200906   -11.200906  -21.200906  -31.200906
349.545342  12  0.02966 2022-06-20  21:00:00    149.545342  139.545342  129.545342  119.545342  109.545342  ... 9.545342    -0.454658   -10.454658  -20.454658  -30.454658  -40.454658  -50.454658  -60.454658  -70.454658  -80.454658
309.973705  12  0.04074 2022-06-20  20:00:00    109.973705  99.973705   89.973705   79.973705   69.973705   ... -30.026295  -40.026295  -50.026295  -60.026295  -70.026295  -80.026295  -90.026295  -100.026295 -110.026295 -120.026295
288.761856  12  0.06420 2022-06-20  19:00:00    88.761856   78.761856   68.761856   58.761856   761856  ... -51.238144  -61.238144  -71.238144  -81.238144  -91.238144  -101.238144 -111.238144 -121.238144 -131.238144 -141.238144
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
620.289423  12  0.00000 2021-06-21  04:00:00    420.289423  410.289423  400.289423  390.289423  380.289423  ... 280.289423  270.289423  260.289423  250.289423  240.289423  230.289423  220.289423  210.289423  200.289423  190.289423
614.984896  12  0.00000 2021-06-21  03:00:00    414.984896  404.984896  394.984896  384.984896  374.984896  ... 274.984896  264.984896  254.984896  244.984896  234.984896  224.984896  214.984896  204.984896  194.984896  184.984896
543.722130  12  0.00442 2021-06-21  02:00:00    343.722130  333.722130  323.722130  313.722130  303.722130  ... 203.722130  193.722130  183.722130  173.722130  163.722130  153.722130  143.722130  133.722130  123.722130  113.722130
515.074752  12  0.00731 2021-06-21  01:00:00    315.074752  305.074752  295.074752  285.074752  275.074752  ... 175.074752  165.074752  155.074752  145.074752  135.074752  125.074752  115.074752  105.074752  95.074752   85.074752
470.910849  12  0.02276 2021-06-21  00:00:00    270.910849  260.910849  250.910849  240.910849  230.910849  ... 130.910849  120.910849  110.910849  100.910849  90.910849   80.910849   70.910849   60.910849   50.910849   40.910849

where, the Diff_number column is basically the difference between the volume column and the number for the particular row and the particular column. Ex for the first row: Diff_200 = 451.586 - 200 = 251.586 and so on.
My aim is to create a new list and insert these columns alongside the diff columns. The new list will run from 0 to (column sum/hours)(where hours = 5824 when I am running the iteration for 7 days a week and hours = 4160 when I am running the iteration for 5 days a week) for every single column. The values will be also be added on the basis of a time constraint, that is they will only be added if df['Time'] is within 7 am to 11 pm.
First I will generate the columns for 7 days a week, for the mentioned time frame and for hours = 5824
So far I have written this code.
#Code for 7 days and 16 hours(7 am to 11 pm)
allowed_time = ['7:00:00','8:00:00','9:00:00','10:00:00','11:00:00','12:00:00','13:00:00','14:00:00','15:00:00','16:00:00','17:00:00','18:00:00','19:00:00','20:00:00','21:00:00','22:00:00','23:00:00']
hours_fixed = 5824
col_names = ['Diff_'+str(y) for y in reqd_list]
for col in col_names:
    colsum = 0
    max_fixed = 0
    new_list = []
    for index in df_current.index:
        colsum = colsum + df_current[col][index]
    max_fixed = colsum/hours_fixed
    new_list = np.arange(0, round(max_fixed), 10).tolist()
    column_names = [col+"_"+str(i) for i in new_list]
    for j in range(len(new_list)):
        for idx in df_current.index:
            if any(x in df_current['Time'][idx] for x in allowed_time):
                df_current[column_names[j]] = df_current[col] - new_list[j]
  

I want to repeat this and generate columns for 5 days(only weekdays) of a week from the columns generated from 7 days a week, for the mentioned time frame and for hours = 4160.
So far I am using this.
#Code for 5 days and 16 hours(7 am to 11 pm): Basically same as 7 days with only the constraint in weekday()
df1 = df_current.iloc[:, 29:]
namesofcolumns = df1.columns.values.tolist()
hours_peak = 4160
for colu in namesofcolumns:
    colsum = 0
    max_peak = 0
    new_list = []
    Column_Names = []
    for ix in df_current.index:
        colsum = colsum + df_current[colu][ix]
    max_peak = colsum/hours_peak
    new_list = np.arange(0, round(max_peak), 10).tolist()
    print(new_list)
    Column_Names = [colu+"_"+str(x) for x in new_list]
    for y in range(len(new_list)):
        for idex in df_current.index:
            if ((any(z in df_current['Time'][idex] for z in allowed_time)) & (df_current['Date'][idex].weekday() <=4)):
                df_current[Column_Names[y]] = df_current[colu] - new_list[y]

The code for 5 days and 16 hours is taking ages to execute, so I was wondering if I can reduce the time complexity severely by using functions for both 7 days and 5 days and vectorizing the code, but since I am relatively new to Python, I don't know how.

Are you sure your code is correct? For example, '7:00:00' in '17:00:00' returns True so z in df_current['Time'][idex] certainly do not want you want. Moreover, df_current[column_names[j]] = df_current[col] - new_list[j] is independent of idx so the same expression is executed whatever the loop iteration which is surprising and certainly unwanted. There are Diff_xxx and Difference_xxx columns: is this intended? Finally, I get some negative values for max_peak which is certainly not intended too. Can you check the code and provide a minimal reproducible example?

Hey Jerome, you’re right, the code isn’t correct as it isn’t producing the right results. Max peak can have negative values no problem as it simply means that there is no column generated from as the list that I am creating in multiples of 10, is an empty list. If max peak is anything less than 10, no columns will be generated which is what I want, technically. I fixed the issue pointed out with difference but I cannot get the code to work the way I want. Can you please help me to code it right and reduce the time complexity of the same?
