2021-03-04

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

    References

    2021-03-01

    How to draw a stratified sample in R

    Problem

    We want to draw a stratified sample from a data frame in R.

    Solution

    Let's look at two examples, with one or several groups.

    One group

    We extract 3 records from each of the species: setosa, versicolor y virginica.

  • Base package
  • set.seed(1)
    iris1 <- lapply(split(iris, iris$Species), function(x) x[sample(nrow(x), 3), ])
    do.call("rbind", iris1) 
    
                  Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
    setosa.14              4.3         3.0          1.1         0.1     setosa
    setosa.19              5.7         3.8          1.7         0.3     setosa
    setosa.28              5.2         3.5          1.5         0.2     setosa
    versicolor.96          5.7         3.0          4.2         1.2 versicolor
    versicolor.60          5.2         2.7          3.9         1.4 versicolor
    versicolor.94          5.0         2.3          3.3         1.0 versicolor
    virginica.148          6.5         3.0          5.2         2.0  virginica
    virginica.133          6.4         2.8          5.6         2.2  virginica
    virginica.131          7.4         2.8          6.1         1.9  virginica
    
  • dplyr
  • library(dplyr)
    set.seed(1)
    iris %>%
      group_by(Species) %>%
      sample_n(., 3)
    
     Source: local data frame [9 x 5]
    Groups: Species
    
      Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
    1          4.3         3.0          1.1         0.1     setosa
    2          5.7         3.8          1.7         0.3     setosa
    3          5.2         3.5          1.5         0.2     setosa
    4          5.7         3.0          4.2         1.2 versicolor
    5          5.2         2.7          3.9         1.4 versicolor
    6          5.0         2.3          3.3         1.0 versicolor
    7          6.5         3.0          5.2         2.0  virginica
    8          6.4         2.8          5.6         2.2  virginica
    9          7.4         2.8          6.1         1.9  virginica
    
    Two groups

    For each number of cylinders (4, 6 u 8) we will extract 2 records with automatic transmission (am = 0) and 2 with manual transmission (am = 1).

  • Base package
  • set.seed(1)
    mtcars1 <- lapply(split(mtcars, list(mtcars$cyl, mtcars$am)), function(x) x[sample(nrow(x), 2), ])
    do.call("rbind", mtcars1) 
    
                           mpg cyl  disp  hp drat    wt  qsec vs am gear carb
    0.4.Merc 240D         24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
    0.4.Toyota Corona     21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
    1.4.Fiat X1-9         27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
    1.4.Lotus Europa      30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
    0.6.Hornet 4 Drive    21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
    0.6.Merc 280          19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
    1.6.Ferrari Dino      19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6
    1.6.Mazda RX4 Wag     21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
    0.8.Chrysler Imperial 14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4
    0.8.Hornet Sportabout 18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2
    1.8.Ford Pantera L    15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4
    1.8.Maserati Bora     15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8
    
  • dplyr
  • set.seed(1)
    mtcars %>%
      group_by(cyl, am) %>%
      sample_n(., 2)
    
    Source: local data frame [12 x 11]
    Groups: cyl, am
    
        mpg cyl  disp  hp drat    wt  qsec vs am gear carb
    1  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
    2  21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
    3  27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
    4  30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
    5  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
    6  19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
    7  19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6
    8  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
    9  14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4
    10 18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2
    11 15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4
    12 15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8
    

    Related posts

    References

    2021-02-09

    How to set tick labels to the edges of a continuous ggplot2 legend

    Title

    Problem

    We want to show the minimum and maximum values in the legend of our plot in ggplot2.

    library(ggplot2)
    d <- subset(diamonds, price >= 257 & price <= 8888)
    ggplot(d, aes(depth, carat, colour = price)) +
      geom_point() +
      scale_colour_gradient(limits = c(257, 8888))
    

    The legend shows values from 2.000 al 8.000 but we do not know the minimum and the maximum values.

    Solution

    Within scale_colour_gradient, we pass the arguments breaks and labels.

    ggplot(d, aes(depth, carat, colour = price)) +
      geom_point() +
      scale_colour_gradient(limits = c(257, 8888), 
                            breaks = c(257, 2000, 4000, 6000, 8000, 8888),
                            labels = c(257, 2000, 4000, 6000, 8000, 8888))
    

    Now we have added to the legend the minimum 257 and the maximum 8.888 values.

    Notes

    Instead of using the default colour scale colours from light blue to dark blue, we can adjust colour choices with the arguments low and high.

    ggplot(d, aes(depth, carat, colour = price)) +
      geom_point() +
      scale_colour_gradient(limits = c(257, 8888), 
                            breaks=c(257, 2000, 4000, 6000, 8000, 8888),
                            labels=c(257, 2000, 4000, 6000, 8000, 8888), low = "red")
    

    References

    2021-01-29

    Cómo crear una pirámide de población con ggplot2

    Title

    Problema

    Queremos crear una pirámide de población con ggplot2. Nuestro ejemplo es la población española por edad y sexo en 2020.

    Ejemplo

    # A tibble: 42 x 3
       Age      Gender   Total
               
     1 "0-4 "   male   1018039
     2 "0-4 "   female  963653
     3 "5-9"    male   1196380
     4 "5-9"    female 1129063
     5 "10-14"  male   1297635
     6 "10-14"  female 1225863
     7 "15-19 " male   1232566
     8 "15-19 " female 1156455
     9 "20-24 " male   1207902
    10 "20-24 " female 1152765
    # ... with 32 more rows
    

    df <- structure(list(Age = c("0-4 ", "0-4 ", "5-9", "5-9", "10-14", 
    "10-14", "15-19 ", "15-19 ", "20-24 ", "20-24 ", "25-29 ", "25-29 ", 
    "30-34 ", "30-34 ", "35-39 ", "35-39 ", "40-44 ", "40-44 ", "45-49 ", 
    "45-49 ", "50-54 ", "50-54 ", "55-59 ", "55-59 ", "60-64 ", "60-64 ", 
    "65-69 ", "65-69 ", "70-74 ", "70-74 ", "75-79 ", "75-79 ", "80-84 ", 
    "80-84 ", "85-89 ", "85-89 ", "90-94 ", "90-94 ", "95-99 ", "95-99 ", 
    "100+", "100+"), Gender = c("male", "female", "male", "female", 
    "male", "female", "male", "female", "male", "female", "male", 
    "female", "male", "female", "male", "female", "male", "female", 
    "male", "female", "male", "female", "male", "female", "male", 
    "female", "male", "female", "male", "female", "male", "female", 
    "male", "female", "male", "female", "male", "female", "male", 
    "female", "male", "female"), Total = c(1018039L, 963653L, 1196380L, 
    1129063L, 1297635L, 1225863L, 1232566L, 1156455L, 1207902L, 1152765L, 
    1308197L, 1275776L, 1421558L, 1417845L, 1702135L, 1688865L, 2024303L, 
    1971909L, 1968659L, 1926866L, 1828015L, 1840434L, 1652558L, 1712299L, 
    1410111L, 1502563L, 1153768L, 1270544L, 1020478L, 1191698L, 773823L, 
    974046L, 513692L, 759379L, 361702L, 634714L, 133032L, 302885L, 
    27305L, 84007L, 3732L, 13576L)), class = "data.frame", row.names = c(NA, 
    -42L))
    tibble(df)
    

    Solución

    Primero necesitamos crear dos columnas. En la primera cambiamos el signo de la población masculina para mostrarla invertida en el gráfico. En la segunda calculamos el porcentaje de población por edad y sexo.

    df <- df %>%
      group_by(Gender) %>% 
      mutate(
        Population = ifelse(Gender == "female", Total,-Total),
        Percent = ifelse(Gender == "female", 100 * (Total / sum(Total)),-100 * (Total / sum(Total))))
    df$Age <- factor(df$Age, levels=unique(df$Age)) # Para mantener el orden original de vector carácter
    

  • Totales
  • ggplot(df, aes(x = Age, Population, fill = Gender)) + 
      geom_bar(data = filter(df, Gender == "female"), stat = "identity") + 
      geom_bar(data = filter(df, Gender == "male"),  stat = "identity") + 
      scale_y_continuous(breaks = seq(-2000000, 2000000, 500000), labels = comma(abs(seq(-2000000, 2000000, 500000))))+
      coord_flip()
    

  • Porcentajes
  • ggplot(df, aes(x = Age, Percent, fill = Gender)) +
      geom_bar(data = filter(df, Gender == "female"), stat = "identity") +
      geom_bar(data = filter(df, Gender == "male"),  stat = "identity") +
      scale_y_continuous(breaks = seq(-10, 10, 2), labels = comma(abs(seq(-10, 10, 2)))) +
      coord_flip()
    

    Referencias

    2021-01-27

    How to create a population pyramid with ggplot2

    Title

    Problem

    We want to create a population pyramid with ggplot2. Our example is the Spanish population by age and gender in 2020.

    Example

    # A tibble: 42 x 3
       Age      Gender   Total
               
     1 "0-4 "   male   1018039
     2 "0-4 "   female  963653
     3 "5-9"    male   1196380
     4 "5-9"    female 1129063
     5 "10-14"  male   1297635
     6 "10-14"  female 1225863
     7 "15-19 " male   1232566
     8 "15-19 " female 1156455
     9 "20-24 " male   1207902
    10 "20-24 " female 1152765
    # ... with 32 more rows
    

    df <- structure(list(Age = c("0-4 ", "0-4 ", "5-9", "5-9", "10-14", 
    "10-14", "15-19 ", "15-19 ", "20-24 ", "20-24 ", "25-29 ", "25-29 ", 
    "30-34 ", "30-34 ", "35-39 ", "35-39 ", "40-44 ", "40-44 ", "45-49 ", 
    "45-49 ", "50-54 ", "50-54 ", "55-59 ", "55-59 ", "60-64 ", "60-64 ", 
    "65-69 ", "65-69 ", "70-74 ", "70-74 ", "75-79 ", "75-79 ", "80-84 ", 
    "80-84 ", "85-89 ", "85-89 ", "90-94 ", "90-94 ", "95-99 ", "95-99 ", 
    "100+", "100+"), Gender = c("male", "female", "male", "female", 
    "male", "female", "male", "female", "male", "female", "male", 
    "female", "male", "female", "male", "female", "male", "female", 
    "male", "female", "male", "female", "male", "female", "male", 
    "female", "male", "female", "male", "female", "male", "female", 
    "male", "female", "male", "female", "male", "female", "male", 
    "female", "male", "female"), Total = c(1018039L, 963653L, 1196380L, 
    1129063L, 1297635L, 1225863L, 1232566L, 1156455L, 1207902L, 1152765L, 
    1308197L, 1275776L, 1421558L, 1417845L, 1702135L, 1688865L, 2024303L, 
    1971909L, 1968659L, 1926866L, 1828015L, 1840434L, 1652558L, 1712299L, 
    1410111L, 1502563L, 1153768L, 1270544L, 1020478L, 1191698L, 773823L, 
    974046L, 513692L, 759379L, 361702L, 634714L, 133032L, 302885L, 
    27305L, 84007L, 3732L, 13576L)), class = "data.frame", row.names = c(NA, 
    -42L))
    tibble(df)
    

    Solution

    First we need to create two columns. One to convert male population to negative so it is reversed in the plot. Another column with the population percentage by age and sex.

    df <- df %>%
      group_by(Gender) %>% 
      mutate(
        Population = ifelse(Gender == "female", Total,-Total),
        Percent = ifelse(Gender == "female", 100 * (Total / sum(Total)),-100 * (Total / sum(Total))))
    df$Age <- factor(df$Age, levels=unique(df$Age)) # To keep the original order of the character vector
    

  • Absolute numbers
  • ggplot(df, aes(x = Age, Population, fill = Gender)) + 
      geom_bar(data = filter(df, Gender == "female"), stat = "identity") + 
      geom_bar(data = filter(df, Gender == "male"),  stat = "identity") + 
      scale_y_continuous(breaks = seq(-2000000, 2000000, 500000), labels = comma(abs(seq(-2000000, 2000000, 500000))))+
      coord_flip()
    

  • Percentage
  • ggplot(df, aes(x = Age, Percent, fill = Gender)) +
      geom_bar(data = filter(df, Gender == "female"), stat = "identity") +
      geom_bar(data = filter(df, Gender == "male"),  stat = "identity") +
      scale_y_continuous(breaks = seq(-10, 10, 2), labels = comma(abs(seq(-10, 10, 2)))) +
      coord_flip()
    

    References

    2021-01-06

    How to insert a picture in an Excel comment

    Title

    Problem

    We want to insert a picture in an Excel comment

    Solution

    1. Select cell, right click, and Insert Comment.
    2. Right click on the border of the comment and clic on Format Comment.
    3. On the Colors and Lines tab, click on the drop-down arrow for Color and select Fill Effects.
    4. On the picture tab, click on Select Picture.
    5. Select the picture, insert, and click OK.
    6. Resize the picture by draggin the border of the comment.
    7. Hide if you only want to show the picture when you hover over the cell.

    Related Posts

    Nube de datos