Pandas: Search if substring contains key in dictionary, and return value

up vote
0
down vote

favorite

I have a dictionary (key, value) and a dataframe using pandas.

mydict = {'KULAR LUMPUR' : 'MY',

            'SINGAPORE' : 'SG',

            'HONG KONG' : 'HK',

            'VIETNAM': 'VN'}

and a dataframe with column ['Address']

                              Address

0  234 JALAN ST KULAR LUMPUR MALAYSIA

1       123 BUILDING STREET SINGAPORE

2          67 CANNING VALE, HONG KONG

How do I search through the dataframe to get the value from the dictionary if substring is found in the key of the dictionary.

e.g.

                              Address Code

0  234 JALAN ST KULAR LUMPUR MALAYSIA   MY

1       123 BUILDING STREET SINGAPORE   SG

2          67 CANNING VALE, HONG KONG   HK

edited Nov 12 at 6:53

jezrael

316k22256333

asked Nov 12 at 6:40

newtoCS

436

format your dict and dataframes properly, so we can run it directly without adding all quotes and commas.
– Vineeth Sai
Nov 12 at 6:43

add a comment |

up vote
0
down vote

favorite

I have a dictionary (key, value) and a dataframe using pandas.

mydict = {'KULAR LUMPUR' : 'MY',

            'SINGAPORE' : 'SG',

            'HONG KONG' : 'HK',

            'VIETNAM': 'VN'}

and a dataframe with column ['Address']

                              Address

0  234 JALAN ST KULAR LUMPUR MALAYSIA

1       123 BUILDING STREET SINGAPORE

2          67 CANNING VALE, HONG KONG

How do I search through the dataframe to get the value from the dictionary if substring is found in the key of the dictionary.

e.g.

                              Address Code

0  234 JALAN ST KULAR LUMPUR MALAYSIA   MY

1       123 BUILDING STREET SINGAPORE   SG

2          67 CANNING VALE, HONG KONG   HK

edited Nov 12 at 6:53

jezrael

316k22256333

asked Nov 12 at 6:40

newtoCS

436

format your dict and dataframes properly, so we can run it directly without adding all quotes and commas.
– Vineeth Sai
Nov 12 at 6:43

add a comment |

up vote
0
down vote

favorite

I have a dictionary (key, value) and a dataframe using pandas.

mydict = {'KULAR LUMPUR' : 'MY',

            'SINGAPORE' : 'SG',

            'HONG KONG' : 'HK',

            'VIETNAM': 'VN'}

and a dataframe with column ['Address']

                              Address

0  234 JALAN ST KULAR LUMPUR MALAYSIA

1       123 BUILDING STREET SINGAPORE

2          67 CANNING VALE, HONG KONG

How do I search through the dataframe to get the value from the dictionary if substring is found in the key of the dictionary.

e.g.

                              Address Code

0  234 JALAN ST KULAR LUMPUR MALAYSIA   MY

1       123 BUILDING STREET SINGAPORE   SG

2          67 CANNING VALE, HONG KONG   HK

edited Nov 12 at 6:53

jezrael

316k22256333

asked Nov 12 at 6:40

newtoCS

436

I have a dictionary (key, value) and a dataframe using pandas.

mydict = {'KULAR LUMPUR' : 'MY',

            'SINGAPORE' : 'SG',

            'HONG KONG' : 'HK',

            'VIETNAM': 'VN'}

and a dataframe with column ['Address']

                              Address

0  234 JALAN ST KULAR LUMPUR MALAYSIA

1       123 BUILDING STREET SINGAPORE

2          67 CANNING VALE, HONG KONG

How do I search through the dataframe to get the value from the dictionary if substring is found in the key of the dictionary.

e.g.

                              Address Code

0  234 JALAN ST KULAR LUMPUR MALAYSIA   MY

1       123 BUILDING STREET SINGAPORE   SG

2          67 CANNING VALE, HONG KONG   HK

python string pandas dictionary

edited Nov 12 at 6:53

jezrael

316k22256333

asked Nov 12 at 6:40

newtoCS

436

edited Nov 12 at 6:53

jezrael

316k22256333

asked Nov 12 at 6:40

newtoCS

