0

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

chimmy tata
  • 49
  • 1
  • 5

1 Answers1

2

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
ti7
  • 16,375
  • 6
  • 40
  • 68