Creating COUNTIF equivalent in R

[Forrestgump]

Hi there,

I have 2 columns in my data frame and I am trying to create a third column called 'ManagerCount'. In excel I can calculate using the Countif function using the column as the range and the cell as the criteria. I am to achieve this in rstudio however. Example below:-

Employee ID

EmployeeID	     ManagerID	  ManagerCount
80001	         NA	          1
81928	         54629	      1
76524	         54629   	  0
54629	         80001	      2
14973	         81928	      0

Any help would be greatly appreciated.

Regards,

Forrestgump

[FJCC]

Perhaps I am just slow this morning but I do not understand the logic of the ManagerCount column. What determines the values of 1, 0, and 2 that appear?

[Forrestgump]

Hi, It counts how many times the code in the 'EmployeeID' column appears in the 'ManagerID' column. So 80001 appears once in the ManagerID column, 54629 appears 2 times, 76524 and 14973 do not appear i.e. 0.

Regards,

Forrestgump

[FJCC]

Getting the result of how many times each ID appears in the ManagerID column is easy. Making that a new column in the original data frame is a little clunky because the counts of zero appear as NA after the left_join. It may be that the data frame DF2 is all you need or it may be there is a more elegant solution.

suppressPackageStartupMessages(library(dplyr))

DF <- data.frame(EmployeeID = c("80001", "81928", "76524", "54629", "14973"),
                 ManagerID = c(NA, "54629", "54629", "80001", "81928"))

DF2 <- DF %>% group_by(ManagerID) %>% summarize(ManagerCount = n())

DF <- left_join(DF, DF2, by = c("EmployeeID" = "ManagerID")) %>% 
  mutate(ManagerCount = ifelse(is.na(ManagerCount), 0, ManagerCount))
#> Warning: Column `EmployeeID`/`ManagerID` joining factors with different
#> levels, coercing to character vector
DF
#>   EmployeeID ManagerID ManagerCount
#> 1      80001      <NA>            1
#> 2      81928     54629            1
#> 3      76524     54629            0
#> 4      54629     80001            2
#> 5      14973     81928            0

^{Created on 2019-09-20 by the reprex package (v0.2.1)}

[Forrestgump]

I am using the code below to count how many time the id appears in the manager_id column :-

  mutate(ManagerCount = map_int(.x = WpA_Rscript$id,
                                        .f = ~ sum(WpA_Rscript$manager_id == .x,
                                                   na.rm = TRUE)))

However when I use this code i get the following error:-

Error in Ops.factor(WpA_Rscript$manager_gpid, .x) : 
  level sets of factors are different

Can anyone help me to understand what the issue is here?

KInd regards,
Forrestgump

[jcblum]

Hi @Forrestgump! Welcome!

Welcome! I'm afraid you'll need to supply some more info in order for helpers to be able to understand your problem.

Can you please try to compose a small, self-contained reproducible example that illustrates your problem? (follow that link for instructions and explanations) A reprex makes it much easier for others to understand your issue and figure out how to help.

Since you're new here, it might also be helpful to know how to properly format code and console output that you post. Using proper code formatting makes the site easier to read, prevents confusion (unformatted code can get garbled by the forum software ), and is generally considered the polite thing to do. Check out this FAQ to find out how — it's as easy as the click of a button! :

FAQ: How to format your code

Formatting Code: the Basics All code or console output you include in your posts should be formatted properly. Luckily, this is very easy to do! Just use the code formatting button at the top of the post editing box: [image] Select some code Click the </> button! The code formatting button automatically adds special Markdown formatting symbols to whatever you selected. If you check the preview pane to the right of your post, you'll see that the forum software now outputs nicely formatted code . [04] The code formatting button is pretty smart! It can tell the difference between snippets of code that are part of a sentence and blocks of code that stand alone. …

[FJCC]

Since you are showing only a part of your code and none of your data, this is something of a guess. The two columns you are comparing in the sum() function, id and manager_id, are factors but they do not have the same levels.
(The error message mentions the column manager_gpid. Is there a typo somewhere?)

Judging from your previous post, this makes sense, since not all employees are managers. You can fix this by making the two columns be of the type character.

WpA_Rscript %>%
  mutate(id = as.character(id), 
              manager_id = as.character(manager_id)) %>%
  mutate(ManagerCount = map_int(.x = id,
                                        .f = ~ sum(manager_id == .x,
                                                   na.rm = TRUE)))