Setting up dataframe in R

Question

I'm currently having some issues setting up my dataframe in a correct way. I would like to end up with the following columns Participant ID, SpeakerDialect (TSpeaker), SpeakerNumber(TSpeaker) and Score.

The output I'm getting from google forms is 4 columns of scores and one with the timestamp(participant ID). Now here comes the trouble. I would like to add some information about the video that they gave a score to the data frame. I made it work by using the following code - but here the Timestamp is not included. When adding the timestamp it completely messes it up. It is a repeated measures design, so the same timestamp will have to be repeated 4 times in the final dataframe


trustworth1 <- read.csv('Danskernes holdninger til politiske udsagn 1.csv')
trustworth1 <- trustworth1 %>% select(Hvor.troværdig.er.personen., Hvor.troværdig.er.personen..1, Hvor.troværdig.er.personen..2, Hvor.troværdig.er.personen..3)


TSpeaker <- c('2', '3', '4', '1')
TDialect <- c('1', '2', '2', '1')

trustworth1 <- trustworth1 %>% t()

trustworth1 <- cbind(TSpeaker, TDialect, trustworth1) %>% 
  as.tibble()

trustworth1 <- unite(trustworth1, Score, starts_with('V'), sep = ", ", remove = FALSE, na.rm = FALSE)

trustworth1 <- trustworth1 %>% select(TSpeaker,TDialect, Score)

trustworth1 <- separate_rows(trustworth1, c(Score), convert = FALSE)

Test dataframe

TimeStamp <- c(1, 2, 3, 4, 5, 6, 7)
Speaker1 <- c(4, 7, 9, 3, 2, 4, 9)
Speaker2 <- c(7, 1, 9, 0, 2, 5, 10)
Speaker3 <- c(3, 1, 9, 2, 9, 5, 10)
Speaker4 <- c(1, 1, 6, 0, 6, 5, 1)

df <- data.frame(TimeStamp, Speaker1, Speaker2, Speaker3, Speaker4)

Dialect of speaker 1 is 1

Dialect of speaker 2 is 2

Dialect of speaker 3 is 1

Dialect of speaker 4 is 2

Ideally I would end up with a data frame with 4 rows per participant, one for each rating of the speakers

RAW DATA:

TimeStamp
<chr>
Speaker2
<int>
Speaker3
<int>
Speaker4
<int>
Speaker1
<int>
1   2020/12/07 11:33:39 AM CET  3   8   6   9
2   2020/12/07 12:16:33 PM CET  5   5   5   5
3   2020/12/07 12:29:11 PM CET  6   7   8   9
4   2020/12/07 12:47:39 PM CET  7   8   8   9
5   2020/12/07 1:04:01 PM CET   5   5   5   5
6   2020/12/07 1:05:33 PM CET   0   8   9   5
6 rows

Any ideas?

Hello Laura Paulsen, welcome to SO. Can you please run `dput(trustworth1[1:10])` so we can see the content of the dataframe. (that is if you are comfortable with showing the data, alternatively you can create a small data frame for the purposes of this question). — Mouad_Seridi, Dec 17 '20 at 12:16
Thank you @Mouad_Seridi . I just added some screenshots of the data frames to the question! — Laura Paulsen, Dec 17 '20 at 12:28
Please do not post an image of code/data/errors: it cannot be copied or searched (SEO), it breaks screen-readers, and it may not fit well on some mobile devices. Ref: https://meta.stackoverflow.com/a/285557 (and https://xkcd.com/2116/). (Personally, I will spend very little time on questions where the asker expects me to transcribe their data into something usable. It's a combination of time-available and matched effort.) Please just include the code, console output, or data (e.g., `data.frame(...)` or the output from `dput(head(x))`) directly. — r2evans, Dec 17 '20 at 12:31
Hey @r2evans Thank you for letting me know! Still a bit new to this whole coding thing! — Laura Paulsen, Dec 17 '20 at 12:51
Yeah, it's a common first thing. On SO, a lot of stress is put on "reproducibility" and "self-contained". Often the question can be resolved without that, but when there is ambiguity many of us are fast to request (demand, even) some particular things in the question. There is a great Q/A on SO about making reproducible questions, and while not everything there applies to every question, it is often good guidance for learning how SO often prefers questions to be structured: https://stackoverflow.com/q/5963269. Thanks! — r2evans, Dec 17 '20 at 12:54
@r2evans Thank you! I'll definitely look into those Q/A's! And thank you for solving my issue! — Laura Paulsen, Dec 17 '20 at 13:16

score 0 · Answer 1 · answered Dec 17 '20 at 12:56

using the sample dataset, I think you want something like this ? you can use 'speaker' 'score' instead of var and val

require(dplyr)
require(tidyr)

df %>% head 
df %>%  gather(var, val, Speaker1:Speaker4) %>%  
  head 

TimeStamp      var val
1         1 Speaker1   4
2         2 Speaker1   7
3         3 Speaker1   9
4         4 Speaker1   3
5         5 Speaker1   2
6         6 Speaker1   4

score 0 · Accepted Answer · answered Dec 17 '20 at 13:00

Here's a dplyr solution.

To bring in the dialect, I suggest using a merge/join operation, which will pair a dialect with every (known) speaker number. For that data, I'll use a frame as well:

dialects <- data.frame(SpeakerNumber = paste0("Speaker", 1:4), SpeakerDialect = c(1L, 2L, 1L, 2L))

Now it's a matter of reshaping/pivoting from a "wide" format to a "long" format:

library(dplyr)
library(tidyr) # pivot_longer

pivot_longer(df, -TimeStamp, names_to = "SpeakerNumber", values_to = "Score") %>%
  left_join(dialects, by = "SpeakerNumber")
# # A tibble: 28 x 4
#    TimeStamp SpeakerNumber Score SpeakerDialect
#        <dbl> <chr>         <dbl>          <int>
#  1         1 Speaker1          4              1
#  2         1 Speaker2          7              2
#  3         1 Speaker3          3              1
#  4         1 Speaker4          1              2
#  5         2 Speaker1          7              1
#  6         2 Speaker2          1              2
#  7         2 Speaker3          1              1
#  8         2 Speaker4          1              2
#  9         3 Speaker1          9              1
# 10         3 Speaker2          9              2
# # ... with 18 more rows

You use the name SpeakerNumber, suggesting you only want the number from that field, and perhaps as a number itself. If that's the case, add

... %>%
  mutate(SpeakerNumber = as.integer(gsub("^Speaker", "", SpeakerNumber)))

Setting up dataframe in R

2 Answers2