combine the similar values of a column into rows

Question

I have a column with userIds, but I want to change this column into different rows and combine the same values in one row.

Code to produce the data in the image looks like:

userid <- c(415,375,415,415,513,64,375,415,317,375)
rating <- c(4,4,4,5,5,4,3,5,4,5)
title <- c("GoldenEye (1995)","GoldenEye (1995)","Babe (1995)",
           "Usual Suspects, The (1995)", "Braveheart (1995)","Braveheart (1995)","Braveheart (1995)",
           "Braveheart (1995)","Apollo 13 (1995)","Apollo 13 (1995)")

data <- data.frame(userid,rating,title)

How do I transform the data so the movie titles are columns, with one row per userid, where the ratings are the cells of the data frame?

  userid Apollo 13 (1995) Babe (1995) Braveheart (1995) GoldenEye (1995)
1     64               NA          NA                 4               NA
2    317                4          NA                NA               NA
3    375                5          NA                 3                4
4    415               NA           4                 5                4
5    513               NA          NA                 5               NA
  Usual Suspects, The (1995)
1                         NA
2                         NA
3                         NA
4                          5
5                         NA
>

Please make sure your question adheres to [this](https://stackoverflow.com/help/mcve) format. That enables us to help you. — Gene Burinsky, Apr 28 '18 at 20:12
Hello Rodaina. Welcome to Stack Overflow! Can you elaborate on what your desired output is? Do you want to transform the data so each row is a movie name, and the columns are the userids? Also, on SO it is helpful to post a question with a [Minimal, Complete, and Verifiable Example](https://stackoverflow.com/help/mcve) so people can provide answers that include working code and data. — Len Greski, Apr 28 '18 at 20:17
I want the rows to be the userIds and columns to be the names of the movies, and then I want to fill this matrix with the ratings of each movie with the corresponding user — Rodaina Mohamed, Apr 28 '18 at 20:46

Giovana Stein · Accepted Answer · 2018-05-04T21:09:17.977

As you didn't gave us a example database, I created a little one. I believe this solve your problem:

library(tidyr)
library(data.table)

movies = data.table(userid = c(21, 24, 35, 21, 27, 35, 21),    
                    movie = c("titanic","titanic", "titanic", "cinderella", "cinderella", "cinderella", "big hero"), 
                    rating = c(1,3,4,2,1,5,3))

movies = spread(movies, key = movie, value = rating)

Hope it helps!

combine the similar values of a column into rows

1 Answers1