Reshaping a dataframe with partial aggregation

Question

I have read several questions similar to mine but none actually answered my dilemma (so to speak). I have a dataset composed of 2 columns: on the first column there are x numbers of usernames repeated 100 times each (i.e. if there are 4 usernames these generates 400 rows under a column named "user") and under column 2 there are non-unique strings corresponding to each username. I want to transpose the UNIQUE usernames from rows to columns but maintaining all the values in col2 without aggregation. For simplicity I add below a small sample from iris dataset. In essence I would like to reshape this:

Species Petal.Width
setosa         0.2
setosa         0.2
setosa         0.4
versicolor     1.4
versicolor     1.3
versicolor     1.0

To this:

setosa  versicolor
0.2       1.4
0.2       1.3
0.4       1.0

without having to manually subsetting the data, as there will be several usernames and the code may be tediously long to complete. It is actually a replacement of columns (from Species to setosa and versicolor in the example above). Could someone please let me know if there is a quick way to achieve this result? Thanks!

Please don't put screen shots. It is really a bad way to share data. — www, Nov 04 '18 at 14:30
In addition to the answers in the link: `unstack(d, Petal.Width ~ Species)` — Henrik, Nov 04 '18 at 14:45
Hi Henrik, as per your request I will be happy to edit my question, but can you please pinpoint the link where the question I asked was answered? I ran through different forums but none of the proposed answers (including transpose functions, merge, cast, etc.) solved my issue which was brilliantly answered by www. Thanks — AlGrasso, Nov 04 '18 at 15:15

score 1 · Answer 1 · answered Nov 04 '18 at 14:34

We can use dplyr and tidyr to convert the data frame.

library(dplyr)
library(tidyr)

dat2 <- dat %>%
  group_by(Species) %>%
  mutate(ID = 1:n()) %>%
  spread(Species, Petal.Width) %>%
  select(-ID)
dat2
# # A tibble: 3 x 2
#   setosa versicolor
#    <dbl>      <dbl>
# 1    0.2        1.4
# 2    0.2        1.3
# 3    0.4        1

Data

dat <- read.table(text = "Species Petal.Width
setosa         0.2
                  setosa         0.2
                  setosa         0.4
                  versicolor     1.4
                  versicolor     1.3
                  versicolor     1.0",
                  header = TRUE, stringsAsFactors = FALSE)

Thanks www. That was 100% appropriate. dat2 did the job and I had no prior knowledge of those mutate and spread functions. The only addition I made was to add the line as.data.frame(dat2). Thank you! — AlGrasso, Nov 04 '18 at 14:45

Reshaping a dataframe with partial aggregation

1 Answers1