Creating a Matrix using 2 columns of a dataframe and count the No of occurrences of events

Question

I have a Df which looks like this:-

    tests machine
    A     mac1
    A     mac1
    A     mac2
    A     mac2
    B     mac1
    B     mac1
    B     mac3
    C     mac3
    C     mac3

Using this Df i want create a Dataframe which looks like this:

    No_of_tests   mac1 mac2 mac3
    A             2    2    0     #test A is done 2 times on mac1, 2 times on mac2 and so on
    B             2    1    0     #test B is done 2 times on mac1, 1 time on mac2 and so on
    C             0    0    2     #test C is done 0 times on mac1, 0 times on mac2 and so on

Can somebody help me create this matrix using the Dataframe above.

`pd.crosstab(df.tests,df.machine,df.machine,aggfunc='count')` — Umar.H, Jun 10 '20 at 10:23
I am getting all the values NAN's after using this @Datanovice — nivedan gowda, Jun 10 '20 at 10:34
I think the NAN's representing 0's meaning that test is not performed in that machine? — nivedan gowda, Jun 10 '20 at 10:43

score 1 · Accepted Answer · answered Jun 10 '20 at 10:45

1

IICU

 df.groupby('tests')['machine'].value_counts().unstack('machine', fill_value=0).reset_index()

answered Jun 10 '20 at 10:45

wwnde

26,119
6
18
32

Creating a Matrix using 2 columns of a dataframe and count the No of occurrences of events

1 Answers1