I have the following data frame.
Port | Label | OtherColumnsFilledWithData
80 | 1 |
60 | 0 |
40 | 1 |
10 | 0 |
80 | 0 |
60 | 0 |
80 | 1 |
I want to create another dataframe that says how many of each ports there are with how many times the label is either 1 or 0.
Port | # | Label=1 | Label=2
80 | 3 | 2 | 1
60 | 2 | 0 | 2
40 | 1 | 1 | 1
Advertisement
Answer
It´s possible to make this with a pivot, but in this case i aggregate one more column to the dataframe
df = pd.DataFrame({'Port': [80, 40,60,10,80,60,80], 'Label': [1, 0,1,0,0,0,1], 'Qty':[1,1,1,1,1,1,1]})
Next, with a pivot_table you can create the table:
df_final = pd.pivot_table(df, columns=['Label'], index=['Port'], values=['Qty'], aggfunc=np.sum).fillna(0)
Finally, to get the total freq, apply:
df_final['Total'] = df_final[('Qty', 0)] + df_final[('Qty', 1)]