CODEWITHSUNDEEP
python-3.xpandas
Pavan Suvarna
Pavan Suvarna

Reputation: 501

how to take only maximum date value is there are two date in a week in dataframe

i have a dataframe called Data

Date          Value    Frequency
06/01/2020     256        A
07/01/2020     235        A
14/01/2020     85         Q
16/01/2020     625        Q
22/01/2020     125        Q

here it is observed that 6/01/2020 and 07/01/2020 are in the same week that is monday and tuesday. Therefore i wanted to take maximum date from week.

my final dataframe should look like this

Date          Value  Frequency
07/01/2020     235      A
16/01/2020     625      Q
22/01/2020     125      Q

I want the maximum date from the week , like i have showed in my final dataframe example.

I am new to python, And i am searching answer for this which i didnt find till now ,Please help

Upvotes: 2

Views: 238

Answers (1)

jezrael
jezrael

Reputation: 862711

First convert column to datetimes by to_datetime and use DataFrameGroupBy.idxmax for rows with maximum datetime per rows with Series.dt.strftime, last select rows by DataFrame.loc:

df['Date'] = pd.to_datetime(df['Date'], dayfirst=True)

print (df['Date'].dt.strftime('%Y-%U'))
0    2020-01
1    2020-01
2    2020-02
3    2020-02
4    2020-03
Name: Date, dtype: object

df = df.loc[df.groupby(df['Date'].dt.strftime('%Y-%U'))['Date'].idxmax()]
print (df)
        Date  Value Frequency
1 2020-01-07    235         A
3 2020-01-16    625         Q
4 2020-01-22    125         Q

If format of datetimes cannot be changed:

d = pd.to_datetime(df['Date'], dayfirst=True)
df = df.loc[d.groupby(d.dt.strftime('%Y-%U')).idxmax()]
print (df)
         Date  Value Frequency
1  07/01/2020    235         A
3  16/01/2020    625         Q
4  22/01/2020    125         Q

Upvotes: 2

Related Questions