Pandas Equivalent to SQL YEAR(GETDATE())

Question

I'm a Pandas newbie but decent at SQL. A function I often leverage in SQL is this:

YEAR(date_format_data) = (YEAR(GETDATE())-1)

This will get me all the data from last year. Can someone please help me understand how to do the equivalent in Pandas?

Here's some example data:

Date     Number
01/01/15 1
01/02/15 2
01/01/15 3
01/01/16 2
01/01/16 1

And here's my best guess at the code:

df = df[YEAR('Date') == (YEAR(GETDATE()) -1)].agg(['sum'])

And this code would return a value of '3'.

Thank you in advance for your help, I'm having a really hard time figuring out what I'm sure is simple.

Me

You may need to first convert your `Date` column to datetime objects with `df.Date = pd.to_datetime(df.Date)`. — Abdou, Jun 26 '17 at 13:01
If you're using `pd.to_datetime` performance might be an issue if the date isn't in a standard format. [This](https://stackoverflow.com/questions/29882573/pandas-slow-date-conversion) might be useful then. — akilat90, Jun 26 '17 at 13:44

MaxU - stand with Ukraine · Accepted Answer · 2017-06-26T14:15:37.333

2

I think you can do it this way:

prev_year = pd.datetime.today().year - 1
df.loc[df['Date'].dt.year == prev_year]

PS .dt.year accessor will work only if Date column is of datetime dtype. If it's not the case you may want to convert that column to datetime dtype first:

df['Date'] = pd.to_datetime(df['Date'], errors='coerce')

edited Jun 26 '17 at 14:15

answered Jun 26 '17 at 12:59

MaxU - stand with Ukraine

205,989
36
386
419

did you mean '1 year' instead of '1 day' in your example code? – Programming_Learner_DK Jun 26 '17 at 14:10
@SDS, as far as i know `GETDATE()-1` - is (today minus one day) yesterday. Did you mean something different? – MaxU - stand with Ukraine Jun 26 '17 at 14:11
Sorry MaxU, you are right - I want to go back one year, not one day. My error, will update code above. – Programming_Learner_DK Jun 26 '17 at 14:14
@SDS, i've updated my answer correspondingly... But now i'm not sure that it's what you need. Are you sure you want to compare only the "year" part? – MaxU - stand with Ukraine Jun 26 '17 at 14:15
Yes, I'm trying to compare year over year sales transactions. I want the reports to update dynamically rather than update hard coded years. This code example will work for months and weeks so I should be good. Thank you! – Programming_Learner_DK Jun 26 '17 at 14:20

score 0 · Answer 2 · answered Jun 26 '17 at 13:03

0

For pandas, first convert your date column to timestamp by pd.to_datetime

df['Date2'] = pd.to_datetime(df['Date'])

(pd.to_datetime has a format parameter to specify your input date format) Then you have

df['Date2'].year

answered Jun 26 '17 at 13:03

jf328

6,841
10
58
82

Pandas Equivalent to SQL YEAR(GETDATE())

2 Answers2