CODEWITHSUNDEEP
pythonpandasdatetime
Hoekieee
Hoekieee

Reputation: 391

re-arrange data in pandas series by month, regardless of the year

I have a pandas series with data from 07-2018 till 06-2019, e.g.

2018-07 1
2018-08 3
2018-09 4
2018-10 5
2018-11 6
2018-12 7
2019-01 9
2019-02 8
2019-03 7
2019-04 6
2019-05 5
2019-06 4

I would like to re-arrange the data from jan-dec, regardless of the year:

2019-01 9
2019-02 8
2019-03 7
2019-04 6
2019-05 5
2019-06 4
2018-07 1
2018-08 3
2018-09 4
2018-10 5
2018-11 6
2018-12 7

Does anyone know how to do this?

Upvotes: 0

Views: 62

Answers (2)

jezrael
jezrael

Reputation: 863256

Create MultiIndex by months and years and sorting by it:

d = pd.to_datetime(df.index, format='%Y-%m')
df.index = [d.year, d.month, df.index]
df = df.sort_index(level=[0,1], ascending=[False, True]).reset_index(level=[0,1], drop=True)
print (df)
         col
2019-01    9
2019-02    8
2019-03    7
2019-04    6
2019-05    5
2019-06    4
2018-07    1
2018-08    3
2018-09    4
2018-10    5
2018-11    6
2018-12    7

Upvotes: 2

Md Johirul Islam
Md Johirul Islam

Reputation: 5162

If you have multiple years with the same date and you want to sum them by the month then a following strategy may be helpful. It groups by only the month and then takes the sum of the values for each month for all the years

df = pd.DataFrame({"date": ["2018-07", "2018-08", "2018-09", "2018-10",
                           "2018-11", "2018-12", "2019-01", "2019-02",
                            "2019-03", "2019-04", "2019-05","2019-06"],
"value":[1,3,4,5,6,7,9,8,7,6,5,4]})

res = df.groupby([df["date"].map(lambda x: x[-2:])]).sum()
print(res)

The output is:

          value
date       
01        9
02        8
03        7
04        6
05        5
06        4
07        1
08        3
09        4
10        5
11        6
12        7

Upvotes: 0

Related Questions