🔒 Identifying "conversations" from messages in a Pandas DataFrame.

[maiden sun]

My data consists of messages between multiple people. I want to be able to add a column with a unique key that lets me link messages between two people as conversations. I can use the UUID module to generate the key so that's not a problem.

The tricky bit is that values in the "from" and "to" columns can be reversed as messages are sent in both directions.

My data looks like this:

df = pd.DataFrame({'From': [['1a2b3c', 'AAA'], ['4d5e6f', 'BBB'], ['7g8h9i', 'CCC'], ['111', 'ZZZ']], 
                   'To': [['111', 'ZZZ'], ['222', 'YYY'], ['333', 'XXX'], ['1a2b3c', 'AAA']], 
                   'Message': ['ABC', 'DEF', 'GHI', 'TEST']})

I would like my data to look like this (note the 1st and last rows are messages between the same person so have the same conversation key):

df = pd.DataFrame({'From': [['1a2b3c', 'AAA'], ['4d5e6f', 'BBB'], ['7g8h9i', 'CCC'], ['111', 'ZZZ']], 
                   'To': [['111', 'ZZZ'], ['222', 'YYY'], ['333', 'XXX'], ['1a2b3c', 'AAA']], 
                   'Message': ['ABC', 'DEF', 'GHI', 'TEST'],
                   'Conversation': ['6cbea588-356e-4b97-a950-a69469c652a9', 'c2c531da-a340-46a3-8290-e62488791bf7', '5723d816-04f4-4c6a-9870-239295c6d7ce', '6cbea588-356e-4b97-a950-a69469c652a9']})

Thanks :)!

[echo finch]

@maiden sun

Python help channel opened

Remember to:

• Ask your Python question, not if you can ask or if there's an expert who can help.
• Show a code sample as text (rather than a screenshot) and the error message, if you've got one.
• Explain what you expect to happen and what actually happens.

:warning: Do not pip install anything that isn't related to your question, especially if asked to over DMs.

Closes after a period of inactivity, or when you send !close.

[plain rampart]

ok so you want to add a conversation column to the data?

[maiden sun]

Yeah that's right. I'm basically looking to "conversationalise" this data so I can identify strands of individual conversations between two people.

[plain rampart]

so something like this

[maiden sun]

Kind of, rows 0 and 3 should have the same conversation key though as they are just different sides of the same conversation if that makes sense?

[plain rampart]

oh i see

hrm

like this?

[maiden sun]

Yeah that's awesome! Thank you. How did you manage it 😆 ?

[plain rampart]

i'll be honest, using an AI to help haha

import pandas as pd
import uuid


df = pd.DataFrame(
    {
        "From": [
            ["1a2b3c", "AAA"],
            ["4d5e6f", "BBB"],
            ["7g8h9i", "CCC"],
            ["111", "ZZZ"],
        ],
        "To": [["111", "ZZZ"], ["222", "YYY"], ["333", "XXX"], ["1a2b3c", "AAA"]],
        "Message": ["ABC", "DEF", "GHI", "TEST"],
    }
)

# Create an empty dictionary to store the UUID mappings
uuid_mapping = {}

# Iterate over each row in the DataFrame
for index, row in df.iterrows():
    # Concatenate 'From' and 'To' values, sort them, and convert to a tuple
    # This ensures that the order of 'From' and 'To' does not affect the key
    from_to_pair = tuple(sorted(row["From"] + row["To"]))

    # Check if the sorted tuple is already in the uuid_mapping dictionary
    if from_to_pair not in uuid_mapping:
        # If not, generate a new UUID and add it to the dictionary with the tuple as the key
        uuid_mapping[from_to_pair] = str(uuid.uuid4())

# Apply the UUID mapping to the DataFrame to assign the 'conversation' UUID
# For each row, find the corresponding UUID in the uuid_mapping dictionary based on the 'From' and 'To' values
df["conversation"] = df.apply(
    lambda row: uuid_mapping[tuple(sorted(row["From"] + row["To"]))], axis=1
)

# Print the DataFrame to see the result
print(df)

[maiden sun]

Ah ok, great! Thanks for that. I'll have a play with it. Looks like it does the job though :).

[plain rampart]

yeah try it on your larger dataset and let me know if it works

seems to work on your example though

[maiden sun]

Thanks, all working fine :)! Although I've realised I may need to add a few fields and change the values in the from/to columns into a dictionary so don't be surprised if I repost in a few days with some tweaks haha. I think the dictionary will break the sorting logic but I'll give it a go myself first.

It's late for me though so I'll call it a day for now. Appreciate the help.

[echo finch]

Python help channel closed

This help channel has been closed and it's no longer possible to send messages here. If your question wasn't answered, feel free to create a new post in #1035199133436354600. To maximize your chances of getting a response, check out this guide on asking good questions.