python pandas extract year from datetime: df['year'] = df['date'].year is not working
If you're running a recent-ish version of pandas then you can use the datetime attribute dt
to access the datetime components:
In [6]:df['date'] = pd.to_datetime(df['date'])df['year'], df['month'] = df['date'].dt.year, df['date'].dt.monthdfOut[6]: date Count year month0 2010-06-30 525 2010 61 2010-07-30 136 2010 72 2010-08-31 125 2010 83 2010-09-30 84 2010 94 2010-10-29 4469 2010 10
EDIT
It looks like you're running an older version of pandas in which case the following would work:
In [18]:df['date'] = pd.to_datetime(df['date'])df['year'], df['month'] = df['date'].apply(lambda x: x.year), df['date'].apply(lambda x: x.month)dfOut[18]: date Count year month0 2010-06-30 525 2010 61 2010-07-30 136 2010 72 2010-08-31 125 2010 83 2010-09-30 84 2010 94 2010-10-29 4469 2010 10
Regarding why it didn't parse this into a datetime in read_csv
you need to pass the ordinal position of your column ([0]
) because when True
it tries to parse columns [1,2,3]
see the docs
In [20]:t="""date Count6/30/2010 5257/30/2010 1368/31/2010 1259/30/2010 8410/29/2010 4469"""df = pd.read_csv(io.StringIO(t), sep='\s+', parse_dates=[0])df.info()<class 'pandas.core.frame.DataFrame'>Int64Index: 5 entries, 0 to 4Data columns (total 2 columns):date 5 non-null datetime64[ns]Count 5 non-null int64dtypes: datetime64[ns](1), int64(1)memory usage: 120.0 bytes
So if you pass param parse_dates=[0]
to read_csv
there shouldn't be any need to call to_datetime
on the 'date' column after loading.
When to use dt
accessor
A common source of confusion revolves around when to use .year
and when to use .dt.year
.
The former is an attribute for pd.DatetimeIndex
objects; the latter for pd.Series
objects. Consider this dataframe:
df = pd.DataFrame({'Dates': pd.to_datetime(['2018-01-01', '2018-10-20', '2018-12-25'])}, index=pd.to_datetime(['2000-01-01', '2000-01-02', '2000-01-03']))
The definition of the series and index look similar, but the pd.DataFrame
constructor converts them to different types:
type(df.index) # pandas.tseries.index.DatetimeIndextype(df['Dates']) # pandas.core.series.Series
The DatetimeIndex
object has a direct year
attribute, while the Series
object must use the dt
accessor. Similarly for month
:
df.index.month # array([1, 1, 1])df['Dates'].dt.month.values # array([ 1, 10, 12], dtype=int64)
A subtle but important difference worth noting is that df.index.month
gives a NumPy array, while df['Dates'].dt.month
gives a Pandas series. Above, we use pd.Series.values
to extract the NumPy array representation.