Rounding pandas column to year

Question

Python and Pandas beginner here.

I want to round off a pandas dataframe column to years. Dates before the 1st of July must be rounded off to the current year and dates after and on the 1st of July must be rounded up to the next year.

For example:

2011-04-05 must be rounded to 2011

2011-08-09 must be rounded to 2012

2011-06-30 must be rounded to 2011

2011-07-01 must be rounded to 2012

What I've tried:

pd.series.dt.round(freq='Y')

Gives the error: ValueError: is a non-fixed frequency

The dataframe column has a wide variety of dates, starting from 1945 all the way up to 2021. Therefore a simple if df.date < 2011-07-01: df['Date']+ pd.offsets.YearBegin(-1) is not working.

I also tried the dt.to_period('Y') function, but then I can't give the before and after the 1st of July argument.

Any tips on how I can solve this issue?

user_10035985 · Answer

Suppose you have this dataframe:

       dates
0 2011-04-05
1 2011-08-09
2 2011-06-30
3 2011-07-01
4 1945-06-30
5 1945-07-01

Then:

# convert to datetime:
df["dates"] = pd.to_datetime(df["dates"])

df["year"] = np.where(
    (df["dates"].dt.month < 7), df["dates"].dt.year, df["dates"].dt.year + 1
)
print(df)

Prints:

       dates  year
0 2011-04-05  2011
1 2011-08-09  2012
2 2011-06-30  2011
3 2011-07-01  2012
4 1945-06-30  1945
5 1945-07-01  1946

user_10254769 · Answer

a bit of a roundabout year is to convert the date values to strings, separate them, and then classify them in a loop, like so:

for i in df["Date"]: # assuming the column's name is "Date"

   thisdate = df["Date"] # extract the ith element of Date
   thisdate = str(thisdate) # convert to string
   datesplit = thisdate.split("-") # split

   Yr = int(datesplit[0]) # get the year # convert year back to a number
   Mth = int(datesplit[1]) # get the month # convert month back to a number

   if Mth < 7: # any date before July
      rnd_Yr = Yr
   else: # any date after July 1st
      rnd_Yr = Yr + 1

Rounding pandas column to year

2 Answers