Fill values within dates according other DataFrame

user_15806782 0
Viewed 58

I'm trying to fill this DataFrame (df1) (I can start it with NaN or zero values):

        27/05/2021  28/05/2021  29/05/2021  30/05/2021  31/05/2021  01/06/2021  02/06/2021 ...
Name1   Nan         Nan         Nan         Nan         Nan         Nan         Nan
Name2   Nan         Nan         Nan         Nan         Nan         Nan         Nan
Name3   Nan         Nan         Nan         Nan         Nan         Nan         Nan
Name4   Nan         Nan         Nan         Nan         Nan         Nan         Nan

Acording information in this DataFrame (df2):

          Start1      End1        Dedication1 (h) Start2      End2        Dedication2 (h) 
Name1     24/05/2021  31/05/2021  8               02/06/2021  10/07/2021  3
Name2     29/05/2021  31/05/2021  5               Nan         Nan         Nan
Name3     27/05/2021  01/06/2021  3               Nan         Nan         Nan
Name4     29/05/2021  07/08/2021  8               10/10/2021  10/12/2021  2

To get something like this (df3):

        27/05/2021  28/05/2021  29/05/2021  30/05/2021  31/05/2021  01/06/2021  02/06/2021 ...
Name1   8           8           8           8           8           0           3
Name2   0           0           5           5           5           0           0
Name3   3           3           3           3           3           3           0
Name4   0           0           8           8           8           8           8

This is a schedule with working hours every day for some months. Both DataFrames will have same index and rows number.

According dates in df2, I need to fill df1 values within start day and end day, with dedication hours in that period.

I have tried loc including all rows, and lambda function to select columns according date, but I dont get fill values within dates. Perhaps I need several steps.

Thanks.

pandas lambda python
1 Answers

You could try this:

from datetime import datetime
import pandas as pd

# Setup
limits = [("Start1", "End1", "Dedication1"), ("Start2", "End2", "Dedication2")]
df3 = df1.copy()

# Deal with NaN values
df3.fillna(0, inplace=True)
df2["Start2"].fillna("31/12/2099", inplace=True)
df2["End2"].fillna("31/12/2099", inplace=True)
df2["Dedication2"].fillna(0, inplace=True)

# Iterate and fill df3
for i, row in df1.iterrows():
    for col in df1.columns:
        for start, end, dedication in limits:
            mask = (
                datetime.strptime(df2.loc[i, start], "%d/%m/%Y")
                <= datetime.strptime(col, "%d/%m/%Y")
                <= datetime.strptime(df2.loc[i, end], "%d/%m/%Y")
            )
            if mask:
                df3.loc[i, col] = df2.loc[i, dedication]

# Format df3
df3 = df3.astype("int")

print(df3)
# Outputs
       27/05/2021  28/05/2021  29/05/2021  ...  31/05/2021  01/06/2021  02/06/2021
Name1           8           8           8  ...           8           0           3
Name2           0           0           5  ...           5           0           0
Name3           3           3           3  ...           3           3           0
Name4           0           0           8  ...           8           8           8
user_112460560
Related