CODEWITHSUNDEEP
pythonpandaspandas-groupbydayofmonth
Ferit
Ferit

Reputation: 648

Python Pandas - Get the rows of first and last day of particular months

My data set df looks as follows:

Date         Value
...
2012-07-31   61.9443
2012-07-30   62.1551
2012-07-27   62.3328
...          ... 
2011-10-04   48.3923
2011-10-03   48.5939
2011-09-30   50.0327
2011-09-29   51.8350
2011-09-28   50.5555
2011-09-27   51.8470
2011-09-26   49.6350
...          ...
2011-08-03   61.3948
2011-08-02   61.5476
2011-08-01   64.1407
2011-07-29   65.0364
2011-07-28   65.7065
2011-07-27   66.3463
2011-07-26   67.1508
2011-07-25   67.5577
...          ...
2010-10-05   57.3674
2010-10-04   56.3687
2010-10-01   57.6022
2010-09-30   58.0993
2010-09-29   57.9934

Below are the data type of the two columns:

Type                 Column Name              Example Value
-----------------------------------------------------------------
datetime64[ns]       Date                     2020-06-19 00:00:00
float64              Value                    108.82

I would like to have a subset of df that contains only the rows where the first entry in October and the last entry of July are selected:

Date         Value
...
2012-07-31   61.9443
2011-10-03   48.5939
2011-07-29   65.0364
2010-10-01   57.6022

Any idea how to do that?

Upvotes: 6

Views: 2314

Answers (3)

Chris
Chris

Reputation: 16147

You can sort by the date so that you know they are in chronological order. After that create two data frames, one where month is 7 and take the last record of the group and one where month is 10 take the first record of the group.

Then you can concatenate them.

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

j = df[df['Date'].dt.month == 7].groupby([df.Date.dt.year, df.Date.dt.month]).last()
o = df[df['Date'].dt.month == 10].groupby([df.Date.dt.year, df.Date.dt.month]).first()

pd.concat([j,o]).reset_index(drop=True)

Output

    Date        Value
0   2011-07-29  65.0364
1   2012-07-31  61.9443
2   2010-10-01  57.6022
3   2011-10-03  48.5939

Upvotes: 6

Ashwiniku918
Ashwiniku918

Reputation: 281

An elegant solution without group just by using index from sorted dataframe:

   # Sort you data by Date and convert date string to datetime
   df['Date']=pd.to_datetime(df['Date'])
   df = df.sort_values(by='Date')
   # For selecting first row just subset by index where month is 7 and select first index i.e. 0
   jul = df.loc[[df.index[df['Date'].dt.month == 7].tolist()[0]]]
   # For sleecting last row just subset by index where months is 10 and select last index i.e -1
   oct = df.loc[[df.index[df['Date'].dt.month == 10].tolist()[-1]]]
   #Finally concatenate both    
   pd.concat([jul,oct]).reset_index(drop=True)

Upvotes: 1

Roy2012
Roy2012

Reputation: 12503

Here's a solution which is based on Pandas only:

df = df.sort_values("Date")
october = df.groupby([df["Date"].dt.year, df["Date"].dt.month], as_index = False).first()
october = october[october.Date.dt.month == 10]

july = df.groupby([df["Date"].dt.year, df["Date"].dt.month], as_index = False).last()
july = july[july.Date.dt.month == 7]

pd.concat([july, october])

The result is:

        Date    Value
2 2011-07-29  65.0364
6 2012-07-31  61.9443
1 2010-10-01  57.6022
5 2011-10-03  48.5939

Upvotes: 3

Related Questions