Match values from dictionary to dataframe row values and add data to that row

Question

How to add values from dictionary as values in new column of df but associated with existing row by value of key in dictionary

import pandas as pd
data = {'caseno': ['123', '456', '789', '000'], 'defname': ['defendant1', 'defendant2', 'defendant3', 'defendant4']}
df = pd.DataFrame.from_dict(data)

def_dict = {'123': ['123address', '123address2', '123csz'], '456':['456address', '456address2', '456csz']}

caseno_lst = df['caseno'].tolist()

I thought this would work, but throws index error.

     for i in caseno_lst:
          for k, v in def_dict.items():
            if k == i:
              df['defadd'] = v
            else:
              pass

Expected output:

        caseno defname     defadd
    0    123   defendant1  [123address, 123adress2, 123csz]
    1    456   defendant2  [456address, 456address2, 456csz]
    2    789   defendant3
    3    000   defendant4

The issue is that my dictionary will not necessarily have a key that matches each case no in the df that I am trying to add columns and values to.

@yatu, actually none of the other answers relate to my precise question. My issue surrounds around adding a new col to the df then adding data into that col, but not having data, necessarily, for each row of the df. All of the other answers are not involving this mismatch issue. Please unmark this as a duplicate or point me to an answer which deals with this precise issue. Thanks. — John Taylor, Oct 09 '19 at 13:48
To embed a list in a dataframe entry is a really abnormal use-case. I'd rethink what problem you're actually trying to solve. — jason m, Oct 09 '19 at 13:51
So not working for you `df['defadd'] = df['caseno'].map(def_dict).fillna('')` ? — jezrael, Oct 09 '19 at 14:01

score 2 · Accepted Answer · answered Oct 09 '19 at 14:04

2

df['defadd'] = df['caseno'].apply(lambda x: def_dict.get(x)).fillna('')

This should give your expected output.

answered Oct 09 '19 at 14:04

Danny

470
4
14

that was fantastic. Worked exactly how I wanted it to work. The other answers and these taught me a lot. StackOverflow to the rescue again! Thanks everyone. – John Taylor Oct 09 '19 at 17:01

score 1 · Answer 2 · answered Oct 09 '19 at 13:59

Building off of what jason m said, this may not be the most appropriate data structure for your use case

That being said, if I understand your use case, you want to associate the addresses with a given caseno based on the dictionary (and the expectation is that the caseno may not be present in some instances of addresses), you would use exception handling to only pick up the ones where the address exists.

The code below could be a simple way to do so (but by no means the most efficient)

df['defadd']=''
for index in df.index:
    try:
        df.loc[index,'defadd']=def_dict[df['caseno'][index]]
    except:
        df.loc[index,'defadd']=''

output:

  caseno     defname                             defadd
0    123  defendant1  [123address, 123address2, 123csz]
1    456  defendant2  [456address, 456address2, 456csz]
2    789  defendant3                                   
3    000  defendant4

jezrael · Answer 3 · 2019-10-09T14:05:47.087

I believe you need:

df['defadd'] = df['caseno'].map(def_dict).fillna('')

print (df)
  caseno     defname                             defadd
0    123  defendant1  [123address, 123address2, 123csz]
1    456  defendant2  [456address, 456address2, 456csz]
2    789  defendant3                                   
3    000  defendant4

Or:

df['defadd'] = df['caseno'].map(lambda x: def_dict.get(x, ''))
print (df)
  caseno     defname                             defadd
0    123  defendant1  [123address, 123address2, 123csz]
1    456  defendant2  [456address, 456address2, 456csz]
2    789  defendant3                                   
3    000  defendant4

For missing lists:

df['defadd'] = df['caseno'].map(lambda x: def_dict.get(x, []))
print (df)
  caseno     defname                             defadd
0    123  defendant1  [123address, 123address2, 123csz]
1    456  defendant2  [456address, 456address2, 456csz]
2    789  defendant3                                 []
3    000  defendant4                                 []

Match values from dictionary to dataframe row values and add data to that row

3 Answers3