Using pandas apply with dates and dates shifted

user_12864785 0
Viewed 279

I have a DataFrame with dates below:

               Daycount   
Date                                                                       
2020-05-01         0      
2020-06-01         0        
2020-07-01         0          
2020-08-01         0         
2020-09-01         0

I'm trying to extract the daycount from one day to the next using the below formula:

def days360(start_date, end_date, method_eu=False):
        start_day = start_date.day
    start_month = start_date.month
    start_year = start_date.year
    end_day = end_date.day
    end_month = end_date.month
    end_year = end_date.year

    if start_day == 31 or (method_eu is False and start_month == 2 and (start_day == 29 or (start_day == 28 and calendar.isleap(start_year) is False))):
        start_day = 30

    if end_day == 31:
        if method_eu is False and start_day != 30:
            end_day = 1

            if end_month == 12:
                end_year += 1
                end_month = 1
            else:
                end_month += 1
        else:
            end_day = 30

    return end_day + end_month * 30 + end_year * 360 - start_day - start_month * 30 - start_year * 360

However, I tried using the apply function as follow, yet I get the following error:

ValueError: The truth value of an array with more than one element is ambiguous. Use a.any() or a.all()

When passing just one set of values within the DataFrame it works so my formula is definitely correct. Creating another columns with the dates shifted and then applying the formula did work, but I'm looking for a cleaner way. I'm not sure about the apply function though. I'm supposed to get 30 days for all the daycount.

hypo["Daycount"] = hypo.apply(lambda x: days360(x.index,x.index.shift(-1)))

Target output should be table below:

        Date  Daycount
0 2020-05-01      30.0
1 2020-06-01      30.0
2 2020-07-01      30.0
3 2020-08-01      30.0
4 2020-09-01      30.0
pandas dataframe python
2 Answers

Use, pd.to_datetime to convert a series to datetime like series then use Series.dt to access datetime properties of series, then use Series.diff on components year, month & day to get the desired results:

df = df.reset_index()
dates = pd.to_datetime(df['Date'])
df['Daycount'] = (
    (dates.dt.year.diff() * 360 + dates.dt.month.diff() * 30 + dates.dt.day.diff()).fillna(0)
)

# print(df)
         Date  Daycount
0  2020-05-01       0.0
1  2020-06-01      30.0
2  2020-07-01      30.0
3  2020-08-01      30.0
4  2020-09-01      30.0

Consider another example with a more sophisticated dataframe:

# Given dataframe
# print(df)
            Daycount
Date                
2020-05-01         0
2020-06-03         0
2020-07-01         0
2021-07-02         0
2022-08-03         0

# Desired result
# print(df)
         Date  Daycount
0  2020-05-01       0.0
1  2020-06-03      32.0
2  2020-07-01      28.0
3  2021-07-02     361.0
4  2022-08-03     391.0
user_128331660

If you want to use .apply you need to modify your function (or add another one based on the one you already have) to operate on Series objects (not their elements). See the pandas DataFrame apply docstring "Objects passed to the function are Series objects whose index is either ..."

You can avoid using .apply and lambda by using list comprehension 

df['derived'] = [ yourfunction(a,b) for a,b in zip(df.index, df.index.shift(-1)) ]

I am sure there is another way to vectorize your function but this at least should make your code work. There was a time when lambda expression were strongly opposed by key person of python and advocated to be removed as it can always be done another way.

user_89675980
Related