Mapping Python dictionary with multiple keys into dataframe with multiple columns matching keys

Question

I have a dictionary that I would like to map onto a current dataframe and create a new column. I have keys in a tuple, which map onto two different columns in my dataframe.

dct = {('County', 'State'):'CountyType'}
df = pd.DataFrame(data=['County','State'])

I would like to create a new column, CountyType, using dict to map onto the two columns in df. However, doing the following gives me an error. How else could this be done?

df['CountyType'] = (list(zip(df.County,df.State)))
df = df.replace({'CountyType': county_type_dict)

I was curious how to create the dictionary of tuples. It is similar to what jpp's answer has. yourdict = df.set_index(['County','State'])['CountyType'].to_dict() Can read more here too: stackoverflow.com/questions/42276588/… — Corey Levinson
– Corey Levinson, Commented Feb 5, 2020 at 20:34

jpp · Accepted Answer · 2018-05-31 21:00:55Z

10

You can create a MultiIndex from two series and then map. Data from @ALollz.

df['CountyType'] = df.set_index(['County', 'State']).index.map(dct.get)

print(df)

  County  State CountyType
0      A      1        One
1      A      2       None
2      B      1       None
3      B      2        Two
4      B      3      Three

edited May 31, 2018 at 21:00

answered May 31, 2018 at 20:25

jpp

166k37 gold badges301 silver badges362 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Freddie Over a year ago

AttributeError: 'DataFrame' object has no attribute 'map' how do you overcome this?

jpp Over a year ago

You have to use index attribute. i.e. df.index.map, not df.map.

Freddie Over a year ago

thanks. How can I use this line to only fill nans in 'County Type'?

jpp Over a year ago

@FaaizQadri, Not sure - that would be a new question.

ALollz · Accepted Answer · 2018-05-31 20:05:08Z

6

If you have the following dictionary with tuples as keys and a DataFrame with columns corresponding to the tuple values

import pandas as pd
dct = {('A', 1): 'One', ('B', 2): 'Two', ('B', 3): 'Three'}
df = pd.DataFrame({'County': ['A', 'A', 'B', 'B', 'B'],
                   'State': [1, 2, 1, 2, 3]})

You can create a Series of the tuples from your df and then just use .map()

df['CountyType'] = pd.Series(list(zip(df.County, df.State))).map(dct)

Results in

  County  State CountyType
0      A      1        One
1      A      2        NaN
2      B      1        NaN
3      B      2        Two
4      B      3      Three

answered May 31, 2018 at 20:05

ALollz

59.7k7 gold badges73 silver badges97 bronze badges

1 Comment

Freddie Over a year ago

can you use this to only fill nan? I tried with pd.Series.fillna(), but does not work.

Collectives™ on Stack Overflow

Mapping Python dictionary with multiple keys into dataframe with multiple columns matching keys

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related