Group by Date and Column-CodePudding

I have a DataFrame:

Date_time           | Available
21/10/2020 05:00:01 |   Yes
21/10/2020 12:20:01 |   No
22/10/2020 04:30:01 |   Yes
22/10/2020 03:40:01 |   Yes
22/10/2020 01:50:01 |   No
23/10/2020 02:10:01 |   Yes
23/10/2020 11:30:01 |   Yes

I want to replicate SQL's case state and group by in Python.

SELECT date,
       sum(case when Available = 'Yes' then 1 else 0 end)*100/count(*) as Available_rate 
FROM table
group by date

What I am looking for is:

Date        | Available_rate 
21/10/2020  |   50
22/10/2020  |   66.667
23/10/2020  |   100

I am able to do this in python:

daily_count = df.groupby([df['date'].dt.date]).size().reset_index(name='counts')

I am not sure how to go further.

CodePudding user response：

You can convert "Available" to boolean, and use GroupBy.mean, this will take advantage of the True/1 and False/0 equivalence to give you the rate:

out = (df['Available']
       .eq('Yes')
       .groupby(df['Date'])
       .mean()
       .mul(100)
       )

Output:

Date
21/10/2020     50.000000
22/10/2020     66.666667
23/10/2020    100.000000
Name: Available, dtype: float64

alternative format:

out = (df['Available']
       .eq('Yes')
       .groupby(df['Date'])
       .mean()
       .mul(100)
       .rename('available rate')
       .reset_index()
       )

CodePudding user response：

Here's alternative solution, using pd.pivot_table:

x = pd.pivot_table(
    df.assign(Available_mean=df["Available"].eq("Yes")),
    index="Date",
    values="Available_mean",
    aggfunc="mean",
)

print(x * 100)

Prints:

            Available_mean
Date                      
2020-10-21       50.000000
2020-10-22       66.666667
2020-10-23      100.000000