CODEWITHSUNDEEP
r
Sas
Sas

Reputation: 37

Collapsing rows into one value

I have a dataset:

LOC     Store   Question        Rating

NYC     MCD     Food quality?       3
NYC     MCD     Water quality?      4
NYC     MCD     Cleanliness?        5

LA      BK      Food quality?       3
LA      BK      Water quality?      2   
LA      BK      Cleanliness?        5

SF      MCD     Food quality?       4
SF      MCD     Water quality?      5   
SF      MCD     Cleanliness?        4

and I’d like to collapse the questions into the average rating for each store:

Loc Store   Avg.Rating
NYC MCD     (3+4+5)/3
LA  BK      (3+2+5)/3
SF  MCD     (4+5+4)/3

tribble for convenience:

df <- 
tribble(
~LOC,   ~Store, ~Question,  ~Rating,
"NYC",  "MCD",  "Food?",        3,
"NYC",  "MCD",  "Water?",       4,
"NYC",  "MCD",  "Clean?",       5,
"LA",   "BK",   "Food?",        3,
"LA",   "BK",   "Water?",       2,  
"LA",   "BK",   "Clean?",       5,
"SF",   "MCD",  "Food?",        4,
"SF",   "MCD",  "Water?",       5,  
"SF",   "MCD",  "Clean?",       4
)

Upvotes: 0

Views: 33

Answers (1)

rpolicastro
rpolicastro

Reputation: 1305

The easiest way would be with 'group_by' and 'summarize' from dplyr.

library("tibble")
library("dplyr")
library("magrittr")

df <- 
tribble(
~LOC,   ~Store, ~Question,  ~Rating,
"NYC",  "MCD",  "Food?",        3,
"NYC",  "MCD",  "Water?",       4,
"NYC",  "MCD",  "Clean?",       5,
"LA",   "BK",   "Food?",        3,
"LA",   "BK",   "Water?",       2,  
"LA",   "BK",   "Clean?",       5,
"SF",   "MCD",  "Food?",        4,
"SF",   "MCD",  "Water?",       5,  
"SF",   "MCD",  "Clean?",       4
)

store.avg <- group_by(df, LOC, Store) %>%
  summarize(Avg.Rating = mean(Rating))

# A tibble: 3 x 3
# Groups:   LOC [3]
  LOC   Store Avg.Rating
  <chr> <chr>      <dbl>
1 LA    BK          3.33
2 NYC   MCD         4   
3 SF    MCD         4.33

Upvotes: 1

Related Questions