How do I classify a variable based on aggregated data from another df?

tidyverse
1

Hello,

Please help.
I am looking to create a new variable based on aggregated data.
One same director appears on multiple rows and is called both "small director" or "big director".

I want to make this consistent and that if a director has more than one row as "big director" then = "TOP" for each row in a new variable. If not = "SMALL"
eg:
dir X, small director, TOP
dir X, big director, TOP
dir X, big director, TOP
dir A, big director, SMALL
dir A, small director, SMALL

I tried to create a new df where I want a condition that if the director is in this df then he/she should have a variable TOP for each row the director appears.

I am clueless how to do this, I tried some sort of code that runs but is wrong.

Please kindly help

Thank you
Christine

reprex::reprex_info()
#> Warning in as.POSIXlt.POSIXct(Sys.time()): unknown timezone 'zone/tz/2017c.
#> 1.0/zoneinfo/Europe/Paris'
#> Created by the reprex package v0.1.1.9000 on 2018-01-02

library(tidyverse)
#> Loading tidyverse: ggplot2
#> Loading tidyverse: tibble
#> Loading tidyverse: tidyr
#> Loading tidyverse: readr
#> Loading tidyverse: purrr
#> Loading tidyverse: dplyr
#> Warning: package 'tidyr' was built under R version 3.4.2
#> Warning: package 'purrr' was built under R version 3.4.2
#> Warning: package 'dplyr' was built under R version 3.4.2
#> Conflicts with tidy packages ----------------------------------------------
#> filter(): dplyr, stats
#> lag():    dplyr, stats

movie_frame<- data.frame(tribble(
                             ~director,    ~director_test,
                           "Spike Lee",    "big director",
                        "Bong Joon-ho",    "big director",
                    "Barbet Schroeder",  "small director",
                       "George Miller",  "small director",
                "Michael Winterbottom",    "big director",
                       "Justin Kurzel",  "small director",
                         "Travis Fine",    "big director",
                 "Phil Alden Robinson",  "small director",
                  "John Mallory Asher",  "small director",
                     "Peter Greenaway",    "big director",
                         "Bart Layton",    "big director",
                       "Fred Schepisi",  "small director",
                    "Gabriele Muccino",    "big director",
                       "Samuel Fuller",    "big director",
                     "Robert Townsend",    "big director",
                          "Mark Joffe",  "small director",
                        "Dominic Sena",  "small director",
                        "Abel Ferrara",  "small director",
                       "Werner Herzog",    "big director",
                          "Wes Craven",  "small director",
                    "Richard Marquand",  "small director",
                       "Daniel Petrie",  "small director",
                    "William Friedkin",  "small director",
                      "Clint Eastwood",  "small director",
                       "Steven Knight",    "big director",
                           "Alan Alda",  "small director",
                      "Clint Eastwood",  "small director",
                           "Sam Raimi",    "big director",
                         "Stevan Mena",  "small director",
                       "Roger Michell",  "small director",
                                    NA,    "big director",
                        "Tony Goldwyn",    "big director",
                          "Jim Hickey",  "small director",
                  "Brandon Cronenberg",  "small director",
                         "John Milius",  "small director",
                        "Renny Harlin",  "small director",
                     "Martin Scorsese",    "big director",
                       "Phillip Noyce",    "big director",
                      "Paul Verhoeven",    "big director",
                     "Charles Jarrott",  "small director",
                            "Don Roos",  "small director",
                         "Jim McBride",  "small director",
                 "Michael Caton-Jones",    "big director",
                     "Bob Clark (III)",  "small director",
                    "Tony Cinciripini",  "small director",
                         "Tamra Davis",    "big director",
                           "Nick Love",    "big director",
                     "Jonathan Kaplan",    "big director",
                       "Robert Benton",  "small director",
                     "George Roy Hill",  "small director",
                         "Woody Allen",    "big director",
                            "Uwe Boll",  "small director",
                        "Liza Azuelos",  "small director",
                   "Penelope Spheeris",  "small director",
                       "James Bridges",  "small director",
               "Mary Stuart Masterson",  "small director",
                         "Woody Allen",    "big director",
                         "Danny Boyle",    "big director",
                       "Jane Anderson",    "big director",
                         "Dave Thomas",    "big director",
                        "Steve Barron",  "small director",
                             "Ang Lee",  "small director",
                         "John Waters",    "big director",
                    "Christopher Cain",  "small director",
                        "Sidney Lumet",    "big director",
                   "Gillian Armstrong",  "small director",
                  "Roger Spottiswoode",    "big director",
                      "Richard Pearce",  "small director",
                      "Richard Lester",    "big director",
                          "Wayne Wang",  "small director",
                     "Gavin O\'Connor",    "big director",
                       "Peter Sollett",    "big director",
                        "Rory Kennedy",    "big director",
                         "Ken Shapiro",  "small director",
                        "Sidney Lumet",  "small director",
                        "Debra Granik",    "big director",
                     "Mark Pellington",  "small director",
                         "Tommy Davis",    "big director",
                 "Christopher Hampton",  "small director",
                       "Andrew Niccol",  "small director",
                    "Steven M. Martin",    "big director",
            "Daisy von Scherler Mayer",  "small director",
                          "Luc Besson",    "big director",
                         "James Ivory",    "big director",
                     "Christine Jeffs",    "big director",
                        "Oliver Stone",    "big director",
                            "Eli Roth",  "small director",
                "Fraser Clarke Heston",  "small director",
                        "Andre Heller",    "big director",
                        "Andrew Davis",  "small director",
                         "Jan de Bont",    "big director",
                      "Harmony Korine",  "small director",
               "Sean MacLeod Phillips",    "big director",
                         "Judd Apatow",  "small director",
                         "Woody Allen",    "big director",
                       "Alexandre Aja",  "small director",
                       "Harold Becker",    "big director",
                          "Sean Ellis",    "big director",
                          "Shawn Levy",    "big director",
                    "Yurek Bogayevicz",  "small director"
           ))

