From long to wide-CodePudding

I have some trouble converting my data.frame from long to wide.

My data looks like this:

Book  Word     Sentiment
AAA    word1     Fear
AAA    word2     Joy
BBB    word3     Trust
BBB    word4     Joy
CCC    word5     Trust
CCC    word6     Trust

I need to create this (considering the count of occurrences of the unique Sentiments and avoiding the 'word' column):

Book    Fear   Trust   Joy
 AAA     1       0      1
 BBB     0       1      1        
 CCC     0       2      0

I'm using R.

CodePudding user response：

A tidyverse approach would look like:

Book <- rep(c("AAA", "BBB", "CCC"), each = 2) 
Word <- paste0("word", 1:6)
Sentiment <- c("Fear", "Joy", "Trust", "Joy", "Trust", "Trust")

df <- data.frame(Book, Word, Sentiment)

df |>
  group_by(Book, Sentiment) |> 
  tally() |> 
  pivot_wider(names_from = Sentiment, values_from = n, values_fill = 0)

CodePudding user response：

    library(tidyverse)

# A tibble: 6 x 3
  book   word sentiment
  <chr> <int> <chr>    
1 AAA       1 joy      
2 AAA       2 trust    
3 BBB       3 trust    
4 BBB       4 joy      
5 CCC       5 trust    
6 CCC       6 fear     


spread(df, key = sentiment, 
        value = word, 
        fill = 0)

# A tibble: 3 x 4
  book   fear   joy trust
  <chr> <dbl> <dbl> <dbl>
1 AAA       2     1     0
2 BBB       0     4     3
3 CCC       0     6     5