I have a database with multiple IDs (rows) and in the columns the covariates. 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 lines 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
Fabiel,
For this condition, there is a calling package of reshape2 that helps you with the function melt.
install.packages("reshape2")
In this case, I'll 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 will group gear and carb .
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 columns that you want to group must be listed. The (ordered) output 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 into a column named "Value" and a "variable" column is created that indicates the column name for the value entered.