Panda's dataframe split a column into multiple columns

Starting with this:

df = pd.DataFrame({'Date': ['somedate', 'somedate'],
                   'location': ['united_kingdom_london', 'united_state_newyork'],
                   'occurence': [5, 5]})

Try this:

df['Country'] = df['location'].str.rpartition('_')[0].str.replace("_", " ")
df['City']    = df['location'].str.rpartition('_')[2]
df[['Date','Country', 'City', 'occurence']]

      Date        Country      City  occurence
0  somedate  united kingdom   london          5
1  somedate    united state  newyork          5

Borrowing idea from @MaxU

df[['Country'," " , 'City']] = (df.location.str.replace('_',' ').str.rpartition(' ', expand= True ))
df[['Date','Country', 'City','occurence' ]]

      Date        Country      City  occurence
0  somedate  united kingdom   london          5
1  somedate    united state  newyork          5

Consider splitting the column's string value using rfind()

import pandas as pd

df = pd.DataFrame({'Date': ['somedate', 'somedate'],
                   'location': ['united_kingdom_london', 'united_state_newyork'],
                   'occurence': [5, 5]})

df['country'] = df['location'].apply(lambda x: x[0:x.rfind('_')])
df['city'] = df['location'].apply(lambda x: x[x.rfind('_')+1:])

df = df[['Date', 'country', 'city', 'occurence']]
print(df)

#        Date         country     city  occurence
# 0  somedate  united_kingdom   london          5
# 1  somedate    united_state  newyork          5

Try this:

temp_location = {}
splits = df['location'].str.split(' ')
temp_location['country'] = splits[0:-1].tolist() 
temp_location['city'] = splits[-1].tolist() 

location_data = pd.DataFrame(temp_location)

If you want it back in the original df:

df['country'] = splits[0:-1].tolist() 
df['city'] = splits[-1].tolist() 

Something like this works

import pandas as pd

df = pd.DataFrame({'Date': ['somedate', 'somedate'],
                   'location': ['united_kingdom_london', 'united_state_newyork'],
                   'occurence': [5, 5]})

df.location = df.location.str[::-1].str.replace("_", " ", 1).str[::-1]
newcols = df.location.str.split(" ")
newcols = pd.DataFrame(df.location.str.split(" ").tolist(),
                         columns=["country", "city"])
newcols.country = newcols.country.str.replace("_", " ")
df = pd.concat([df, newcols], axis=1)
df.drop("location", axis=1, inplace=True)
print(df)

         Date  occurence         country     city
  0  somedate          5  united kingdom   london
  1  somedate          5    united state  newyork

You could use regex in the replace for a more complicated pattern but if it's just the word after the last _ I find it easier to just reverse the str twice as a hack rather than fiddling around with regular expressions

I would use .str.extract() method:

In [107]: df
Out[107]:
       Date               location  occurence
0  somedate  united_kingdom_london          5
1  somedate   united_state_newyork          5
2  somedate         germany_munich          5

In [108]: df[['country','city']] = (df.location.str.replace('_',' ')
   .....:                             .str.extract(r'(.*)\s+([^\s]*)', expand=True))

In [109]: df
Out[109]:
       Date               location  occurence         country     city
0  somedate  united_kingdom_london          5  united kingdom   london
1  somedate   united_state_newyork          5    united state  newyork
2  somedate         germany_munich          5         germany   munich

In [110]: df = df.drop('location', 1)

In [111]: df
Out[111]:
       Date  occurence         country     city
0  somedate          5  united kingdom   london
1  somedate          5    united state  newyork
2  somedate          5         germany   munich

PS please be aware that it's not possible to parse properly (to distinguish) between rows containing two-words country + one-word city and rows containing one-word country + two-words city (unless you have a full list of countries so you check it against this list)...