Convert a column of dates of a pandas dataframe to the last date of the respective year-CodePudding

I have a pandas dataframe, lets call it df, looking like this:

    Acount              Type   Id               Date         Value          Per
0   Exp                 P      IQ               2016-03-31  -23421.170324   3M
1   Exp                 P      IQ               2017-03-31  -44803.599908   3M
2   Exp                 P      IQ               2018-03-31  -29294.611346   3M
3   Exp                 P      IQ               2019-03-31  -9463.281704    3M

I need the date column to have the last day of each year, for example: "2019/12/31" and df to look like this:

    Acount              Type   Id               Date         Value          Per
0   Exp                 P      IQ               2016-12-31  -23421.170324   3M
1   Exp                 P      IQ               2017-12-31  -44803.599908   3M
2   Exp                 P      IQ               2018-12-31  -29294.611346   3M
3   Exp                 P      IQ               2019-12-31  -9463.281704    3M

Do you guys know what do I have to do?

CodePudding user response：

Use pd.offsets.YearEnd:

df['Date'] = pd.to_datetime(df['Date'])   pd.offsets.YearEnd(0)

Output:

  Acount Type  Id       Date         Value Per
0    Exp    P  IQ 2016-12-31 -23421.170324  3M
1    Exp    P  IQ 2017-12-31 -44803.599908  3M
2    Exp    P  IQ 2018-12-31 -29294.611346  3M
3    Exp    P  IQ 2019-12-31  -9463.281704  3M

CodePudding user response：

Example

df = pd.DataFrame(['2016-03-31', '2017-03-31', '2018-03-31'], columns=['Date'])

df

    Date
0   2016-03-31
1   2017-03-31
2   2018-03-31

Code

df['Date'].astype('Period[Y]').dt.to_timestamp(freq='Y')

result:

0   2016-12-31
1   2017-12-31
2   2018-12-31
Name: Date, dtype: datetime64[ns]