CODEWITHSUNDEEP
pythonpandastime
emax
emax

Reputation: 7245

Pandas: how to take the year month of a date column?

I have a large dataframe df that contains the date in the form %Y-%m-%d. 

df
    val     date
0   356   2017-01-03
1   27    2017-03-28
2   33    2017-07-12
3   455   2017-09-14

I wan to create a new column YearMonth that contains the date in the form %Y%m

df['YearMonth'] = df['date'].dt.to_period('M')

but it takes a very long time

Upvotes: 1

Views: 600

Answers (2)

jezrael
jezrael

Reputation: 862406

Your solution is faster as strftime in larger DataFrame, but there is different output - Periods vs strings:

df['YearMonth'] = df['date'].dt.strftime('%Y-%m')
df['YearMonth1'] = df['date'].dt.to_period('M')
print (type(df.loc[0, 'YearMonth']))
<class 'str'>

print (type(df.loc[0, 'YearMonth1']))
<class 'pandas._libs.tslibs.period.Period'>

#[40000 rows x 2 columns]
df = pd.concat([df] * 10000, ignore_index=True)

In [63]: %timeit df['date'].dt.strftime('%Y-%m')
237 ms ± 1.7 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [64]: %timeit df['date'].dt.to_period('M')
57 ms ± 985 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

List comprehension is slow too:

In [65]: %timeit df['new'] = [str(x)[:7] for x in df['date']]
209 ms ± 2.6 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Another Alexander's solution:

In [66]: %timeit df['date'].astype(str).str[:7]
236 ms ± 1.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Upvotes: 2

Alexander
Alexander

Reputation: 109510

You could convert the date column to a string if it is not already and then truncate to the year and month (i.e. the first seven characters).

df['YearMonth'] = df['date'].astype(str).str[:7]
   val        date YearMonth
0  356  2017-01-03   2017-01
1   27  2017-03-28   2017-03
2   33  2017-07-12   2017-07
3  455  2017-09-14   2017-09

Upvotes: 0

Related Questions