Summary_big_D<- movie_frame%>% 
filter(director_test=="big director") %>% 
select(director, director_test) %>% 
group_by(director, director_test) %>% 
summarise( count= n()) %>% 
arrange(desc(count))

movie_frame %>% 
mutate(TOP= ifelse(Summary_big_D$director==movie_frame$director, "TOP", "SMALL"))
#> Warning in Summary_big_D$director == movie_frame$director: longer object
#> length is not a multiple of shorter object length
#>                     director  director_test   TOP
#> 1                  Spike Lee   big director SMALL
#> 2               Bong Joon-ho   big director SMALL
#> 3           Barbet Schroeder small director SMALL
#> 4              George Miller small director SMALL
#> 5       Michael Winterbottom   big director SMALL
#> 6              Justin Kurzel small director SMALL
#> 7                Travis Fine   big director SMALL
#> 8        Phil Alden Robinson small director SMALL
#> 9         John Mallory Asher small director SMALL
#> 10           Peter Greenaway   big director SMALL
#> 11               Bart Layton   big director SMALL
#> 12             Fred Schepisi small director SMALL
#> 13          Gabriele Muccino   big director SMALL
#> 14             Samuel Fuller   big director SMALL
#> 15           Robert Townsend   big director SMALL
#> 16                Mark Joffe small director SMALL
#> 17              Dominic Sena small director SMALL
#> 18              Abel Ferrara small director SMALL
#> 19             Werner Herzog   big director SMALL
#> 20                Wes Craven small director SMALL
#> 21          Richard Marquand small director SMALL
#> 22             Daniel Petrie small director SMALL
#> 23          William Friedkin small director SMALL
#> 24            Clint Eastwood small director SMALL
#> 25             Steven Knight   big director SMALL
#> 26                 Alan Alda small director SMALL
#> 27            Clint Eastwood small director SMALL
#> 28                 Sam Raimi   big director SMALL
#> 29               Stevan Mena small director SMALL
#> 30             Roger Michell small director SMALL
#> 31                      <NA>   big director  <NA>
#> 32              Tony Goldwyn   big director SMALL
#> 33                Jim Hickey small director SMALL
#> 34        Brandon Cronenberg small director SMALL
#> 35               John Milius small director SMALL
#> 36              Renny Harlin small director SMALL
#> 37           Martin Scorsese   big director SMALL
#> 38             Phillip Noyce   big director SMALL
#> 39            Paul Verhoeven   big director SMALL
#> 40           Charles Jarrott small director SMALL
#> 41                  Don Roos small director SMALL
#> 42               Jim McBride small director SMALL
#> 43       Michael Caton-Jones   big director SMALL
#> 44           Bob Clark (III) small director SMALL
#> 45          Tony Cinciripini small director  <NA>
#> 46               Tamra Davis   big director SMALL
#> 47                 Nick Love   big director SMALL
#> 48           Jonathan Kaplan   big director SMALL
#> 49             Robert Benton small director SMALL
#> 50           George Roy Hill small director SMALL
#> 51               Woody Allen   big director SMALL
#> 52                  Uwe Boll small director SMALL
#> 53              Liza Azuelos small director SMALL
#> 54         Penelope Spheeris small director SMALL
#> 55             James Bridges small director SMALL
#> 56     Mary Stuart Masterson small director SMALL
#> 57               Woody Allen   big director SMALL
#> 58               Danny Boyle   big director SMALL
#> 59             Jane Anderson   big director   TOP
#> 60               Dave Thomas   big director SMALL
#> 61              Steve Barron small director SMALL
#> 62                   Ang Lee small director SMALL
#> 63               John Waters   big director SMALL
#> 64          Christopher Cain small director SMALL
#> 65              Sidney Lumet   big director SMALL
#> 66         Gillian Armstrong small director SMALL
#> 67        Roger Spottiswoode   big director SMALL
#> 68            Richard Pearce small director SMALL
#> 69            Richard Lester   big director SMALL
#> 70                Wayne Wang small director SMALL
#> 71            Gavin O'Connor   big director SMALL
#> 72             Peter Sollett   big director SMALL
#> 73              Rory Kennedy   big director SMALL
#> 74               Ken Shapiro small director SMALL
#> 75              Sidney Lumet small director SMALL
#> 76              Debra Granik   big director SMALL
#> 77           Mark Pellington small director SMALL
#> 78               Tommy Davis   big director SMALL
#> 79       Christopher Hampton small director SMALL
#> 80             Andrew Niccol small director SMALL
#> 81          Steven M. Martin   big director SMALL
#> 82  Daisy von Scherler Mayer small director SMALL
#> 83                Luc Besson   big director SMALL
#> 84               James Ivory   big director SMALL
#> 85           Christine Jeffs   big director SMALL
#> 86              Oliver Stone   big director SMALL
#> 87                  Eli Roth small director SMALL
#> 88      Fraser Clarke Heston small director SMALL
#> 89              Andre Heller   big director SMALL
#> 90              Andrew Davis small director  <NA>
#> 91               Jan de Bont   big director SMALL
#> 92            Harmony Korine small director SMALL
#> 93     Sean MacLeod Phillips   big director SMALL
#> 94               Judd Apatow small director SMALL
#> 95               Woody Allen   big director SMALL
#> 96             Alexandre Aja small director SMALL
#> 97             Harold Becker   big director SMALL
#> 98                Sean Ellis   big director SMALL
#> 99                Shawn Levy   big director SMALL
#> 100         Yurek Bogayevicz small director SMALL
2

