Grouping data caracteristics - Pandas Python

Question

Data set Image Hello, So basically i want to go from the first data set to the second, so grouping by animal and color to create a columns counting the number in each category (Total), columns Alarm counting the number of alarm cases and repartition dividing alarm by total

J=data.groupby(['Animal', 'Color']).count()
Gi=data[data['Alert']='alarm']
G=Gi.groupby(['Animal', 'Color']).count()
#Then I don't know what to do how to create a new dataset as i want to, let's call it dataf
###
dataf['Repartition']=dataf['Alarm']/dataf['Total']

Thank you for your help and advices :)

Answer 1

You basically need to merge your dataframe Jand G (little bit modified) like this :

# Rename your result column as "Total" and reset the index
J=data.groupby(['Animal', 'Color']).count().rename(index=str,columns={"Alert":"Total"}).reset_index()
Gi=data[data['Alert']=='alarm']
# Add the reset_index as well on G
G=Gi.groupby(['Animal', 'Color']).count().reset_index()
# Merge the two DataFrames
dataf = pd.merge(J, G, how='left', on=['Animal','Color'])
# Ensure there's no null values after merging
dataf['Alarm'].fillna(0)
# And then you can compute the repartition
dataf['Repartition']=dataf['Alarm']/dataf['Total']

pd.merge() doc : https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.merge.html

Hope it helps !