0

I am trying to transform a dataframe where I'd like multiple rows to be collapsed into a single column. I have pasted what the current data frame looks like, and what I want the final data frame to look, like further down.

The column "subject.number" is currently populated with a number from 1-9. The numbers are repeated multiple times depending on how many corresponding "sentiment.score" values that subject has.

I would like to end up with a data frame where each subject number is its own column, and that column is populated with its corresponding sentiment scores as rows.

Here is the original data frame

   subject.number sentiment.score
1               1            -1.0
2               1            -0.5
3               1             0.0
4               1             0.5
5               1             1.0
6               1            -1.0
7               1            -0.5
8               1             0.0
9               1             0.5
10              1             1.0
11              2            -1.0
12              2            -0.5
13              2             0.0
14              2             0.5
15              2             1.0
16              2            -1.0
17              2            -0.5
18              2             0.0
19              2             0.5
20              3             1.0
21              3            -1.0
22              3            -0.5
23              3             0.0
24              3             0.5
25              3             1.0
26              3            -1.0
27              3            -0.5
28              4             0.0
29              4             0.5
30              4             1.0
31              4            -1.0
32              4            -0.5
33              4             0.0
34              4             0.5
35              5             1.0
36              5            -1.0
37              5            -0.5
38              5             0.0
39              5             0.5
40              5             1.0
41              6            -1.0
42              6            -0.5
43              6             0.0
44              6             0.5
45              6             1.0
46              7            -1.0
47              7            -0.5
48              7             0.0
49              7             0.5
50              8             1.0
51              8            -1.0
52              8            -0.5
53              9             0.0
54              9             0.5

Here is what I want the final data frame to look like

     X1   X2   X3   X4   X5   X6   X7   X8  X9
1  -1.0 -1.0  1.0  0.0  1.0 -1.0 -1.0  1.0 0.0
2  -0.5 -0.5 -1.0  0.5 -1.0 -0.5 -0.5 -1.0 0.5
3   0.0  0.0 -0.5  1.0 -0.5  0.0  0.0 -0.5  NA
4   0.5  0.5  0.0 -1.0  0.0  0.5  0.5   NA  NA
5   1.0  1.0  0.5 -0.5  0.5  1.0   NA   NA  NA
6  -1.0 -1.0  1.0  0.0  1.0   NA   NA   NA  NA
7  -0.5 -0.5 -1.0  0.5   NA   NA   NA   NA  NA
8   0.0  0.0 -0.5   NA   NA   NA   NA   NA  NA
9   0.5  0.5   NA   NA   NA   NA   NA   NA  NA
10  1.0   NA   NA   NA   NA   NA   NA   NA  NA

Any help would be greatly appreciated. I thought maybe constructing a for loop could be good to solve this problem? But am unsure how to format it.

Also please excuse any weird formatting in my question - this is my first time posting on stackexchange so I'm still getting used to it.

Eva Bonning
  • 11
  • 3
  • Does this answer your question: https://stackoverflow.com/questions/63526716/reshape-data-using-pivot-wider-function – Dave2e Jun 28 '22 at 00:18
  • In your case, `library(tidyverse); df %>% group_by(subject.number) %>% mutate(n = 1:n()) %>% ungroup() %>% pivot_wider(names_from = subject.number, values_from = sentiment.score, names_prefix = "X")`. – Maurits Evers Jun 28 '22 at 01:25
  • Then add a `%>% select(-c(n))` to @MauritsEvers' code, to remove the intermediate 'n' column, and you're all done. – Caspar V. Jun 28 '22 at 01:50
  • @MauritsEvers thank you for your response, I tried your code and am getting the following errors Error: unexpected SPECIAL in "%>%" values_from = sentiment.score, Error: unexpected ',' in "values_from = sentiment.score," names_prefix = "X") Error: unexpected ')' in "names_prefix = "X")" – Eva Bonning Jun 28 '22 at 04:07
  • @EvaBonning Hmm, not easy to say. First thing to do is to verify that the code I give works for the sample data you gave. Then move on to your actual data. How is the actual data different from the sample data? Is there a typo in your code? Are column names of your actual data "unusual" in some way (e.g. they contain spaces or special characters)? – Maurits Evers Jun 28 '22 at 04:12
  • @MauritsEvers Working with the sample data, I think it's something to do with the ungroup() function. I can run: sentiment.analysis %>% group_by(subject.number) %>% mutate(n = 1:n()). Then when I run: sentiment.analysis %>% pivot_wider(names_from = subject.number, values_from = sentiment.score, names_prefix = "X"). Doing so produces an output, but with a new issue, where for example the column X1 has one row, that contains "c(-1, -0.5, 0, 0.5, 1, -1, -0.5, 0, 0.5, 1)", and X2 has one row, that contains "c(-1, -0.5, 0, 0.5, 1, -1, -0.5, 0)" and so on. – Eva Bonning Jun 28 '22 at 04:56
  • @MauritsEvers for clarification, sentiment.analysis is the name of my df – Eva Bonning Jun 28 '22 at 04:57
  • @EvaBonning I cannot reproduce your issues. When I use your sample data and the code from my second comment I reproduce your expected output. – Maurits Evers Jun 28 '22 at 07:00
  • Are you sure you're chaining the commands properly. For example, you cannot do `sentiment.analysis %>% group_by(subject.number) %>% mutate(n = 1:n())` and then `sentiment.analysis %>% pivot_wider(names_from = subject.number, values_from = sentiment.score, names_prefix = "X")`. This will not work as you're not carrying over the creation of the `n` column. Issues may also arise if you have multiple entries for one wide cell. Is that the case? Your sample data doesn't include those cases. The key is to compare your actual data with the sample data and spot the differences. – Maurits Evers Jun 28 '22 at 07:02

0 Answers0