Bonono Bonono - 1 month ago 15
R Question

Create proportional pivot table with multiple variables in R

I'm trying to create a pivot table out of the following data:

Scan Distance NOFO ID
1 1.0 NANA A12
2 1.0 NANA A10
3 2.0 HAHT A12
4 4.0 TANA A1
5 1.0 TANA A3
6 0.5 TATA A1


I want the proportions of NOFO per each Distance and ID.

I've tried using ftable, dcast and tapply functions and am able to do it for NOFO ~ Distance:

prop.table(table(df$NOFO, df$Distance, useNA = "ifany")))


Example result:

0.5 1 2 4
HAHT 0.003012048 0.000000000 0.000000000 0.003012048
NANA 0.000000000 0.000000000 0.003012048 0.000000000
TANA 0.000000000 0.000000000 0.003012048 0.003012048
TATA 0.000000000 0.000000000 0.000000000 0.000000000


But I haven't been able to figure out a solution for NOFO ~ Distance + ID, so that each Distance proportion is split per ID.

Answer

Just add ID as a third variable.

prop.table(table(df$NOFO, df$Distance, df$ID, useNA = "ifany"))
, ,  = A1


             0.5         1         2         4
  HAHT 0.0000000 0.0000000 0.0000000 0.0000000
  NANA 0.0000000 0.0000000 0.0000000 0.0000000
  TANA 0.0000000 0.0000000 0.0000000 0.1666667
  TATA 0.1666667 0.0000000 0.0000000 0.0000000

, ,  = A10


             0.5         1         2         4
  HAHT 0.0000000 0.0000000 0.0000000 0.0000000
  NANA 0.0000000 0.1666667 0.0000000 0.0000000
  TANA 0.0000000 0.0000000 0.0000000 0.0000000
  TATA 0.0000000 0.0000000 0.0000000 0.0000000

, ,  = A12


             0.5         1         2         4
  HAHT 0.0000000 0.0000000 0.1666667 0.0000000
  NANA 0.0000000 0.1666667 0.0000000 0.0000000
  TANA 0.0000000 0.0000000 0.0000000 0.0000000
  TATA 0.0000000 0.0000000 0.0000000 0.0000000

, ,  = A3


             0.5         1         2         4
  HAHT 0.0000000 0.0000000 0.0000000 0.0000000
  NANA 0.0000000 0.0000000 0.0000000 0.0000000
  TANA 0.0000000 0.1666667 0.0000000 0.0000000
  TATA 0.0000000 0.0000000 0.0000000 0.0000000

or if you prefer a flat table

> ftable(.Last.value)
                 A1       A10       A12        A3

HAHT 0.5  0.0000000 0.0000000 0.0000000 0.0000000
     1    0.0000000 0.0000000 0.0000000 0.0000000
     2    0.0000000 0.0000000 0.1666667 0.0000000
     4    0.0000000 0.0000000 0.0000000 0.0000000
NANA 0.5  0.0000000 0.0000000 0.0000000 0.0000000
     1    0.0000000 0.1666667 0.1666667 0.0000000
     2    0.0000000 0.0000000 0.0000000 0.0000000
     4    0.0000000 0.0000000 0.0000000 0.0000000
TANA 0.5  0.0000000 0.0000000 0.0000000 0.0000000
     1    0.0000000 0.0000000 0.0000000 0.1666667
     2    0.0000000 0.0000000 0.0000000 0.0000000
     4    0.1666667 0.0000000 0.0000000 0.0000000
TATA 0.5  0.1666667 0.0000000 0.0000000 0.0000000
     1    0.0000000 0.0000000 0.0000000 0.0000000
     2    0.0000000 0.0000000 0.0000000 0.0000000
     4    0.0000000 0.0000000 0.0000000 0.0000000