python - Panda's dataframe split a column into multiple columns -

i have pandas dataframe looks below:

date     |    location          | occurance <br> ------------------------------------------------------ somedate |united_kingdom_london | 5   somedate |united_state_newyork  | 5    

i want transform

date     | country        | city    | occurance <br> --------------------------------------------------- somedate | united kingdom | london  | 5   --------------------------------------------------- somedate | united state   | newyork | 5      

i new python , after research have written following code, seems unable extract country , city:

df.location= df.location.replace({'-': ' '}, regex=true) df.location= df.location.replace({'_': ' '}, regex=true)  temp_location = df['location'].str.split(' ').tolist()   location_data = pd.dataframe(temp_location, columns=['country', 'city']) 

i appreciate response.

starting 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 @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