Bonono - 1 year ago 65
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.

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download