CODEWITHSUNDEEP
pandas
dark horse
dark horse

Reputation: 447

Pandas - Filtering out data by weekday

I have a Dataframe that has list of dates with sales count for each of the days as shown below:

date,count
11/1/2018,345
11/2/2018,100
11/5/2018,432
11/7/2018,500
11/11/2018,555
11/17/2018,754

I am trying to check of all the sales that were done how many were done on a weekday. To pull all week-days in November I am doing the below:

weekday = pd.DataFrame(pd.bdate_range('2018-11-01', '2018-11-30'))

Now I am trying to compare dates in df with value in weekday as below:

df_final = df[df['date'].isin(weekday)]

But the above returns no rows.

Upvotes: 2

Views: 2533

Answers (2)

jeschwar
jeschwar

Reputation: 1314

Use a DatetimeIndex and let pandas do the work for you as follows:

# generate some sample sales data for the month of November
df = pd.DataFrame(
    {'count': np.random.randint(0, 900, 30)}, 
    index=pd.date_range('2018-11-01', '2018-11-30', name='date')
)

# resample by business day and call `.asfreq()` on the resulting groupby-like object to get your desired filtering
df.resample(rule='B').asfreq()

Other values for the resampling rule can be found here

Upvotes: 2

BENY
BENY

Reputation: 323226

You should remove pd.DataFrame when create the weekday, since when we using Series and DataFrame with isin means we not only match the values but also the index and columns , since the original index and columns may different from the new created dataframe weekday, that is why return the False

df.date=pd.to_datetime(df.date)
weekday = pd.bdate_range('2018-11-01', '2018-11-30')
df_final = df[df['date'].isin(weekday)]
df_final
Out[39]: 
        date  count
0 2018-11-01    345
1 2018-11-02    100
2 2018-11-05    432
3 2018-11-07    500

Simple example address the issue I mentioned above 

df=pd.DataFrame({'A':[1,2,3,4,5]})
newdf=pd.DataFrame({'B':[2,3]})
df.isin(newdf)
Out[43]: 
       A
0  False
1  False
2  False
3  False
4  False
df.isin(newdf.B.tolist())
Out[44]: 
       A
0  False
1   True
2   True
3  False
4  False

Upvotes: 3

Related Questions