If I understand your requirements that a director has to be big more than once this produces the output you want.

Also it would help if you pointed out specifically what was wrong with the output you produced that would help.

suppressPackageStartupMessages(library(tidyverse))
movie_frame<- data.frame(tribble(
    ~director,    ~director_test,
    "Spike Lee",    "big director",
    "Bong Joon-ho",    "big director",
    "Barbet Schroeder",  "small director",
    "George Miller",  "small director",
    "Michael Winterbottom",    "big director",
    "Justin Kurzel",  "small director",
    "Travis Fine",    "big director",
    "Phil Alden Robinson",  "small director",
    "John Mallory Asher",  "small director",
    "Peter Greenaway",    "big director",
    "Bart Layton",    "big director",
    "Fred Schepisi",  "small director",
    "Gabriele Muccino",    "big director",
    "Samuel Fuller",    "big director",
    "Robert Townsend",    "big director",
    "Mark Joffe",  "small director",
    "Dominic Sena",  "small director",
    "Abel Ferrara",  "small director",
    "Werner Herzog",    "big director",
    "Wes Craven",  "small director",
    "Richard Marquand",  "small director",
    "Daniel Petrie",  "small director",
    "William Friedkin",  "small director",
    "Clint Eastwood",  "small director",
    "Steven Knight",    "big director",
    "Alan Alda",  "small director",
    "Clint Eastwood",  "small director",
    "Sam Raimi",    "big director",
    "Stevan Mena",  "small director",
    "Roger Michell",  "small director",
    NA,    "big director",
    "Tony Goldwyn",    "big director",
    "Jim Hickey",  "small director",
    "Brandon Cronenberg",  "small director",
    "John Milius",  "small director",
    "Renny Harlin",  "small director",
    "Martin Scorsese",    "big director",
    "Phillip Noyce",    "big director",
    "Paul Verhoeven",    "big director",
    "Charles Jarrott",  "small director",
    "Don Roos",  "small director",
    "Jim McBride",  "small director",
    "Michael Caton-Jones",    "big director",
    "Bob Clark (III)",  "small director",
    "Tony Cinciripini",  "small director",
    "Tamra Davis",    "big director",
    "Nick Love",    "big director",
    "Jonathan Kaplan",    "big director",
    "Robert Benton",  "small director",
    "George Roy Hill",  "small director",
    "Woody Allen",    "big director",
    "Uwe Boll",  "small director",
    "Liza Azuelos",  "small director",
    "Penelope Spheeris",  "small director",
    "James Bridges",  "small director",
    "Mary Stuart Masterson",  "small director",
    "Woody Allen",    "big director",
    "Danny Boyle",    "big director",
    "Jane Anderson",    "big director",
    "Dave Thomas",    "big director",
    "Steve Barron",  "small director",
    "Ang Lee",  "small director",
    "John Waters",    "big director",
    "Christopher Cain",  "small director",
    "Sidney Lumet",    "big director",
    "Gillian Armstrong",  "small director",
    "Roger Spottiswoode",    "big director",
    "Richard Pearce",  "small director",
    "Richard Lester",    "big director",
    "Wayne Wang",  "small director",
    "Gavin O\'Connor",    "big director",
    "Peter Sollett",    "big director",
    "Rory Kennedy",    "big director",
    "Ken Shapiro",  "small director",
    "Sidney Lumet",  "small director",
    "Debra Granik",    "big director",
    "Mark Pellington",  "small director",
    "Tommy Davis",    "big director",
    "Christopher Hampton",  "small director",
    "Andrew Niccol",  "small director",
    "Steven M. Martin",    "big director",
    "Daisy von Scherler Mayer",  "small director",
    "Luc Besson",    "big director",
    "James Ivory",    "big director",
    "Christine Jeffs",    "big director",
    "Oliver Stone",    "big director",
    "Eli Roth",  "small director",
    "Fraser Clarke Heston",  "small director",
    "Andre Heller",    "big director",
    "Andrew Davis",  "small director",
    "Jan de Bont",    "big director",
    "Harmony Korine",  "small director",
    "Sean MacLeod Phillips",    "big director",
    "Judd Apatow",  "small director",
    "Woody Allen",    "big director",
    "Alexandre Aja",  "small director",
    "Harold Becker",    "big director",
    "Sean Ellis",    "big director",
    "Shawn Levy",    "big director",
    "Yurek Bogayevicz",  "small director"
))

