convert month of dates into sequence

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

1 answer

  • answered 2022-05-06 22:16 ti7

    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
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum