0

I have a dataframe like this. 

Company        Headquarters
Amazon         United States Seattle
Tencent        China Shenzhen
Rakuten        Japan Tokyo
Naver          South Korea Seongnam
Flipkart       India Bengaluru
ASOS.com       United Kingdom London
Salesforce.com United States San Francisco
Chewy          United States Dania Beach, Florida

I would like to split the 'Headquarters' columns into 'Country' and 'City'. There is no delimiter in between. I have no clue how to do this. Thanks very much.

Candice2020
  • 31
  • 4
  • whata the logic gonna be? for your sample `df["Town"] = df["Headquartert"].str.split()[-1]` could work - as soon as you add `'Fantasia South Hampton'` is added this does not work anymore – Patrick Artner Apr 22 '20 at 19:59
  • Will there only ever be 2 or 3 words? And will the 3-word entries always conform to `Country Country City` as represented in your posted data? Or are there different cases you haven't shown? – G. Anderson Apr 22 '20 at 20:01
  • The column Headquarters sometimes has more than 3 words, like United States San Francisco, United States Dania Beach, Florida. – Candice2020 Apr 22 '20 at 20:06
  • 1
    My first instinct would be to find and replace all of the 2-word country names with an abbreviation, e.g. `'South Korea'`-> `'SK'`, `'United States'` -> `'US'`, then you can use `.str.split()` and know that the first element is the country and everything else can go in the second column – G. Anderson Apr 22 '20 at 20:15
  • you'll probably need some logic to split then -maybe a city list. or a stringent logic that allows to split based only on the data-that you have to provide – Patrick Artner Apr 22 '20 at 20:15
  • 1
    [how-do-i-split-a-string-into-several-columns-in-a-dataframe-with-pandas-python](https://stackoverflow.com/questions/48958282/how-do-i-split-a-string-into-several-columns-in-a-dataframe-with-pandas-python) applies to splitting in general - not your special case – Patrick Artner Apr 22 '20 at 20:16
  • I created a list with all country names, listname = ['United States', 'China','Russia','United Kingdom','South Korea','Canada','Brazil','Germany','Japan'], don't know how to proceed. – Candice2020 Apr 22 '20 at 20:28
  • 2
    Combine @PatrickArtner's link above with [Replace part of the string in pandas data frame](https://stackoverflow.com/questions/42331992/replace-part-of-the-string-in-pandas-data-frame) with the list you created (make it a dictionary like `{'United Kingdom':'UK'...}` for the replacement) and you should be able to solve it – G. Anderson Apr 22 '20 at 20:52

0 Answers0