3
I have a database with several ID (rows) and in the columns the covariables. These covariables are repeated in other columns with different information about the same ID. How to join these covariables 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
2
Fabiel,
For this condition, there is a calling package of reshape2 that assists 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 the purposes of demonstration, we shall group Gear and carb.
The melt command basically groups all the 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 should be listed. The output (sorted) 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 "variable" column is created indicating the name of the column referring to the value inserted.
Browser other questions tagged r date rstudio
You are not signed in. Login or sign up in order to post.
Fabiel, what exactly do you mean by putting together these covariables? And what is the reason for this? In this case, do you refer to group by? Or are you talking about grouping these templates into one dataframe?
– Nicolas Vieira
Be Var1, Var2, Var3 and Var4. What I want is to unite all these variables into just one variable, would be a var2 "necklace" under var1 and so on.
– Fabiel Fernando
I believe I understand. Just to make it clearer and make sure: Will the 4 columns be grouped into a UNICA in exchange for more rows with the same id filled in? Ex: ID1 ,var1, var2,..., var4. Would: ID1, var1; ID1, var2; ...; ID1, var4?
– Nicolas Vieira
Yes. The ID will end up repeating itself in other lines to make the compensation of this single variable that I will have.
– Fabiel Fernando