Reshaping dataframe - two columns from correlation variables

Question

I have the below df

    var1 var2 Freq
1    a    b   10
2    b    a    5
3    b    d   10

created from

help <- data.frame(var1 = c("a", "b", "b"), var2 = c("b", "a", "d"), Freq = c(10, 5, 10))

ab correlation is the same as ba, and I am hoping to combine them into one row to look like

   var1 var2 Freq
1    a    b   15
2    b    d   10

any thoughts?

By using stringsAsFactors=FALSE, you can change var1=pmin(var1,var2) and var2=pmax(var1,var2), after which aggregation should be straightforward. — Arun
– Arun, Commented Oct 17, 2014 at 20:45

Sven Hohenstein · Accepted Answer · 2014-10-17 20:16:20Z

2

Here's one way:

setNames(aggregate(help$Freq, as.data.frame(t(apply(help[-3], 1, sort))), sum), 
         names(help))

#   var1 var2 Freq
# 1    a    b   15
# 2    b    d   10

answered Oct 17, 2014 at 20:16

Sven Hohenstein

82k17 gold badges150 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

b222 Over a year ago

Thanks, Sven. Can you explain what the help[-3] means?

Sven Hohenstein Over a year ago

@bpace The command help[-3] is an example of negative indexing. It returns all columns of help except the third one.

agstudy · Accepted Answer · 2014-10-17 19:56:19Z

0

In base R :

do.call(rbind,
by(dat,rowSums(sapply(dat[,c("var1","var2")],as.integer)),
   function(x)data.frame(x[1,c("var1","var2")],
                         Freq= sum(x[,"Freq"]))))

  var1 var2 Freq
3    a    b   15
5    b    d   10

I create first a grouping variable by summing the integer representation of your columns. Then performing the sum of frequencies by group. Finally bind the result to get a new data.frame.

answered Oct 17, 2014 at 19:56

agstudy

122k18 gold badges205 silver badges265 bronze badges

Collectives™ on Stack Overflow

Reshaping dataframe - two columns from correlation variables

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related