big_directors <- movie_frame %>%
    group_by(director) %>%
    count(director_test) %>%
    filter(director_test == "big director", n > 1) %>%
    select(director)

new_movie_frame <- mutate(movie_frame,
             size = if_else(director %in% big_directors$director, "BIG", "SMALL"))

new_movie_frame
#>                     director  director_test  size
#> 1                  Spike Lee   big director SMALL
#> 2               Bong Joon-ho   big director SMALL
#> 3           Barbet Schroeder small director SMALL
#> 4              George Miller small director SMALL
#> 5       Michael Winterbottom   big director SMALL
#> 6              Justin Kurzel small director SMALL
#> 7                Travis Fine   big director SMALL
#> 8        Phil Alden Robinson small director SMALL
#> 9         John Mallory Asher small director SMALL
#> 10           Peter Greenaway   big director SMALL
#> 11               Bart Layton   big director SMALL
#> 12             Fred Schepisi small director SMALL
#> 13          Gabriele Muccino   big director SMALL
#> 14             Samuel Fuller   big director SMALL
#> 15           Robert Townsend   big director SMALL
#> 16                Mark Joffe small director SMALL
#> 17              Dominic Sena small director SMALL
#> 18              Abel Ferrara small director SMALL
#> 19             Werner Herzog   big director SMALL
#> 20                Wes Craven small director SMALL
#> 21          Richard Marquand small director SMALL
#> 22             Daniel Petrie small director SMALL
#> 23          William Friedkin small director SMALL
#> 24            Clint Eastwood small director SMALL
#> 25             Steven Knight   big director SMALL
#> 26                 Alan Alda small director SMALL
#> 27            Clint Eastwood small director SMALL
#> 28                 Sam Raimi   big director SMALL
#> 29               Stevan Mena small director SMALL
#> 30             Roger Michell small director SMALL
#> 31                      <NA>   big director SMALL
#> 32              Tony Goldwyn   big director SMALL
#> 33                Jim Hickey small director SMALL
#> 34        Brandon Cronenberg small director SMALL
#> 35               John Milius small director SMALL
#> 36              Renny Harlin small director SMALL
#> 37           Martin Scorsese   big director SMALL
#> 38             Phillip Noyce   big director SMALL
#> 39            Paul Verhoeven   big director SMALL
#> 40           Charles Jarrott small director SMALL
#> 41                  Don Roos small director SMALL
#> 42               Jim McBride small director SMALL
#> 43       Michael Caton-Jones   big director SMALL
#> 44           Bob Clark (III) small director SMALL
#> 45          Tony Cinciripini small director SMALL
#> 46               Tamra Davis   big director SMALL
#> 47                 Nick Love   big director SMALL
#> 48           Jonathan Kaplan   big director SMALL
#> 49             Robert Benton small director SMALL
#> 50           George Roy Hill small director SMALL
#> 51               Woody Allen   big director   BIG
#> 52                  Uwe Boll small director SMALL
#> 53              Liza Azuelos small director SMALL
#> 54         Penelope Spheeris small director SMALL
#> 55             James Bridges small director SMALL
#> 56     Mary Stuart Masterson small director SMALL
#> 57               Woody Allen   big director   BIG
#> 58               Danny Boyle   big director SMALL
#> 59             Jane Anderson   big director SMALL
#> 60               Dave Thomas   big director SMALL
#> 61              Steve Barron small director SMALL
#> 62                   Ang Lee small director SMALL
#> 63               John Waters   big director SMALL
#> 64          Christopher Cain small director SMALL
#> 65              Sidney Lumet   big director SMALL
#> 66         Gillian Armstrong small director SMALL
#> 67        Roger Spottiswoode   big director SMALL
#> 68            Richard Pearce small director SMALL
#> 69            Richard Lester   big director SMALL
#> 70                Wayne Wang small director SMALL
#> 71            Gavin O'Connor   big director SMALL
#> 72             Peter Sollett   big director SMALL
#> 73              Rory Kennedy   big director SMALL
#> 74               Ken Shapiro small director SMALL
#> 75              Sidney Lumet small director SMALL
#> 76              Debra Granik   big director SMALL
#> 77           Mark Pellington small director SMALL
#> 78               Tommy Davis   big director SMALL
#> 79       Christopher Hampton small director SMALL
#> 80             Andrew Niccol small director SMALL
#> 81          Steven M. Martin   big director SMALL
#> 82  Daisy von Scherler Mayer small director SMALL
#> 83                Luc Besson   big director SMALL
#> 84               James Ivory   big director SMALL
#> 85           Christine Jeffs   big director SMALL
#> 86              Oliver Stone   big director SMALL
#> 87                  Eli Roth small director SMALL
#> 88      Fraser Clarke Heston small director SMALL
#> 89              Andre Heller   big director SMALL
#> 90              Andrew Davis small director SMALL
#> 91               Jan de Bont   big director SMALL
#> 92            Harmony Korine small director SMALL
#> 93     Sean MacLeod Phillips   big director SMALL
#> 94               Judd Apatow small director SMALL
#> 95               Woody Allen   big director   BIG
#> 96             Alexandre Aja small director SMALL
#> 97             Harold Becker   big director SMALL
#> 98                Sean Ellis   big director SMALL
#> 99                Shawn Levy   big director SMALL
#> 100         Yurek Bogayevicz small director SMALL
2 Likes
3

