0

I have three columns: Sender, Receiver, # of Text Messages.

I need to condense the rows by getting the average of two columns.

So let's say Carol sends Jack 8 text messages, and Jack sends Carol 4 text messages, the result would be 6 messages between these two people.

See image below for better description.

enter image description here

user10631408
  • 1
  • 2
  • Please post your data as text, not images. Responders would have to type it all in if it is not text. If mydata is your data frame then paste the output of `dput(mydata)` into the question or if that is very large `dput(head(mydata))` – G. Grothendieck Apr 18 '19 at 15:40

1 Answers1

0

A typical way of doing this in SQL is something like:

select least(sender, receiver) as person1,
       greatest(sender, receiver) as person2,
       sum(num)
from t
group by least(sender, receiver),
         greatest(sender, receiver);

Some databases don't support least() and greatest(). These can be replaced by case expressions.

select (case when sender < receiver then sender else receiver end) as person1,
       (case when sender >= receiver then sender else receiver end) as person2,
       sum(num)
from t
group by (case when sender < receiver then sender else receiver end), 
          (case when sender >= receiver then sender else receiver end);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786