How can I gather data in a column depending on another value from another column-CodePudding

Because a table is worth a thousand words, here what I would like to do:

Column A	Column B	Column C	Column D
AMY	1.5	x1	y1
STR	2	x2	y2
AMY	4.5	x2	y3
STR	3	x3	y4

I would like to reunite all data from Column B in a Column depending on their value of Column A. And each Value of column A form a column Name. This, in a new dataframe.

AMY	STR
1.5	2
4.5	3

Thanks a lot !

I tried to make it using gather() and unite(), but it didnt give me the result i was expected.

CodePudding user response：

Using dplyr and tidyr you could do

library(dplyr)
library(tidyr)

df1 %>% 
  select(ColumnA, ColumnB) %>% 
  pivot_wider(names_from="ColumnA", values_from="ColumnB", values_fn = list) %>% 
  unnest(cols=everything())

CodePudding user response：

If the dataset have equal number of observations for ColumnA values, then select the columns and unstack in base R

unstack(df1[1:2], `Column B`~ `Column A`)
  AMY STR
1 1.5   2
2 4.5   3

Or as @onyambu mentioned, it can also work without the formula specified

unstack(df1[1:2])

data

df1 <- structure(list(`Column A` = c("AMY", "STR", "AMY", "STR"), 
`Column B` = c(1.5, 
2, 4.5, 3), `Column C` = c("x1", "x2", "x2", "x3"), `Column D` = c("y1", 
"y2", "y3", "y4")), class = "data.frame", 
row.names = c(NA, -4L
))