Pandas dataframe pandas

Question

I have this csv:

message	reported_agents
Missing status flag was removed	host1
Missing status flag was raised	host1
Missing status flag was removed	host2
Missing status flag was raised	host2
Missing status flag was raised	host3

I need to get this

Host	total messages	Missing flag remove	Missing flag raised
host1	2	1	1
host2	2	1	1
host3	1	0	1

This is what I did but it doesn't show what I need. Thank you for your help

def load_data():
    event = pd.read_csv("events.csv")
    return event
data = load_data()

def GCmissing(data):
        x=data.groupby('hosts')['message'].value_counts()         

Answer 1

Use pandas.crosstab and then sum along columns for totals:

out_df = pd.crosstab(index = df['reported_agents'], columns = df['message'])
out_df['total_messages'] = out_df.sum(axis = 1)

Output:

reported_agents Missing status flag was raised  Missing status flag was removed total_messages
host1           1                               1                               2
host2           1                               1                               2
host3           1                               0                               1