i want to combine months from years into sequence, for example, i have dataframe like this:
stuff_id date 1 2015-02-03 2 2015-03-03 3 2015-05-19 4 2015-10-13 5 2016-01-07 6 2016-03-20
i want to sequence the months of the date. the desired output is:
stuff_id date month 1 2015-02-03 1 2 2015-03-03 2 3 2015-05-19 4 4 2015-10-13 9 5 2016-01-07 12 6 2016-03-20 14
which means feb’15 is the first month in the date list and jan’2016 is the 12th month after feb’2015
Advertisement
Answer
If your date column is a datetime (if it’s not, cast it to one), you can use the .dt.month and .dt.year properties for this!
https://pandas.pydata.org/docs/reference/api/pandas.Series.dt.month.html
recast
(text copy from Answer to Pasting data into a pandas dataframe)
>>> df = pd.read_table(io.StringIO(s), delim_whitespace=True) # text from SO >>> df["date"] = pd.to_datetime(df["date"]) >>> df stuff_id date 0 1 2015-02-03 1 2 2015-03-03 2 3 2015-05-19 3 4 2015-10-13 4 5 2016-01-07 5 6 2016-03-20 >>> df.dtypes stuff_id int64 date datetime64[ns] dtype: object
extract years and months to decimal months and reduce to relative
>>> months = df["date"].dt.year * 12 + df["date"].dt.month # series >>> df["months"] = months - min(months) + 1 >>> df stuff_id date months 0 1 2015-02-03 1 1 2 2015-03-03 2 2 3 2015-05-19 4 3 4 2015-10-13 9 4 5 2016-01-07 12 5 6 2016-03-20 14