[FIXED] How to extract all rows from a pandas dataframe given a series object

Issue

I have the following data as a dataframe:

Date         Category    Sales       Paid
8/12/2020    Table       1 table     Yes
8/12/2020    Chair       3chairs     Yes
13/1/2020    Cushion     8 cushions  Yes
24/5/2020    Table       3Tables     Yes
31/10/2020   Chair       12 Chairs   No
11/7/2020    Mats        12Mats      Yes
11/7/2020    Mats        4Mats       Yes

When I run my query I have a Series object returned that shows the Date which had the highest count of sales for the entire data.

ddate=df['Sales'].str.extract('^(\d+)', expand=False).astype(int).groupby(df['Date']).agg(sums='count').idxmax()

I may receive a return object that looks like this:

['8/12/2020']

In order to retrieve all rows in the original dataframe df I have been informed to run:

df[df['Date'].eq(ddate)]

However, this returns an empty dataframe. But if I choose a date as a string I do get back all the results

df[df['Date'].eq('8/12/2020')]

Why doesn't the series input work? How can I craft the query to make it work?

Solution

df[df['Date'].eq(ddate)] does not work because ddate is a list.

ddate=df['Sales'].str.extract('^(\d+)', expand=False).astype(int).groupby(df['Date']).agg(sums='count').idxmax()

print(ddate)
#output
['8/12/2020']

In order to make it work you have to unpack the list.

Since, it is the maximum value(only one value) you can do:

ddate[0]

In equation it will be

df[df['Date'].eq(ddate[0])]

Answered By - Goku - stands with Palestine