How can I summarize an R dataset by values in a given column?

user_8700798 0
Viewed 65

I'm new to R and I've been doing ok so far but I need to do something a little complicated now and can't quite get it to work. I have a dataset similar to the following (going forward I will call this df):

df <- tribble(~name,             ~word,             ~N,
              "brandon",         "hello",            3,
               "john",           "test",             5,
               "jim",            "hello",            2,
               "brandon",        "goodbye",          2,
               "brandon",        "test",             1,
               "jim",            "goodbye",          4)

so far I have something like this going on:

temp_df <- df %>% mutate(
                     "hello" = ifelse(word == "hello", N, 0),
                     "goodbye" = ifelse(word == "goodbye", N, 0),
                     "test" = ifelse(word == "test", N, 0)
                  )

which is creating something like this:

name            hello           goodbye        test        word         N
brandon         3               0              0           hello        3
john            0               0              5           test         5
jim             2               0              0           hello        2
brandon         0               2              0           goodbye      2
brandon         0               0              1           test         1
jim             0               4              0           goodbye      4

but I need the df to look like this:

name            hello           goodbye        test
brandon         3               2              1
john            0               0              5
jim             2               4              0

I know how to select() the important data once I'm done here but I'm just not sure how to get all the data for each name into one row.

dplyr r
2 Answers

Using dplyr:

df %>%
  pivot_wider(id_cols="name", names_from="word", values_from="N", values_fill=0)

yields

# A tibble: 3 x 4
  name    hello  test goodbye
  <chr>   <dbl> <dbl>   <dbl>
1 brandon     3     1       2
2 john        0     5       0
3 jim         2     0       4
user_125052510

Data Frame

df <- tribble(~name,             ~word,             ~N,
              "brandon",         "hello",            3,
               "john",           "test",             5,
               "jim",            "hello",            2,
               "brandon",        "goodbye",          2,
               "brandon",        "test",             1,
               "jim",            "goodbye",          4)

Solution

library(dplyr) 
  df %>%
  pivot_wider(id_cols="name", names_from="word", values_from="N", 
  values_fill=0)

pivot_wider() "widens" data, increasing the number of columns and decreasing the number of rows. The inverse transformation is pivot_longer().

The help() function and ? help operator in R provide access to the documentation pages for R functions, data sets, and other objects, both for packages in the standard R distribution and for contributed packages. For example, help(pivot_wider) or ?pivot_wider.

Output

    name    hello   test    goodbye
    brandon 3       1       2   
    john    0       5       0   
    jim     2       0       4
user_133567300
Related