How to compare dataframe dates with the values of row and column keeping weekdays and holidays apart

Question

I have a dataframe that represents this:

I need to create another column 'Mark' and here is why it is complicated. For the value 'C' execution day is Sunday 8/11/2018. The next day would be Monday 9/11/2018. So I need to calculate the weekdays values of the previous week. For this case I need to calculate 1/11/2018,2/11/2018,3/11/2018,4/11/2018 and 5/11/2018.

However, if the next day of execution day is Friday or Saturday I would need to take the values of the previous week 'Friday' and 'Saturday'. For example, B executes on Thursday 12/11/2018'. The day after is 'Friday'. So I need to calculate the average of the previous week's Fridayand Saturday which are 6/11/2018 and 7/11/2018

Initially I did not had the Day column, which I added afterwords by using

df['Execution']=pd.to_datetime(df['Execution'])
df['Day']=df['Execution'].dt.weekday_name

And I can get to the point where it prints something if execution date matches with one of the column dates. Here is the code-

for j,row in df.iterrows():
x=str(row['Execution'])
x=x[slicing]

for i, val in enumerate (df.columns.values):
    print(df.columns[i])

    if i<l1:
        val=str(val)
        val=val[slicing]
        if x==val: #Execution date matches column date
            print('yay')

I am trying to learn python on my own and I have started by learning pandas dataframe.
However, Now I am lost and could not figure out the logic to proceed. Can anyone enlighten me the way?

In SO you should not add SOLVED to the title of your question, what you should do is post an answer and mark it as correct. :-) — eyllanesc, Oct 10 '18 at 04:35
@eyllanesc posted the answer, but couldn't mark it as correct. — T0167, Oct 10 '18 at 04:49
If you want to mark as correct your own answer you can do it in 2 days, those are the SO rules, :-) — eyllanesc, Oct 10 '18 at 04:52
I also recommend not only say: *this code works for me*, explains that part of the solution did the magic and why. — eyllanesc, Oct 10 '18 at 04:53

T0167 · Accepted Answer · 2018-10-10T05:01:01.813

Here is the code that worked for me with explanation:

for i,row in df.iterrows():
  for j, val in enumerate (range(0,l1-1)):   #l1 is the number of columns 
               #subtracted 1 to not take last column in account as I only need the dates

    if df.columns[j+1]==row['Execution']: #to match the date of column execution,with the column dates

        a=pd.to_datetime(df.columns[j+1+1])
        a=a.day_name() #to convert the date in to weekday name
#As for friday I would need previous week's friday and saturday values.
#Therefore, I subtracted 7 and 8 to get the required value. For all the other days I calculated carefully this way so that I get the days right.
        if (a=='Friday'): 
            mark=(df.iloc[i,(j+1+1-7)]+df.iloc[i,(j+1+1-6)])/2
#df.iloc(row,column) was used to get the values right
            markList.append(mark)
        elif (a=='Saturday'):
            mark=(df.iloc[i,(j+1+1-7)]+df.iloc[i,(j+1+1-8)])/2
            markList.append(mark)
        elif (a=='Sunday'):
            mark=(df.iloc[i,(j+1+1-7)]+df.iloc[i,(j+1+1-6)]+df.iloc[i,(j+1+1-5)]+MPDr.iloc[i,(j+1+1-4)]+df.iloc[i,(j+1+1-3)])/5
            markList.append(mark)
        elif (a=='Monday'):
            mark=(df.iloc[i,(j+1+1-7)]+df.iloc[i,(j+1+1-6)]+df.iloc[i,(j+1+1-5)]+df.iloc[i,(j+1+1-4)]+df.iloc[i,(j+1+1-8)])/5
            markList.append(mark)
        elif (a=='Tuesday'):
            mark=(df.iloc[i,(j+1+1-7)]+df.iloc[i,(j+1+1-6)]+df.iloc[i,(j+1+1-5)]+df.iloc[i,(j+1+1-8)]+df.iloc[i,(j+1+1-9)])/5
            markList.append(mark)
        elif (a=='Wednesday'):
            mark=(df.iloc[i,(j+1+1-7)]+df.iloc[i,(j+1+1-6)]+df.iloc[i,(j+1+1-8)]+df.iloc[i,(j+1+1-9)]+df.iloc[i,(j+1+1-10)])/5
            markList.append(mark)  
        elif (a=='Thursday'):
            mark=(df.iloc[i,(j+1+1-7)]+df.iloc[i,(j+1+1-8)]+df.iloc[i,(j+1+1-9)]+df.iloc[i,(j+1+1-10)]+df.iloc[i,(j+1+1-11)])/5
            markList.append(mark)

df['mark']=markList #To add at the end of the dataframe

How to compare dataframe dates with the values of row and column keeping weekdays and holidays apart

1 Answers1