436

edited Nov 12 at 6:53

jezrael

316k22256333

edited Nov 12 at 6:53

jezrael

316k22256333

edited Nov 12 at 6:53

jezrael

316k22256333

asked Nov 12 at 6:40

newtoCS

436

asked Nov 12 at 6:40

newtoCS

436

asked Nov 12 at 6:40

newtoCS

436

format your dict and dataframes properly, so we can run it directly without adding all quotes and commas.
– Vineeth Sai
Nov 12 at 6:43

add a comment |

format your dict and dataframes properly, so we can run it directly without adding all quotes and commas.
– Vineeth Sai
Nov 12 at 6:43

format your dict and dataframes properly, so we can run it directly without adding all quotes and commas.
– Vineeth Sai
Nov 12 at 6:43

add a comment |

1 Answer
1

active

oldest

votes

up vote
1
down vote

Use str.extract by regex with keys of dictionary with map:

df = pd.DataFrame({'Address': ['234 JALAN ST KULAR LUMPUR MALAYSIA', 

                               '123 BUILDING STREET SINGAPORE', 

                               '67 CANNING VALE, HONG KONG']})



print (df)

                              Address

0  234 JALAN ST KULAR LUMPUR MALAYSIA

1       123 BUILDING STREET SINGAPORE

2          67 CANNING VALE, HONG KONG



mydict = {'KULAR LUMPUR' : 'MY',

            'SINGAPORE' : 'SG',

            'HONG KONG' : 'HK',

            'VIETNAM': 'VN'}



pat = '|'.join(r"b{}b".format(x) for x in mydict.keys())

df['Code'] = df['Address'].str.extract('('+ pat + ')', expand=False).map(mydict)



print (df)

                              Address Code

0  234 JALAN ST KULAR LUMPUR MALAYSIA   MY

1       123 BUILDING STREET SINGAPORE   SG

2          67 CANNING VALE, HONG KONG   HK

Explanation:

print (pat)

bKULAR LUMPURb|bSINGAPOREb|bHONG KONGb|bVIETNAMb

b are called word boundaries for match words between b
| are for regex OR

edited Nov 12 at 7:35

answered Nov 12 at 6:46

jezrael

316k22256333

1

Jezrael, can pls you add the explanation about pat that will help readers and me :-)
– pygo
Nov 12 at 6:52

I am getting this error. AttributeError: 'DataFrame' object has no attribute 'map'
– newtoCS
Nov 12 at 6:56

@newtoCS - do you use df['Address'].str.extract('('+ pat + ')', expand=False).map(mydict) ?
– jezrael
Nov 12 at 6:59

I have used.. and if it is KULARLUMPUR , without spacing - this answer works. However, if it is KULAR LUMPUR with spacing (2 words), the error will come up
– newtoCS
Nov 12 at 7:13

@newtoCS - For me it working nice, there are same spaces, only one in data and in dictioanry?
– jezrael
Nov 12 at 7:14

|
show 1 more comment

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53257034%2fpandas-search-if-substring-contains-key-in-dictionary-and-return-value%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
1
down vote

Use str.extract by regex with keys of dictionary with map:

df = pd.DataFrame({'Address': ['234 JALAN ST KULAR LUMPUR MALAYSIA', 

                               '123 BUILDING STREET SINGAPORE', 

                               '67 CANNING VALE, HONG KONG']})



print (df)

                              Address

0  234 JALAN ST KULAR LUMPUR MALAYSIA

1       123 BUILDING STREET SINGAPORE

2          67 CANNING VALE, HONG KONG



mydict = {'KULAR LUMPUR' : 'MY',

            'SINGAPORE' : 'SG',

            'HONG KONG' : 'HK',

            'VIETNAM': 'VN'}



pat = '|'.join(r"b{}b".format(x) for x in mydict.keys())

df['Code'] = df['Address'].str.extract('('+ pat + ')', expand=False).map(mydict)



print (df)

                              Address Code

0  234 JALAN ST KULAR LUMPUR MALAYSIA   MY

1       123 BUILDING STREET SINGAPORE   SG

2          67 CANNING VALE, HONG KONG   HK

Explanation:

print (pat)

bKULAR LUMPURb|bSINGAPOREb|bHONG KONGb|bVIETNAMb

b are called word boundaries for match words between b
| are for regex OR

edited Nov 12 at 7:35

answered Nov 12 at 6:46

jezrael

316k22256333

1

Jezrael, can pls you add the explanation about pat that will help readers and me :-)
– pygo
Nov 12 at 6:52

I am getting this error. AttributeError: 'DataFrame' object has no attribute 'map'
– newtoCS
Nov 12 at 6:56

@newtoCS - do you use df['Address'].str.extract('('+ pat + ')', expand=False).map(mydict) ?
– jezrael
Nov 12 at 6:59

I have used.. and if it is KULARLUMPUR , without spacing - this answer works. However, if it is KULAR LUMPUR with spacing (2 words), the error will come up
– newtoCS
Nov 12 at 7:13

@newtoCS - For me it working nice, there are same spaces, only one in data and in dictioanry?
– jezrael
Nov 12 at 7:14

|
show 1 more comment

up vote
1
down vote

Use str.extract by regex with keys of dictionary with map:

df = pd.DataFrame({'Address': ['234 JALAN ST KULAR LUMPUR MALAYSIA', 

                               '123 BUILDING STREET SINGAPORE', 

                               '67 CANNING VALE, HONG KONG']})



print (df)

                              Address

0  234 JALAN ST KULAR LUMPUR MALAYSIA

1       123 BUILDING STREET SINGAPORE

2          67 CANNING VALE, HONG KONG



mydict = {'KULAR LUMPUR' : 'MY',

            'SINGAPORE' : 'SG',

            'HONG KONG' : 'HK',

            'VIETNAM': 'VN'}



pat = '|'.join(r"b{}b".format(x) for x in mydict.keys())

df['Code'] = df['Address'].str.extract('('+ pat + ')', expand=False).map(mydict)



print (df)

                              Address Code

0  234 JALAN ST KULAR LUMPUR MALAYSIA   MY

1       123 BUILDING STREET SINGAPORE   SG

2          67 CANNING VALE, HONG KONG   HK

Explanation:

print (pat)

bKULAR LUMPURb|bSINGAPOREb|bHONG KONGb|bVIETNAMb

b are called word boundaries for match words between b
| are for regex OR

edited Nov 12 at 7:35

answered Nov 12 at 6:46

jezrael

316k22256333

1

Jezrael, can pls you add the explanation about pat that will help readers and me :-)
– pygo
Nov 12 at 6:52

I am getting this error. AttributeError: 'DataFrame' object has no attribute 'map'
– newtoCS
Nov 12 at 6:56

@newtoCS - do you use df['Address'].str.extract('('+ pat + ')', expand=False).map(mydict) ?
– jezrael
Nov 12 at 6:59

I have used.. and if it is KULARLUMPUR , without spacing - this answer works. However, if it is KULAR LUMPUR with spacing (2 words), the error will come up
– newtoCS
Nov 12 at 7:13

@newtoCS - For me it working nice, there are same spaces, only one in data and in dictioanry?
– jezrael
Nov 12 at 7:14

|
show 1 more comment

up vote
1
down vote

Use str.extract by regex with keys of dictionary with map:

df = pd.DataFrame({'Address': ['234 JALAN ST KULAR LUMPUR MALAYSIA', 

                               '123 BUILDING STREET SINGAPORE', 

                               '67 CANNING VALE, HONG KONG']})



print (df)

                              Address

0  234 JALAN ST KULAR LUMPUR MALAYSIA

1       123 BUILDING STREET SINGAPORE

2          67 CANNING VALE, HONG KONG



mydict = {'KULAR LUMPUR' : 'MY',

            'SINGAPORE' : 'SG',

            'HONG KONG' : 'HK',

            'VIETNAM': 'VN'}



pat = '|'.join(r"b{}b".format(x) for x in mydict.keys())

df['Code'] = df['Address'].str.extract('('+ pat + ')', expand=False).map(mydict)



print (df)

                              Address Code

0  234 JALAN ST KULAR LUMPUR MALAYSIA   MY

1       123 BUILDING STREET SINGAPORE   SG

