1

I'm working on a R Markdown file that I would like to submit as a manuscript to an academic journal. I would like to create a table that shows which three words (item2) co-occur most frequently with some keywords (item1). Note that some key words have more than three co-occurring words. The data that I am currently working with:

item1 <- c("water","water","water","water","water","sun","sun","sun","sun","moon","moon","moon")
item2 <- c("tree","dog","cat","fish","eagle","bird","table","bed","flower","house","desk","tiger")
n <- c("200","83","34","34","34","300","250","77","77","122","46","46")
df <- data.frame(item1,item2,n)

Which gives this dataframe:

   item1  item2   n
1  water   tree 200
2  water    dog  83
3  water    cat  34
4  water   fish  34
5  water  eagle  34
6    sun   bird 300
7    sun  table 250
8    sun    bed  77
9    sun flower  77
10  moon  house 122
11  moon   desk  46
12  moon  tiger  46

Ultimately, I would like to pass the data to the function papaja::apa_table, which requires a data.frame (or a matrix / list). I therefore need to reshape the data.

My question: How can I reshape the data (preferably with dplyr) to get the following structure? 

  water_item2 water_n sun_item2 sun_n moon_item2 moon_n
1        tree     200      bird   300      house    122
2         dog      83     table   250       desk     46
3         cat      34       bed    77      tiger     46
4        fish      34    flower    77       <NA>   <NA>
5       eagle      34      <NA>  <NA>       <NA>   <NA>
Waylan
  • 37,164
  • 12
  • 83
  • 109
iunda
  • 89
  • 7

1 Answers1

0

We can borrow an approach from an old answer of mine to a different question, and modify a classic gather(), unite(), spread() strategy by creating unique identifiers by group to avoid duplicate identifiers, then dropping that variable:

library(dplyr)
library(tidyr)

item1 <- c("water","water","water","water","water","sun","sun","sun","sun","moon","moon","moon")
item2 <- c("tree","dog","cat","fish","eagle","bird","table","bed","flower","house","desk","tiger")
n <- c("200","83","34","34","34","300","250","77","77","122","46","46")
# Owing to Richard Telford's excellent comment,
# I use data_frame() (or equivalently for our purposes,
# data.frame(..., stringsAsFactors = FALSE))
# to avoid turning the strings into factors

df <- data_frame(item1,item2,n)

df %>% 
    group_by(item1) %>%
    mutate(id = 1:n()) %>%
    ungroup() %>%
    gather(temp, val, item2, n) %>%
    unite(temp2, item1, temp, sep = '_') %>%
    spread(temp2, val) %>%
    select(-id)

# A tibble: 5 x 6
  moon_item2 moon_n sun_item2 sun_n water_item2 water_n
  <chr>      <chr>  <chr>     <chr> <chr>       <chr>  
1 house      122    bird      300   tree        200    
2 desk       46     table     250   dog         83     
3 tiger      46     bed       77    cat         34     
4 NA         NA     flower    77    fish        34     
5 NA         NA     NA        NA    eagle       34
duckmayr
  • 16,303
  • 3
  • 35
  • 53
  • 1
    if you use data_frame it won't turn strings into factors by default and you won't get the warning – Richard Telford Sep 22 '18 at 11:55
  • @duckmayr This is exactly what I was looking for. Thanks so much. I was trying for over a day and am very pleased with this solution. – iunda Sep 22 '18 at 16:17