Way to create a dictionary while iterating through a dataframe

user_14171755 0
Viewed 533

this is my first question here so please excuse me for any formatting errors.

I have a dataframe that let's say looks like this

ID | Contact | First Name | Last Name
 1 |    A    |    Joe     |    Doe
 1 |    B    |    Jane    |    Doe
 2 |    C    |    Peter   |    Parker
 2 |    D    |    Iron    |    Man

And I want to iterate through the dataframe and create a dictionary so I get results such as:

{1:{A:[Joe, Doe]}, {B:[Jane, Doe]}, 2:{C:[Peter, Parker]},{D:[Iron, Man]}}

I'm using pandas to create the dataframe and I've been struggling with this for a while, maybe I'm too far down the rabbithole and the answer is rather easy or something different from what I've already tried.

I tried using a for loop to iterate through the contacts and then checking if the ID repeated and then grouping them together but it didn't work.

Thanks in advance for any help.

pandas python
2 Answers

Since the ID seems to be one key for the rest of the informations from this ID, you can try this (which puts all the informations from the ID into a list):

import pandas as pd
df = pd.DataFrame({'ID': [1,1,2,2], 'Contact': ['A', 'B', 'C', 'D'], 'First Name': ['Joe', 'Jane', 'Peter', 'Iron'], 'Last Name': ['Doe', 'Doe', 'Parker', 'Man']})

res = {}

for index, row in df.iterrows():
    cur_id = row[0]
    cur_contact = row[1]
    cur_fname = row[2]
    cur_lname = row[3]

    if cur_id in res:
        res[cur_id].append({cur_contact: [cur_fname, cur_lname]})
    else:
        res[cur_id] = [{cur_contact: [cur_fname, cur_lname]}]

print(res)

output:

{1: [{'A': ['Joe', 'Doe']}, {'B': ['Jane', 'Doe']}],
 2: [{'C': ['Peter', 'Parker']}, {'D': ['Iron', 'Man']}]}
user_93071410

One way is to create a Full Name column then loop through groupby and specify what you want to do with the data:

df['Full Name'] = df[['First Name', 'Last Name']].agg(list, axis=1)

out = {i:d.set_index('Contact')['Full Name'].to_dict() 
          for i,d in df.groupby('ID')}

Output:

{1: {'A': ['Joe', 'Doe'], 'B': ['Jane', 'Doe']},
 2: {'C': ['Peter', 'Parker'], 'D': ['Iron', 'Man']}}
user_42384080
Related