loop over date range and appending new values to a new data frame

Question

I wish to loop each row of the date frame below over each date of date rage below, check the following condition and return the current date of date range in a new data frame with all columns we have in the data frame. As the new expected data frame will contain all those columns:

Condition in SQL:

WHERE s.OpenDate < @currentDate
AND ISNULL(s.CloseDate, '2050-01-01') > @currentDate
AND s.CloseType IN (0, 1, 3, 7, 8);

Data Range

from datetime import datetime, timedelta
# Define start and end dates
start = datetime(2024, 6, 1)
end = datetime(2024, 12, 10)
current_date = start
# List to store valid dates
valid_dates = []

while current_date <= end:
    valid_dates.append(current_date.strftime('%Y-%m-%d'))
    current_date += timedelta(days=1)

Data Frame Sample (only first 4 rows)

User_Id	OpenDate	CloseDate	CloseType
12	2022-02-14	2022-02-18	3
14	2021-12-26	2021-12-26	6
34	2022-05-22	2022-06-12	3
93	2021-12-06	2021-12-06	6

your request is poorly stated - ambiguous, lacks details ( where is User_Id used in any context ) , suggest you read stackoverflow.com/help/minimal-reproducible-example and edit your post — ticktalk
– ticktalk, Commented Dec 12, 2024 at 14:19

Sarthak Bhan · Accepted Answer · 2024-12-12 02:40:46Z

I am not exactly sure what exactly you want, as far as I understand, I have written a script. Let me know if this is what you want.

import pandas as pd
from datetime import datetime, timedelta

# Sample DataFrame
data = {
    'User_Id': [12, 14, 34, 93],
    'OpenDate': ['2024-07-14', '2021-12-26', '2022-05-22', '2021-12-06'],
    'CloseDate': ['2024-08-18', '2021-12-26', '2022-06-12', '2021-12-06'],
    'CloseType': [3, 6, 3, 6]
}

df = pd.DataFrame(data)
df['OpenDate'] = pd.to_datetime(df['OpenDate'])
df['CloseDate'] = pd.to_datetime(df['CloseDate'], errors='coerce')  # Handle NaT for missing dates

# Define start and end dates
start = datetime(2024, 6, 1)
end = datetime(2024, 12, 10)

# Generate the date range
date_range = pd.date_range(start=start, end=end)

# List to store the results
results = []

# Loop through each row and each date
for index, row in df.iterrows():
    for current_date in date_range:
        if row['OpenDate'] < current_date and (pd.isnull(row['CloseDate']) or row['CloseDate'] > current_date) and row['CloseType'] in [0, 1, 3, 7, 8]:
            result_row = {
                'CurrentDate': current_date,
                'User_Id': row['User_Id'],
                'OpenDate': row['OpenDate'],
                'CloseDate': row['CloseDate'],
                'CloseType': row['CloseType']
            }
            results.append(result_row)

# Create a new DataFrame with the results
result_df = pd.DataFrame(results)

# Display the result DataFrame
print(result_df)

It will give an output like this:

   CurrentDate  User_Id   OpenDate  CloseDate  CloseType
0   2024-07-15       12 2024-07-14 2024-08-18          3
1   2024-07-16       12 2024-07-14 2024-08-18          3
2   2024-07-17       12 2024-07-14 2024-08-18          3
3   2024-07-18       12 2024-07-14 2024-08-18          3
4   2024-07-19       12 2024-07-14 2024-08-18          3
5   2024-07-20       12 2024-07-14 2024-08-18          3
6   2024-07-21       12 2024-07-14 2024-08-18          3
7   2024-07-22       12 2024-07-14 2024-08-18          3
8   2024-07-23       12 2024-07-14 2024-08-18          3
9   2024-07-24       12 2024-07-14 2024-08-18          3
10  2024-07-25       12 2024-07-14 2024-08-18          3
11  2024-07-26       12 2024-07-14 2024-08-18          3
12  2024-07-27       12 2024-07-14 2024-08-18          3
13  2024-07-28       12 2024-07-14 2024-08-18          3
14  2024-07-29       12 2024-07-14 2024-08-18          3
15  2024-07-30       12 2024-07-14 2024-08-18          3
16  2024-07-31       12 2024-07-14 2024-08-18          3
17  2024-08-01       12 2024-07-14 2024-08-18          3
18  2024-08-02       12 2024-07-14 2024-08-18          3
19  2024-08-03       12 2024-07-14 2024-08-18          3
20  2024-08-04       12 2024-07-14 2024-08-18          3
21  2024-08-05       12 2024-07-14 2024-08-18          3
22  2024-08-06       12 2024-07-14 2024-08-18          3
23  2024-08-07       12 2024-07-14 2024-08-18          3
24  2024-08-08       12 2024-07-14 2024-08-18          3
25  2024-08-09       12 2024-07-14 2024-08-18          3
26  2024-08-10       12 2024-07-14 2024-08-18          3
27  2024-08-11       12 2024-07-14 2024-08-18          3
28  2024-08-12       12 2024-07-14 2024-08-18          3
29  2024-08-13       12 2024-07-14 2024-08-18          3
30  2024-08-14       12 2024-07-14 2024-08-18          3
31  2024-08-15       12 2024-07-14 2024-08-18          3
32  2024-08-16       12 2024-07-14 2024-08-18          3
33  2024-08-17       12 2024-07-14 2024-08-18          3

thank you. It seems the logic is correct however I've got other error when running the code above: TypeError: NDFrame.replace() got an unexpected keyword argument 'tzinfo'

Collectives™ on Stack Overflow

loop over date range and appending new values to a new data frame

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related