Wrangling dataset by picking out Rotten Tomatoes movie ratings from a column

If the data is formatted similarly throughout your dataset, the following code should work:

df %>% group_by(Title) %>% 
  slice(match("Rotten Tomatoes", df$Ratings) + 1) %>%
  rename(rottentomatoes_rating = Ratings)

This gives:

# A tibble: 2 x 6
# Groups:   Title [2]
  Title             Year  Rated     Released   Runtime rottentomatoes_rating
  <chr>             <chr> <chr>     <date>     <chr>   <chr>                
1 Gully Boy         2019  Not Rated 2019-02-14 153 min 100%                 
2 Isn't It Romantic 2019  PG-13     2019-02-13 89 min  68%     

For the NAs, if the original data always has the RT score the row after the string is observed, then it should give you NA by default.

sumshyftw answer is good.

But here is a data.table version if you simply want to get rotten tomatoes's percents:

dt <- dt[dt$Ratings %like% "%",]
dt <- setnames(dt, "Ratings", "rottentomatoes_rating")

Output :

# A tibble: 2 x 6
  Title             Year  Rated     Released   Runtime rottentomatoes_rating
  <chr>             <chr> <chr>     <date>     <chr>   <chr>                
1 Isn't It Romantic 2019  PG-13     2019-02-13 89 min  68%                  
2 Gully Boy         2019  Not Rated 2019-02-14 153 min 100%  

I used %like% "%" because I assume that the full data is just like your example.

Assuming your dataset is called dt you can use this process to get a tidy version of your dataset:

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%

new version that fills NA values when blank

# using data.table
library(data.table)
dt <- as.data.table(df)

# Index will hold whether the row is a Provider eg Rotten Tomatoes, or a value
dt[, Index:=rep(c("Provider", "Value"), .N/2)]
# Need an index to bind these together
dt[, Provider.Id:=rep(1:(.N/2), each=2), by=Title]
dt[1:6,]

# segment out the Provider & Values in to columns
out <- dcast(dt, Title+Provider.Id~Index, value.var = "Ratings")
out[, Provider := NULL]

# now convert to full wide format 
out_df <- as.data.frame(dcast(out, Title~Provider, value.var="Value", fill=NA))
out_df

To get all metrics with data.table

# using data.table
library(data.table)
dt <- as.data.table(df)

# groups the data set with by, and extracts the Ratings
# makes use of logic that the odd indeces hold the name of the provider,
# the even ones hold the values. Only works if this holds.
# It can probably be optimised a bit. dcast converts from long to required wide
# format
splitRatings <- function(Ratings){
  # e.g. Ratings=dt$Ratings[1:6]
  N <- length(Ratings)
  split_dt <- data.table(DB=Ratings[1:N %% 2 == 1],
                         Values=Ratings[1-(1:N %% 2) == 1])
  out <- dcast(split_dt, .~DB, value.var = "Values")
  out[, ".":=NULL]
  out
}

# applies the function based on the by clause, returning the table embedded
dt2 <- dt[, splitRatings(Ratings), by=.(Title, Year, Rated, Released, Runtime)]

# convert back
out <- as.data.frame(dt2)

Here is one version.

df %>% 
  mutate(Value = ifelse(str_detect(Ratings, "\\d"), Ratings, NA)) %>% 
  fill(Value, .direction = "up") %>% 
  filter(!str_detect(Ratings, "\\d")) %>% 
  spread(Ratings, Value)