So here is part of an example dataset I'm working with:

   `D1`    `D2`        'D3'     `D4`     `D5`     `D6`     `D7`
   <chr>   <chr>      <dbl>    <dbl>    <dbl>    <dbl>    <dbl>
 1 921     917          935      457      462      451      465
 2 898     E9           914      446      452      440      455
 3 817     806          814      407      412      398      411
 4 644     632          624      321      327      314      324
 5 E9      399          385      207      213      200      206
 6 136     127          127       69       72       66       66
 7 223     233          209      117      106      117      118
 8 475     E9           443      239      234      238      246
 9 684     685          665      340      341      337      348
10 816     814          828      406      409      400      412
...

This is after I've worked with it a bit, and you can see the first two columns have a couple instances of "E9" in them, which is what I'm looking to count by running this:

df2 <- df %>%  select(-c(Time))
devices$Exclusions <- str_count(df2, "E9")

Here is my final result:

  Device        ID             Exclusions
  <chr>         <int>          <int>
1 D4            145287         14
2 D5            145286         16
3 D6            145285          0
4 D7            145284          0
5 D1            145280          0
6 D2            145277          0
7 D3            145278          0

So this leads me to my problem. The devices aren't necessarily in the same order and when it counts the instances of "E9" it is simply attaching them to the other dataframe in the order those devices are in, rather than matching them up with their names. What can I add in order to add that str_count from the D1 column to the D1 row in the other dataframe, rather than just the top row?

CodePudding user response：

Here's a solution in the tidyverse.

Solution

library(tidyverse)


# ...
# Code to generate 'df'.
# ...


df_counts <- df %>%
  # Homogenize columns as text.
  mutate(across(everything(), as.character)) %>%
  # Pivot columns into a 'Device | Code' format.
  pivot_longer(everything(), names_to = "Device", values_to = "Code") %>%
  # For each device...
  group_by(Device) %>%
  # ...count how many times "E9" appears among its codes.
  summarize(Exclusions = sum(Code == "E9"))

Speculating about the structure of your devices dataset, I can enrich the result with those IDs from your sample output:

# ...
# Code to generate 'devices'.
# ...


devices <- devices %>%
  full_join(df_counts, by = "Device", keep = FALSE)

Result

Given a df dataset like your example

df <- structure(
  list(
    D1 = c("921", "898", "817", "644", "E9", "136", "223", "475", "684", "816"),
    D2 = c("917", "E9", "806", "632", "399", "127", "233", "E9", "685", "814"),
    D3 = c(935, 914, 814, 624, 385, 127, 209, 443, 665, 828),
    D4 = c(457, 446, 407, 321, 207, 69, 117, 239, 340, 406),
    D5 = c(462, 452, 412, 327, 213, 72, 106, 234, 341, 409),
    D6 = c(451, 440, 398, 314, 200, 66, 117, 238, 337, 400),
    D7 = c(465, 455, 411, 324, 206, 66, 118, 246, 348, 412)
  ),
  class = c("tbl_df", "tbl", "data.frame"),
  row.names = c(NA, -10L)
)

this workflow should yield a result for df_counts like this:

# A tibble: 7 x 2
  Device Exclusions
  <chr>       <int>
1 D1              1
2 D2              2
3 D3              0
4 D4              0
5 D5              0
6 D6              0
7 D7              0

Furthermore, given a devices dataset like your example

devices <- structure(
  list(
    Device = c("D4", "D5", "D6", "D7", "D1", "D2", "D3"),
    ID = c(145287L, 145286L, 145285L, 145284L, 145280L, 145277L, 145278L)
  ),
  class = c("tbl_df", "tbl", "data.frame"),
  row.names = c(NA, -7L)
)

this solution should yield a devices dataset like this:

# A tibble: 7 x 3
  Device     ID Exclusions
  <chr>   <int>      <int>
1 D4     145287          0
2 D5     145286          0
3 D6     145285          0
4 D7     145284          0
5 D1     145280          1
6 D2     145277          2
7 D3     145278          0