CODEWITHSUNDEEP
rdataframeaggregate
user1042267
user1042267

Reputation: 303

R: aggregate similar columns and use column name as value in R

I have a data frame that looks like this

ID  FactorA FactorB Industry1 Industry2  Curr1   Curr2
1   1.121   0.121   1         0          1       0
2   1.52    0.114   0         1          1       0

Factor A and Factor B are real numbers and all of them have values. However Industry1 and industry2 (same for currency 1 and currency 2) are binary, ie only one of them can have value=1.

Since I want to shrink the data for storage purposes as I have 80 Industry types and 100 currency types and only one of them have values, I want to store them like this

ID  FactorA FactorB Industry    Curr
1   1.121   0.121   Industry1   Curr1   
2   1.52    0.114   Industry2   Curr1

Basically I want to use the column names where the value is 1 and join all the strings where type=Industry, Curr etc. I do have another table that links each column name to its type

ColName     Type
FactorA     Factor
FactorB     Factor
Industry1   Industry
Industry2   Industry
Curr1       Curr
Curr2       Curr

Upvotes: 0

Views: 654

Answers (2)

csgillespie
csgillespie

Reputation: 60472

You can collapse columns using the melt function from the reshape2 package. As mentioned in the comments, provide an example - it makes life easier:

dd = data.frame(ID = 1:2, factorA = c(1.121, 1.52),
  factorB = c(0.12, 0.114), Ind1 = 1:0, Ind2= 0:1,
  Curr1 = 1, Curr2=0)

First load the package:

library(reshape2)

Next melt the columns, but protect the first three:

dd1 = melt(dd, id=1:3)

Look at the melt help file for more information. Then just a bit of subsetting to get what you want:

dd2 = dd1[dd1$value == 1,]

You will probably want to drop the final column.

Upvotes: 3

David Robinson
David Robinson

Reputation: 78610

You could use ifelse to create the new columns:

mydata$Industry = ifelse(mydata$Industry1, "Industry1", "Industry2")
mydata$Curr = ifelse(mydata$Curr1, "Curr1", "Curr2")

Upvotes: 0

Related Questions