How to create time series for every end date of the year with its corresponding value?-CodePudding

I have a dataframe that looks like in the following way where the 'Date' column has already datetime64 dtype:

           Date     Income_Company_A
0       1990-02-01       2185600.0  
1       1990-02-02       3103200.0
........................................
5467    2011-10-10       29555500.0
5468    2011-10-11       54708100.0

How can I get the values for Income_Company_A where the date has to be an ending date for each year, i.e., it has to be 31 Dec for every year starting from 1990 till 2011? Also, if the value is Null/NaN for the ending date for each year, then how can I fill it up with the value that can be found prior to that date from the dataframe?

The first output with NaN values should look like this:

1990-12-31     1593200.0
1991-12-31     4802000.0
1992-12-31     3302000.0
1993-12-31     5765200.0
1994-12-31           NaN

Then by replacing the NaN value for the date 1994-12-31 by the value that can be found for a prior date, for example, 1994-12-29 7865200.0, the final output should look like this:

1990-12-31     1593200.0
1991-12-31     4802000.0
1992-12-31     3302000.0
1993-12-31     5765200.0
1994-12-31     7865200.0

CodePudding user response：

Assuming Date column is already in datetime data type:

df.loc[(df['Date'].dt.month == 12) & (df['Date'].dt.day == 31)].ffill()

in that case , try this :

df.loc[df.groupby(df['Date'].dt.year)['Date'].idxmax()].ffill()

CodePudding user response：

Use resample and take the last valid value of year:

out = df.assign(Date=df['Date'].astype('datetime64')).resample('Y', on='Date').last()

You can omit .assign(...) if your Date column has already datetime64 dtype.