Yes correct, a director has to be big more than once.
My output results in a variable that does not give me TOP for each row as new variable. My code yields only SMALL with one row as TOP. In my reprex example Woody Allen should appear with a new column with 3 rows TOP.

52
52691×417 40.2 KB

42

4

Yup, my approach is to create a table with the aggregate results and then I use rigth_join() to compare it back to the original table, it returns 3 rows with Woody Allen

movie_test <- movie_frame %>%
  group_by(director, director_test) %>%
  filter(director_test == "big director") %>%
  tally() %>%
  filter(n > 1) %>%
  mutate(top = "top") %>%
  select(director, top) %>%
  right_join(movie_frame, by = "director") %>%
  mutate(top = ifelse(is.na(top), "small", top))


movie_test %>%
  filter(top == "top")

# A tibble: 3 x 3
# Groups: director [1]
  director    top   director_test
  <chr>       <chr> <chr>        
1 Woody Allen top   big director 
2 Woody Allen top   big director 
3 Woody Allen top   big director
2 Likes
5

I called them BIG and SMALL instead of TOP and SMALL, but I think my sample produces the results you want then.

1 Like
6

Thank you so much I just saw that it was working perfect!

7

Thats excellent, thank you I now learnt how to join properly!:slight_smile:

1 Like
8

After years of writing SQL queries I'm finally beginning to internalize dplyr... it's pretty neat!

3 Likes