CODEWITHSUNDEEP
pythondataframe
Arya
Arya

Reputation: 35

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

Upvotes: 0

Views: 42

Answers (1)

gañañufla
gañañufla

Reputation: 562

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

Upvotes: 1

Related Questions