Cómo filtrar múltiples valores en una columna en R

Problema

Queremos filtrar múltiples valores en una columna en R. En nuestro ejemplo, queremos filtrar las filas que contengan la cadena de texto Tom o Lynn en la columna name.

Ejemplo

  days  name
1   88  Lynn
2   11   Tom
3    2 Chris
4    5  Lisa
5   22  Kyla
6    1   Tom
7  222  Lynn
8    2  Lynn

df <-
  data.frame(
    days = c(88, 11, 2, 5, 22, 1, 222, 2),
    name = c("Lynn", "Tom", "Chris", "Lisa", "Kyla", "Tom", "Lynn", "Lynn")
  ) 

Solución

Base package

df[df$name %in% c("Tom", "Lynn"), ] # or
target <- c("Tom", "Lynn")
df[df$name %in% target, ]

  days name
1   88 Lynn
2   11  Tom
6    1  Tom
7  222 Lynn
8    2 Lynn

dplyr

library(dplyr)
filter(df, name %in% c("Tom", "Lynn")) # or
target <- c("Tom", "Lynn")
filter(df, name %in% target)

data.table

library(data.table)
DT <- data.table(df)
DT[name %in% target]

sqldf

library(sqldf)
# Dos alternativas:
sqldf('SELECT *
      FROM df 
      WHERE name = "Tom" OR name = "Lynn"')
sqldf('SELECT *
      FROM df 
      WHERE name IN ("Tom", "Lynn")')

Entradas relacionadas

• Filtrar filas que contengan una cadena de texto en R

Referencias

• stackoverflow

How to filter multiple values on a column in R

Problem

We want to filter multiple values on a column in R. In our example, we want to subset the rows containing the string Tom or Lynn for the column name.

Example

  days  name
1   88  Lynn
2   11   Tom
3    2 Chris
4    5  Lisa
5   22  Kyla
6    1   Tom
7  222  Lynn
8    2  Lynn

df <-
  data.frame(
    days = c(88, 11, 2, 5, 22, 1, 222, 2),
    name = c("Lynn", "Tom", "Chris", "Lisa", "Kyla", "Tom", "Lynn", "Lynn")
  ) 

Solution

Base package

df[df$name %in% c("Tom", "Lynn"), ] # or
target <- c("Tom", "Lynn")
df[df$name %in% target, ]

  days name
1   88 Lynn
2   11  Tom
6    1  Tom
7  222 Lynn
8    2 Lynn

dplyr

library(dplyr)
filter(df, name %in% c("Tom", "Lynn")) # or
target <- c("Tom", "Lynn")
filter(df, name %in% target)

data.table

library(data.table)
DT <- data.table(df)
DT[name %in% target]

sqldf

library(sqldf)
# Two alternatives:
sqldf('SELECT *
      FROM df 
      WHERE name = "Tom" OR name = "Lynn"')
sqldf('SELECT *
      FROM df 
      WHERE name IN ("Tom", "Lynn")')

Related posts

• Filter rows containing a certain string in R

References

• stackoverflow

Filter rows containing a certain string in R

Problem

We want to filter rows containing a certain string in R. In our example, rows containing the string 'foo'.

foo <- data.frame(Company = c("company1", "foo", "test", "food"), Metric = rnorm(4, 10))

   Company    Metric
1 company1  7.590178
2      foo  9.711493
3     test 10.862799
4     food  9.337434

Solution

dplyr

Using the grepl function inside filter.

library(dplyr)
filter(foo, grepl("foo", Company))

  Company   Metric
1     foo 9.711493
2    food 9.337434

data.table

Another options is the function like from data.table with a similar syntax to SQL.

library(data.table)
DT <- data.table(foo)
DT[Company %like% 'foo']

   Company   Metric
1:     foo 9.711493
2:    food 9.337434

References

• stackoverflow

Filtrar filas que contengan una cadena de texto en R

Problema

Queremos filtrar las filas de un data frame que contengan una determinada cadena de texto. En nuestro ejemplo las filas que contengan la cadena 'foo'.

foo <- data.frame(Company = c("company1", "foo", "test", "food"), Metric = rnorm(4, 10))

   Company    Metric
1 company1  7.590178
2      foo  9.711493
3     test 10.862799
4     food  9.337434

Solución

dplyr

Usamos la función grepl dentro de filter.

library(dplyr)
filter(foo, grepl("foo", Company))

  Company   Metric
1     foo 9.711493
2    food 9.337434

data.table

Otra opción sería usar la función like de data.table, con una sintaxis similar a SQL.

library(data.table)
DT <- data.table(foo)
DT[Company %like% 'foo']

   Company   Metric
1:     foo 9.711493
2:    food 9.337434

Referencias

• stackoverflow

Create a mini IMDb database in R

Introduction

In a previous post we showed how to extract movie info R info using the imdbapi package. In this post we will create a mini imdb database using the same package.

Solution

If we use the free version, the maximum number of requests per day is 1,000. We need to request an API key here.

First we need a vector containing the movie titles or the IMDbIDs (e.g.: for Vertigo the last section of the url https://www.imdb.com/title/tt0052357/, the string tt0052357. In our example we will use the list containing the results from the Sight and Sound 2012 poll of 846 critics, these are the films receiving at least 3 votes.

library(imdbapi)
library(data.table)
library(tidyverse)
sight_sound <- read.csv("https://sites.google.com/site/nubededatosblogspotcom/Sight&Sound2012-CriticsPoll.txt", stringsAsFactors = FALSE)
glimpse(sight_sound)

Observations: 588
Variables: 17
$ const                          "tt0052357", "tt0033467", "tt004643...
$ position                       1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, ...
$ created                        "Thu Aug 16 07:42:05 2012", "Thu Au...
$ description                    NA, NA, NA, NA, NA, NA, NA, NA, NA,...
$ modified                       "Thu Aug 16 07:42:05 2012", "Thu Au...
$ Title                          "Vertigo", "Citizen Kane", "Tôkyô m...
$ Directors                      "Alfred Hitchcock", "Orson Welles",...
$ Title.type                     "Feature Film", "Feature Film", "Fe...
$ IMDb.Rating                    8.5, 8.5, 8.2, 8.0, 8.3, 8.3, 8.0, ...
$ PeacefulAnarchy.rated          10, 9, 10, 9, 9, 6, 6, 10, 8, 9, 6,...
$ Runtime..mins.                 128, 119, 136, 110, 94, 160, 119, 6...
$ Genres                         "mystery, romance, thriller", "dram...
$ Year                           1958, 1941, 1953, 1939, 1927, 1968,...
$ Num.Votes                      153502, 205699, 16219, 14872, 19188...
$ Release.Date..month.day.year.  "1958-05-09", "1941-05-01", "1953-1...
$ Id                             1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, ...
$ URL                            "http://www.imdb.com/title/tt005235...

We use the function lapply to extract the info for all IMDbIDs.

tt <-
  lapply(sight_sound$const, function(x) {
    return(tryCatch(
      find_by_id(
        x,
        type = NULL,
        year_of_release = NULL,
        plot = "full",
        include_tomatoes = TRUE,
        api_key = "12345678"
      ),
      error = function(e)
        NULL
    ))
  })
df_sight_sound <- rbindlist(tt, fill = TRUE)
df_sight_sound$Ratings <- as.character(df_sight_sound$Ratings)
df_sight_sound <- as.data.frame(df_sight_sound)
df_sight_sound %>% distinct(imdbID) %>% summarise(n= n())

    n
1 586

After running the code some titles may be missing. In our examples, two title. We will repeat the process until obtain all of them.

# Checking missing titles
m <- subset(sight_sound, !(const %in% df_sight_sound$imdbID))$const 
m

[1] "tt0115751" "tt0032551"

Finally, we keep distinct titles removing duplicates.

df_sight_sound <- df_sight_sound %>% 
  filter(grepl("Internet",Ratings)) %>% 
  group_by(imdbID) %>% 
  distinct()

# A tibble: 588 x 26
# Groups:   imdbID [588]
   Title Year  Rated Released   Runtime Genre Director Writer Actors Plot 
                       
 1 Vert~ 1958  PG    1958-07-21 128 min Myst~ Alfred ~ "Alec~ James~ "Joh~
 2 Citi~ 1941  PG    1941-09-05 119 min Dram~ Orson W~ Herma~ Josep~ "A g~
 3 Toky~ 1953  NOT ~ 1972-03-13 136 min Drama Yasujir~ Kôgo ~ Chish~ An e~
 4 The ~ 1939  NOT ~ 1950-04-08 110 min Come~ Jean Re~ Jean ~ Nora ~ Avia~
 5 Sunr~ 1927  NOT ~ 1927-11-04 94 min  Dram~ F.W. Mu~ Carl ~ Georg~ "In ~
 6 2001~ 1968  G     1968-05-12 149 min Adve~ Stanley~ Stanl~ Keir ~ "\"2~
 7 The ~ 1956  PASS~ 1956-05-26 119 min Adve~ John Fo~ Frank~ John ~ Etha~
 8 Man ~ 1929  NOT ~ 1929-05-12 68 min  Docu~ Dziga V~ Dziga~ Mikha~ This~
 9 The ~ 1928  NOT ~ 1928-10-25 114 min Biog~ Carl Th~ Josep~ Maria~ The ~
10 8½    1963  NOT ~ 1963-06-25 138 min Drama Federic~ Feder~ Marce~ Guid~
# ... with 578 more rows, and 16 more variables: Language ,
#   Country , Awards , Poster , Ratings ,
#   Metascore , imdbRating , imdbVotes , imdbID ,
#   Type , DVD , BoxOffice , Production , Website ,
#   Response , totalSeasons 

To export the final results as a csv:

write.csv(df_sight_sound, "df_sight_sound.csv", row.names = FALSE)

Related posts

• Extract movie info using the OMDb API in R

References

• Package imdbapi manual
• ombapi

How to subset a contingency table in R?

Problem

We'd like to subset a contingency table. In our example, we use the dataset chickwts, subsetting those types of feed for which we have more than 11 observations.

table(chickwts$feed)

   casein horsebean   linseed  meatmeal   soybean sunflower 
       12        10        12        11        14        12

Solution

• Base package

Using the function subset.

subset(data.frame(table(chickwts$feed)), Freq > 11)

• dplyr

library(dplyr)
chickwts %>% 
  count(feed) %>%
  filter(n > 11) 

Results

 # base
      Var1 Freq
1    casein   12
3   linseed   12
5   soybean   14
6 sunflower   12

# dplyr

# A tibble: 4 × 2
       feed     n
      
1    casein    12
2   linseed    12
3   soybean    14
4 sunflower    12

References

• stackoverflow

Plot a continuous series with ggplot2

Problem

When we try to plot a continuous series, in our example WS (Winter Solstice), ggplot2 connects the last winter data point in March to the first winter data point in December.

• Data

library(ggplot2)

getSeason <- function(DATES) {
#found here https://stackoverflow.com/questions/9500114/find-which-season-a-particular-date-belongs-to
WS <- as.Date("2012-12-15", format = "%Y-%m-%d") # Winter Solstice
SE <- as.Date("2012-3-15",  format = "%Y-%m-%d") # Spring Equinox
SS <- as.Date("2012-6-15",  format = "%Y-%m-%d") # Summer Solstice
FE <- as.Date("2012-9-15",  format = "%Y-%m-%d") # Fall Equinox

# Convert dates from any year to 2012 dates
d <- as.Date(strftime(DATES, format="2012-%m-%d"))

ifelse (d >= WS | d < SE, "Winter",
  ifelse (d >= SE & d < SS, "Spring",
    ifelse (d >= SS & d < FE, "Summer", "Fall")))
}

zz <- sample(1:10000,365)/1000
dag <- seq(as.Date("2014-01-01"), as.Date("2014-12-31"), by = "day")
seas <-  getSeason(dag)
test <- data.frame(zz,dag,seas)

ggplot(data=test, aes(x=dag,ymax=zz,ymin=0,fill=seas))+
geom_ribbon()

Solution

We can solve it by subsetting our data in two, above and below WS, and plotting two layers with geom_ribbon. Thus you convert the continuous WS series into two discrete sections.

library(dplyr)
ggplot() +
  geom_ribbon(data = filter(test, dag >= "2014-12-15") ,
              aes(x = dag, ymax = zz, ymin = 0, fill = seas)) +
  geom_ribbon(data = filter(test, dag < "2014-12-15") ,
              aes(x = dag, ymax = zz, ymin = 0, fill = seas))

Results

References

• Spanish version
• stackoverflow
• English posts