I need some help to optimize my python code. The code duplicates a single row in the dataframe multiple times based on the value in that row.
I will try to describe what it does:
The dataframe has three date columns: split date, start date and end date and a status column which holds “in” or “out”. The dataframe will have more columns but the rest are not important in the algorithm.
It uses the mm/dd of the spilt date to create a list of dates between the start and end date. Say the split date is 2000-02-15, start date is 2010-03-26 and end date is 2012-12-31. The date list will be 2010-03-26, 2011-02-15, 2012-02-15 and 2012-12-31.
Based on the date list, it will duplicate the original dataframe row and replace the start date and end date with the dates in the date list. Using the same example, the original row will be duplicated three times with start date and end date of:
2010-03-26 and 2011-02-15
2011-02-15 and 2012-02-15
2012-02-15 and 2012-12-31
All newly created rows will take status of “in” except for the last row which will have the same status as the original row. With the same example:
- If the status is “in” in the original row then the three duplicated rows will have status of “in”.
- If the status is “out” in the original row then the first and second row of the newly created rows will have status of “in” and the third row will be “out”.
I have tried different ways to do the above task and come up with the most efficient code I could think of but I am still not very satisfied with its performance.
I have a huge dataframe with many columns and hundred thousands of row (which is still expanding) and it takes a while to run. Multiprocessing reduces the run time a bit but I think the core algorithm could probably be further optimized.
The task was originally performed in SAS and it was so much quicker there. I am not sure if python is slower than SAS generally or my code is not optimized so your suggestions and views will be very much appreciated.
My python code:
import pandas as pd
import numpy as np
data = {"name": ["a","b","c","d"],
"status": ["out","out","in","out"],
"split_date": ["2000-02-15","2010-12-31","1984-02-29","2000-03-31"],
"start_date": ["2010-03-26","2010-01-01","2015-06-15","2012-11-18"],
"end_date": ["2012-12-31","2015-12-31","2020-03-01","2018-07-19"]}
df = pd.DataFrame(data)
df["split_date"]=pd.DatetimeIndex(df["split_date"]).normalize()
df["start_date"]=pd.DatetimeIndex(df["start_date"]).normalize()
df["end_date"]=pd.DatetimeIndex(df["end_date"]).normalize()
def to_split(df,start_column,end_column,split_column):
"""
df = name of the dataframe whose dates are to be split
start_column = column name of start date
end_column = column name of end date
split_column = column name of split date (only mm-dd of the date will be used)
"""
dict_df = df.to_dict(orient="index")
dictionary_list=[]
for key,row_dict in dict_df.items():
first_date=row_dict[start_column]
last_date=row_dict[end_column]
if last_date < first_date:
raise ValueError("end_column_date is before start_column_date.")
try:
splitter = pd.Timestamp(first_date.year,row_dict[split_column].month,row_dict[split_column].day)
except: #leap day
splitter = pd.Timestamp(first_date.year,row_dict[split_column].month,row_dict[split_column].day-1)
#Create a date list to store intermediate split date between first_date and last_date
date_list=[]
date_list.append(first_date)
last_boolean=False
while last_boolean == False:
if splitter <= first_date and splitter <= last_date:
try:
splitter = pd.Timestamp(splitter.year+1,row_dict[split_column].month,
row_dict[split_column].day)
except: #leap day
splitter = pd.Timestamp(splitter.year+1,row_dict[split_column].month,
row_dict[split_column].day-1)
elif splitter > first_date and splitter <= last_date:
date_list.append(splitter)
try:
splitter = pd.Timestamp(splitter.year+1,row_dict[split_column].month,
row_dict[split_column].day)
except: #leap day
splitter = pd.Timestamp(splitter.year+1,row_dict[split_column].month,
row_dict[split_column].day-1)
else: #splitter > last_date
date_list.append(last_date)
last_boolean = True
#convert list to dictionary first to remove any duplicate then convert back to list again
date_list = list(dict.fromkeys(date_list))
date_list.sort()
no_segment = len(date_list)-1
status=row_dict["status"]
for i in range(no_segment):
#Assign new start and end date to each newly created row
row_dict["start_column_date"]=date_list[i]
row_dict["end_column_date"]=date_list[i+1]
#Assign new status to each newly created row
if i != no_segment-1:
row_dict["status"] = "in"
else:
row_dict["status"] = status
#dictionary_list.append(row_dict)
#results will be wrong because list contains a reference to the original dictionary
dictionary_list.append(row_dict.copy())
new_df=pd.DataFrame.from_dict(dictionary_list)
return new_df
expanded_df=to_split(df,"start_date","end_date","split_date")
expanded_df