Question:
I have a database with several ID (rows) and the covariates in the columns. These covariates are repeated in other columns with different information about the same ID. How to join these covariates using R in a single column and creating more rows for each grouped value?
Example:
id var1 var2
1 21.0 160.0
2 25.0 100.0
Results in:
id var
1 21.0
1 160.0
2 25.0
2 100.0
Answer:
Fabiel,
For this condition, there is a reshape2 caller package that helps you with the melt function.
install.packages("reshape2")
For this case, I will use the dataset included in RSTUDIO called mtcars :
> data(mtcars)
> head(mtcars[order(mtcars$rn)],4)
rn mpg cyl disp hp drat wt qsec vs am gear carb
1: AMC Javelin 15.2 8 304 150 3.15 3.435 17.30 0 0 3 2
2: Cadillac Fleetwood 10.4 8 472 205 2.93 5.250 17.98 0 0 3 4
3: Camaro Z28 13.3 8 350 245 3.73 3.840 15.41 0 0 3 4
4: Chrysler Imperial 14.7 8 440 230 3.23 5.345 17.42 0 0 3 4
For demonstration purposes, we'll group the gear and carb column.
The melt command basically performs the grouping of all unlisted columns, as follows:
mtcars_dataframe_melt <- melt(mtcars, id.vars=c("rn","mpg", "cyl", "disp", "hp", "drat", "wt","qsec","vs","am"))
In this case, all columns except the ones you want to group must be listed. The output (ordered) is as follows:
>head(mtcars_dataframe_melt[order(mtcars_dataframe_melt$rn),],8)
rn mpg cyl disp hp drat wt qsec vs am variable value
23 AMC Javelin 15.2 8 304 150 3.15 3.435 17.30 0 0 gear 3
55 AMC Javelin 15.2 8 304 150 3.15 3.435 17.30 0 0 carb 2
15 Cadillac Fleetwood 10.4 8 472 205 2.93 5.250 17.98 0 0 gear 3
47 Cadillac Fleetwood 10.4 8 472 205 2.93 5.250 17.98 0 0 carb 4
24 Camaro Z28 13.3 8 350 245 3.73 3.840 15.41 0 0 gear 3
56 Camaro Z28 13.3 8 350 245 3.73 3.840 15.41 0 0 carb 4
17 Chrysler Imperial 14.7 8 440 230 3.23 5.345 17.42 0 0 gear 3
49 Chrysler Imperial 14.7 8 440 230 3.23 5.345 17.42 0 0 carb 4
Columns are grouped in a column named "Value" and a column "variable" is created which indicates the column name referring to the entered value.