2          67 CANNING VALE, HONG KONG   HK

Explanation:

print (pat)

bKULAR LUMPURb|bSINGAPOREb|bHONG KONGb|bVIETNAMb

b are called word boundaries for match words between b
| are for regex OR

edited Nov 12 at 7:35

answered Nov 12 at 6:46

jezrael

316k22256333

Use str.extract by regex with keys of dictionary with map:

df = pd.DataFrame({'Address': ['234 JALAN ST KULAR LUMPUR MALAYSIA', 

                               '123 BUILDING STREET SINGAPORE', 

                               '67 CANNING VALE, HONG KONG']})



print (df)

                              Address

0  234 JALAN ST KULAR LUMPUR MALAYSIA

1       123 BUILDING STREET SINGAPORE

2          67 CANNING VALE, HONG KONG



mydict = {'KULAR LUMPUR' : 'MY',

            'SINGAPORE' : 'SG',

            'HONG KONG' : 'HK',

            'VIETNAM': 'VN'}



pat = '|'.join(r"b{}b".format(x) for x in mydict.keys())

df['Code'] = df['Address'].str.extract('('+ pat + ')', expand=False).map(mydict)



print (df)

                              Address Code

0  234 JALAN ST KULAR LUMPUR MALAYSIA   MY

1       123 BUILDING STREET SINGAPORE   SG

2          67 CANNING VALE, HONG KONG   HK

Explanation:

print (pat)

bKULAR LUMPURb|bSINGAPOREb|bHONG KONGb|bVIETNAMb

b are called word boundaries for match words between b
| are for regex OR

edited Nov 12 at 7:35

answered Nov 12 at 6:46

jezrael

316k22256333

edited Nov 12 at 7:35

answered Nov 12 at 6:46

jezrael

316k22256333

answered Nov 12 at 6:46

jezrael

316k22256333

answered Nov 12 at 6:46

jezrael

316k22256333

1

Jezrael, can pls you add the explanation about pat that will help readers and me :-)
– pygo
Nov 12 at 6:52

I am getting this error. AttributeError: 'DataFrame' object has no attribute 'map'
– newtoCS
Nov 12 at 6:56

@newtoCS - do you use df['Address'].str.extract('('+ pat + ')', expand=False).map(mydict) ?
– jezrael
Nov 12 at 6:59

I have used.. and if it is KULARLUMPUR , without spacing - this answer works. However, if it is KULAR LUMPUR with spacing (2 words), the error will come up
– newtoCS
Nov 12 at 7:13

@newtoCS - For me it working nice, there are same spaces, only one in data and in dictioanry?
– jezrael
Nov 12 at 7:14

|
show 1 more comment

1

Jezrael, can pls you add the explanation about pat that will help readers and me :-)
– pygo
Nov 12 at 6:52

I am getting this error. AttributeError: 'DataFrame' object has no attribute 'map'
– newtoCS
Nov 12 at 6:56

@newtoCS - do you use df['Address'].str.extract('('+ pat + ')', expand=False).map(mydict) ?
– jezrael
Nov 12 at 6:59

I have used.. and if it is KULARLUMPUR , without spacing - this answer works. However, if it is KULAR LUMPUR with spacing (2 words), the error will come up
– newtoCS
Nov 12 at 7:13

@newtoCS - For me it working nice, there are same spaces, only one in data and in dictioanry?
– jezrael
Nov 12 at 7:14

Jezrael, can pls you add the explanation about pat that will help readers and me :-)
– pygo
Nov 12 at 6:52

I am getting this error. AttributeError: 'DataFrame' object has no attribute 'map'
– newtoCS
Nov 12 at 6:56

@newtoCS - do you use df['Address'].str.extract('('+ pat + ')', expand=False).map(mydict) ?
– jezrael
Nov 12 at 6:59

I have used.. and if it is KULARLUMPUR , without spacing - this answer works. However, if it is KULAR LUMPUR with spacing (2 words), the error will come up
– newtoCS
Nov 12 at 7:13

@newtoCS - For me it working nice, there are same spaces, only one in data and in dictioanry?
– jezrael
Nov 12 at 7:14

|
show 1 more comment

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vfrdtyky