I have a dataframe that looks something like this
NUM <- c("45", "45", "45", "45", "48", "50", "66", "66", "66", "68")
Type <- c("A", "F", "C", "B", "D", "A", "E", "C", "F", "D")
Points <- c(9.2,60.8,22.9,1012.7,18.7,11.1,67.2,63.1,16.7,58.4)
df1 <- data.frame(NUM,Type,Points)
df1:
+-----+------+--------+
| NUM | TYPE | Points |
+-----+------+--------+
| 45 | A | 9.2 |
| 45 | F | 60.8 |
| 45 | C | 22.9 |
| 45 | B | 1012.7 |
| 48 | D | 18.7 |
| 50 | A | 11.1 |
| 66 | E | 67.2 |
| 66 | C | 63.1 |
| 66 | F | 16.7 |
| 65 | D | 58.4 |
+-----+------+--------+
I am trying to obtain an output that takes the rows in type column to convert it to individual columns.
Desired Output:
+-----+----------+----------+----------+----------+----------+----------+
| NUM | Points.A | Points.B | Points.C | Points.D | Points.E | Points.F |
+-----+----------+----------+----------+----------+----------+----------+
| 45 | 9.2 | 1012.7 | 22.9 | N/A | N/A | 60.8 |
| 48 | N/A | N/A | N/A | 18.7 | N/A | N/A |
| 50 | 11.1 | N/A | N/A | N/A | N/A | N/A |
| 66 | N/A | N/A | 63.1 | N/A | 67.2 | 16.7 |
| 65 | N/A | N/A | N/A | N/A | 58.4 | N/A |
+-----+----------+----------+----------+----------+----------+----------+
I tried using melt(df1) but doing it wrongly since the values in the rows are the NUM values rather than points. Kindly let me know how I could go about solving this.
You are looking for a basic "long" to "wide" reshaping process.
In base R, you can use the notorious reshape
. For this type of data, the syntax is quite straightforward:
reshape(df1, direction = "wide", idvar = "NUM", timevar = "Type")
# NUM Points.A Points.F Points.C Points.B Points.D Points.E
# 1 45 9.2 60.8 22.9 1012.7 NA NA
# 5 48 NA NA NA NA 18.7 NA
# 6 50 11.1 NA NA NA NA NA
# 7 66 NA 16.7 63.1 NA NA 67.2
# 10 68 NA NA NA NA 58.4 NA
You can also use the "tidyr" package, for several functions just wrap reshape2
but uses different syntax. In this case, the syntax would be:
> library(tidyr)
> spread(df1, Type, Points)
You can try dcast
library(reshape2)
dcast(df1, NUM~paste0('Points.',Type), value.var='Points')
Or you can convert to data.table
and use dcast
from the data.table. It would be faster
library(data.table)#v1.9.5+
dcast(setDT(df1), NUM~paste0('Points.',Type), value.var='Points')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With