Sorting daily data in monthly groups with Python

Question

I have a daily streamflow data for 11 years as in the picture. I want to sort the streamflow values in descending order for every month (every month has its own descending order i.e 10th month is going like 107,98,86... and and 11th month is like 170,154,144,... but they are all in same dataframe)

[Please don't post images of code/data (or links to them)](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question) — jezrael, Apr 05 '20 at 11:01
Better is generate some sample data like `np.random.seed(2020) rng = pd.date_range('1989-10-01', periods=50) df = pd.DataFrame({'DATE (Month, Day, Year)': rng, 'STREAMFLOW (CFS)': np.random.randint(1000, size=50)})` — jezrael, Apr 05 '20 at 11:01
Also what is expected output `DataFrame` ? Can you add first 10 rows of first month data? — jezrael, Apr 05 '20 at 11:02

jezrael · Accepted Answer · 2020-04-05T10:53:41.297

You can convert column DATE to datetimes if necessary by to_datetime, then create months periods column by Series.dt.to_period an dlast sorting by multiple columns by DataFrame.sort_values:

np.random.seed(2020)
rng = pd.date_range('1989-10-01', periods=50)
df = pd.DataFrame({'DATE (Month, Day, Year)': rng, 
                   'STREAMFLOW (CFS)': np.random.randint(1000, size=50)})  
print (df.head(10))
  DATE (Month, Day, Year)  STREAMFLOW (CFS)
0              1989-10-01               864
1              1989-10-02               392
2              1989-10-03               323
3              1989-10-04               630
4              1989-10-05               707
5              1989-10-06                91
6              1989-10-07               637
7              1989-10-08               643
8              1989-10-09               583
9              1989-10-10               952

#already datetimes
#df['DATE (Month, Day, Year)'] = pd.to_datetime(df['DATE (Month, Day, Year)'], dayfirst=True)

df['per'] = df['DATE (Month, Day, Year)'].dt.to_period('m')

df = df.sort_values(['per', 'STREAMFLOW (CFS)', ], ascending=[True, False])
print (df.head(10))
   DATE (Month, Day, Year)  STREAMFLOW (CFS)      per
29              1989-10-30               980  1989-10
14              1989-10-15               970  1989-10
20              1989-10-21               958  1989-10
9               1989-10-10               952  1989-10
13              1989-10-14               920  1989-10
22              1989-10-23               870  1989-10
0               1989-10-01               864  1989-10
28              1989-10-29               806  1989-10
15              1989-10-16               777  1989-10
4               1989-10-05               707  1989-10

score 0 · Answer 2 · answered Apr 05 '20 at 10:42

0

Convert your date columns to a pandas datetime object using

df['Date'] =pd.to_datetime(df.Date)

Then sort by date

df.sort_values(by='Date')

You can see a more detailed answer here

answered Apr 05 '20 at 10:42

le_camerone

630
5
17

Sorting daily data in monthly groups with Python

2 Answers2