CODEWITHSUNDEEP
pythonpandas
Karma
Karma

Reputation: 269

Filling out empty cells with lists of values

I have a data frame that looks like below:

         City         State              Country
      Chicago            IL        United States
       Boston                    
    San Diego            CA        United States
  Los Angeles            CA        United States
San Francisco
   Sacramento     
    Vancouver            BC               Canada
      Toronto

And I have 3 lists of values that are ready to fill in the None cells:

city = ['Boston', 'San Francisco', 'Sacramento', 'Toronto']
state = ['MA', 'CA', 'CA', 'ON']
country = ['United States', 'United States', 'United States', 'Canada']

The order of the elements in these list are correspondent to each other. Thus, the first items across all 3 lists match each other, and so forth. How can I fill out the empty cells and produce a result like below?

         City         State              Country
      Chicago            IL        United States
       Boston            MA        United States
    San Diego            CA        United States
  Los Angeles            CA        United States
San Francisco            CA        United States
   Sacramento            CA        United States
    Vancouver            BC               Canada
      Toronto            ON               Canada

My code gives me an error and I'm stuck.

if df.loc[df['City'] == 'Boston']:
    'State' = 'MA'

Any solution is welcome. Thank you.

Upvotes: 1

Views: 74

Answers (1)

cs95
cs95

Reputation: 403060

Create two mappings, one for <city : state>, and another for <city : country>.

city_map = dict(zip(city, state))
country_map = dict(zip(city, country))

Next, set City as the index -

df = df.set_index('City')

And, finally use map/replace to transform keys to values as appropriate -

df['State'] = df['City'].map(city_map)
df['Country'] = df['City'].map(country_map)

As an extra final step, you may call df.reset_index() at the end.

Upvotes: 2

Related Questions