Create new dataframe column with values based existing column AND on dictionary?

Question

I have a dictionary

smsgateway = {'AT&T':'@txt.att.net', 'Boost Mobile':'@sms.myboostmobile.com', 'Cricket':'@sms.cricketwireless.net', 'Google Fi':'@msg.fi.google.com', 'Metro PCS':'@mymetropcs.com', 'Republic Wireless':'@textrepublicwireless.com', 'Sprint':'@messaging.sprintpcs.com', 'T-Mobile':'@tmomail.net', 'U.S. Cellular': '@email.uscc.net', 'Verizon':'@vtext.com', 'Virgin Mobile': '@vmobil.com', 'XFinity Mobile':'@vtext.com'}

and I have a dataframe like so:

Name         cell_provider  cell_number
Tony Danza   Google Fi      9999999999  
John Smith   T-Mobile       8888888888

Ultimately, I am trying to create a new df column that is created based on the cell_number and the cell_provider ([email protected]) but I am stuck at trying to create a column that is taking the elements of cell_provider and comparing them to the key/value pair of the smsgateway dictionary. I understand how to concat the columns after I get past this step, could anyone point me in the right direction? Please...and THANK YOU!

Zach Flanders · Accepted Answer · 2022-07-05 17:37:54Z

2

You can replace the cell_provider values with the addresses from the dictionary using .replace and then add the resulting series to the cell number after casting to string like this:

df = df.assign(cell_num_provider=df.cell_number.astype(str) + df.cell_provider.replace(smsgateway))

output:

   cell_number cell_provider           cell_num_provider
0     99999999     Google Fi  [email protected]
1     88888888      T-Mobile        [email protected]

See documentation on pandas.Series.replace

answered Jul 5, 2022 at 17:37

Zach Flanders

1,3141 gold badge7 silver badges11 bronze badges

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

1 Comment

AKtheAT Over a year ago

You guys are geniuses! It amazes me how simple these fixes can be and how much more I have to learn still...

Qdr · Accepted Answer · 2022-07-05 17:54:23Z

1

An alternative solution to the first answer. you could use transform:

df['address'] = df['cell_number'].astype(str) + df['cell_provider'].transform(lambda: smsgateway[x])

output:

      cell_number  cell_provider    address
0     99999999     Google Fi        [email protected]
1     88888888     T-Mobile         [email protected]

answered Jul 5, 2022 at 17:54

Qdr

7455 silver badges13 bronze badges

1 Comment

AKtheAT Over a year ago

You guys are geniuses! It amazes me how simple these fixes can be and how much more I have to learn still... Thank you as well!

Hamza · Accepted Answer · 2022-07-05 18:42:44Z

0

Probably the cleanest way would be to use map:

df['cell_num_provider'] = df.cell_number+df.cell_provider.map(smsgateway))

	Name	cell_provider	cell_number	cell_num_provider
0	Tony Danza	Google Fi	9999999999	[email protected]
1	John Smith	T-Mobile	8888888888	[email protected]

answered Jul 5, 2022 at 18:42

Hamza

6,1354 gold badges38 silver badges52 bronze badges

2 Comments

AKtheAT Over a year ago

This looks great too! Thanks! What is the etiquette for selecting an answer!?

Hamza Over a year ago

You can select the tick mark on the left side of answer below votes buttons to mark it as accepted answer. Detailed instructions: meta.stackexchange.com/questions/147531/….

Collectives™ on Stack Overflow

Create new dataframe column with values based existing column AND on dictionary?

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related