Extracting City and State from a column in CSV using python

Question

917 1st St, Dallas, TX 75001
682 Chestnut St, Boston, MA 02215
669 Spruce St, Los Angeles, CA 90001
669 Spruce St, Los Angeles, CA 90001

so, i'm trying to extract the city and state from the given data...

def get_city_state(address):
    asplit = address.split(",")
    ssplit = address.split(" ")
    city = asplit[1].split()[-1]
    state = asplit[2].split()[0]
    return city , state


all_data['City'] = all_data['Purchase Address'].apply(lambda x: f"{get_city_state(x)}")
all_data.head()

What's the problem? If you want to return multiple values in apply, you can look at [Returning multiple variables with pandas.series.apply](https://stackoverflow.com/questions/72176826/returning-multiple-variables-with-pandas-series-apply). — Ynjxsjmh, May 10 '22 at 14:28
Maybe you want `all_data[['City', 'State']] = all_data['Purchase Address'].apply(get_city_state).tolist()`? — Ynjxsjmh, May 10 '22 at 14:30

score 0 · Accepted Answer · answered May 10 '22 at 14:42

do you want this ?

all_data = pd.DataFrame({'Purchase Address': ['917 1st St, Dallas, TX 75001',
                               '682 Chestnut St, Boston, MA 02215',
                               '669 Spruce St, Los Angeles, CA 90001',
                               '669 Spruce St, Los Angeles, CA 90001']})

Just the city :

def get_city_state(address):
    asplit = address.split(",")
    ssplit = address.split(" ")
    city = asplit[1].split()[-1]
    state = asplit[2].split()[0]
    return city 


all_data['City'] = all_data['Purchase Address'].apply(get_city_state).to_list()

Just the States :

def get_city_state(address):
        asplit = address.split(",")
        ssplit = address.split(" ")
        city = asplit[1].split()[-1]
        state = asplit[2].split()[0]
        return states 
    
    
    all_data['States'] = all_data['Purchase Address'].apply(get_city_state).to_list()

Both :

def get_city_state(address):
    asplit = address.split(",")
    ssplit = address.split(" ")
    city = asplit[1].split()[-1]
    state = asplit[2].split()[0]
    return city , state


all_data[['City', 'State']] = all_data['Purchase Address'].apply(get_city_state).to_list()

Output :

score 0 · Answer 2 · answered May 10 '22 at 14:48

The easiest way is using Pandas. I suppose that you have tha data ina csv file or you can edit them to a csv file. Then:

1º Import pandas:

import pandas as pd

2º Create a dataframe with your data (it can be csv, jason, xls, ...). In this case code refers a comma separated csv file:

pd.read_csv('path or url of csv file', sep = ',')

Output: Prints your dataframe After thar, you can edit the line and assign to a var:

df = pd.read_csv('path or url of csv file', sep = ',')

Now, to extract the columns you need:

df(['City','State'])

Saying “thanks” is appreciated, but it doesn’t answer the question. Instead, vote up the answers that helped you the most! If these answers were helpful to you, please consider saying thank you in a more constructive way – by contributing your own answers to questions your peers have asked here.

Extracting City and State from a column in CSV using python

2 Answers2