CODEWITHSUNDEEP
pythonexcelpandas
durmuş can
durmuş can

Reputation: 23

Sorting daily data in monthly groups with Python

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)

Excel file

Upvotes: 0

Views: 297

Answers (2)

jezrael
jezrael

Reputation: 863291

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

Upvotes: 1

le_camerone
le_camerone

Reputation: 630

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

Upvotes: 0

Related Questions