CODEWITHSUNDEEP
rpivot-table
Angel Nieves
Angel Nieves

Reputation: 73

How can I do a pivot table in R with a calculated field?

I'm trying to construct a pivot table with a calculated field in R. My data frame is similar to this:

   number   hour    tot      cefe  sell
        1  09:00    50         2     1
        1  10:00    52        NA    NA
        1  11:00    72         4     1
        1  12:00    96         3    NA
        1  13:00    90         4     1
        1  14:00    98         2     1
        2  08:00    38         4     1
        2  09:00   427        16     2
        2  10:00   493        16     1
        2  11:00   340        16    NA
        2  12:00   571        20     2
        2  13:00   547        23     2
        2  14:00   578        25     4

I need to construct a pivot table using "number" variable as rows, "hour" as columns and the result of "cefe/tot" as value. I don´t want to add another column or generate another data frame to do this but I don't know how to do it. I tried with cast function but it only considers "sell" variable as value. Any idea?

Upvotes: 2

Views: 436

Answers (1)

Andrew Brēza
Andrew Brēza

Reputation: 8317

You can do this with dplyr and tidyr, although this script does technically create a new column as part of its transformation.

library(dplyr)
library(tidyr)

x %>%
  mutate(cefetot = cefe/tot) %>%
  select(number, hour, cefetot) %>%
  spread(key = hour, value = cefetot)

Upvotes: 2

Related Questions