r - 通过从列中挑选烂番茄电影评级来整理数据集

Question

我有这个示例数据集:

structure(list(Title = c("Isn't It Romantic", "Isn't It Romantic", 
"Isn't It Romantic", "Isn't It Romantic", "Isn't It Romantic", 
"Isn't It Romantic", "Gully Boy", "Gully Boy", "Gully Boy", "Gully Boy", 
"Gully Boy", "Gully Boy", "The Wandering Earth", "The Wandering Earth", 
"The Wandering Earth", "The Wandering Earth", "The Wandering Earth", 
"The Wandering Earth", "How to Train Your Dragon: The Hidden World", 
"How to Train Your Dragon: The Hidden World", "How to Train Your Dragon: The Hidden World", 
"How to Train Your Dragon: The Hidden World", "How to Train Your Dragon: The Hidden World", 
"How to Train Your Dragon: The Hidden World", "American Woman", 
"American Woman", "Us", "Us", "Us", "Us", "Us", "Us", "The Wolf's Call", 
"The Wolf's Call", "Avengers: Endgame", "Avengers: Endgame", 
"Avengers: Endgame", "Avengers: Endgame", "Avengers: Endgame", 
"Avengers: Endgame", "The Silence", "The Silence", "The Silence", 
"The Silence", "The Silence", "The Silence", "My Little Pony: Equestria Girls: Spring Breakdown", 
"My Little Pony: Equestria Girls: Spring Breakdown"), Ratings = c("Internet Movie Database", 
"5.9/10", "Rotten Tomatoes", "68%", "Metacritic", "60/100", "Internet Movie Database", 
"8.4/10", "Rotten Tomatoes", "100%", "Metacritic", "65/100", 
"Internet Movie Database", "6.4/10", "Rotten Tomatoes", "74%", 
"Metacritic", "62/100", "Internet Movie Database", "7.6/10", 
"Rotten Tomatoes", "91%", "Metacritic", "71/100", "Rotten Tomatoes", 
"57%", "Internet Movie Database", "7.1/10", "Rotten Tomatoes", 
"94%", "Metacritic", "81/100", "Internet Movie Database", "7.6/10", 
"Internet Movie Database", "8.7/10", "Rotten Tomatoes", "94%", 
"Metacritic", "78/100", "Internet Movie Database", "5.2/10", 
"Rotten Tomatoes", "23%", "Metacritic", "25/100", "Internet Movie Database", 
"7.7/10")), row.names = c(NA, -48L), class = c("tbl_df", "tbl", 
"data.frame"))

Ratings 列为每部电影提供 3 种不同类型的评分(Imdb、Rotten Tomatoes 和 Metacritic)，每部电影分布在 6 行中。

我想整理这个数据集，以便为每部电影创建一个名为 rottentomatoes_rating 的新列，值是评级。因此，在我的样本数据集中，Isn't it Romantic movie 在 rottentomatoes_rating 下有 68%，Gully Boy 在 rottentomatoes_rating 下有 100%，等等。

对于那些没有 rottentomatoes_rating 的电影，我想将 NA 放在 rottentomatoes_rating 下。

我考虑过在 tidyr 中使用 spread，但我不太清楚该怎么做，因为在我的例子中，变量和值都在同一列中!

Answer 1

假设您的数据集名为 dt，您可以使用此过程来获取数据集的整洁版本:

library(tidyverse)

# specify indexes of Rating companies
ids = seq(1, nrow(dt), 2)

# get rows of Rating companies
dt %>% slice(ids) %>%
  # combine with the rating values
  cbind(dt %>% slice(-ids) %>% select(RatingsValue = Ratings)) %>%
  # reshape dataset
  spread(Ratings, RatingsValue)

#                Title Year     Rated   Released Runtime Internet Movie Database Metacritic Rotten Tomatoes
# 1         Gully Boy 2019 Not Rated 2019-02-14 153 min                  8.4/10     65/100            100%
# 2 Isn't It Romantic 2019     PG-13 2019-02-13  89 min                  5.9/10     60/100             68%