Segregate (Group) all the messages of each number(two way)

user_19153418 0
Viewed 39

I have a data of conversations between the phone numbers

from phone to phone message
7788994455 2233665588 hi
2233665588 7788994455 hello
1122335566 4455117766 where are you
4455117766 1122335566 I am home
2233665588 7788994455 wassup?

I am looking to segregate all the messages of each number (two way).

Should be as:-

Example from the above table:

7788994455,2233665588:- hi|hello|wassup?

I am looking for the whole conversation should be grouped accordingly(from phone and to phone).

pandas-groupby data-science pandas dataframe python
2 Answers

You may do:

df['from_to'] = list(zip(df['from'], df['to']))
df['from_to'] = df['from_to'].apply(lambda x:tuple(sorted(x)))
df.groupby('from_to').agg({'message':'|'.join})

Output:

                                            message
from_to 
(1122335566, 4455117766)    where are you|I am home
(2233665588, 7788994455)           hi|hello|wassup?
user_14103030

Just have a combined field for both:

df['grouper'] = df.apply(lambda x: set([x['from phone'], x['to phone']]), axis=1).astype(str)

And do a groupby:

df.groupby('grouper').agg({'message': ' | '.join})

Which will give you following dataframe:

                            message
grouper 
{1122335566, 4455117766}    I am home
{2233665588, 7788994455}    hi | hello | wassup?
{4455117766, 1122335566}    where are you
user_72129290
Related