Skip to content
Advertisement

How to count all the elements in a column with reference to another column Python

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)

df_final

Finally, to get the total freq, apply:

df_final['Total'] = df_final[('Qty', 0)] + df_final[('Qty', 1)]

df_final

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement