How to split strings inside a numpy array?

Question

I have the following table:

As the column 'location' has the state repeating inside it, I am trying to remove the state from location so that it only has the city name.

year    location    state   success
2009    New York, NY    NY  1
2009    New York, NY    NY  1
2009    Chicago, IL IL  1
2009    New York, NY    NY  1
2009    Boston, MA  MA  1
2009    Long Beach, CA  CA  1
2009    Atlanta, GA GA  1

I have tried the following code:

x = KS_clean.column(1)
np.chararray.split(x, ',')

How can I split the string so the result only contains the city name like the following:

array('New York', 'New York', 'Chicago', ...,) 

so that I can put it back inside the table?

Sorry it is basic question but I am new to python and still learning. Thanks

Answer 1

I think you need working with DataFrame first (e.g. by read_csv):

import numpy as np
from pandas.compat import StringIO

temp=u"""year;location;state;success
2009;New York, NY;NY;1
2009;New York, NY;NY;1
2009;Chicago, IL;IL;1
2009;New York, NY;NY;1
2009;Boston, MA;MA;1
2009;Long Beach, CA;CA;1
2009;Atlanta, GA;GA;1"""
#after testing replace 'StringIO(temp)' to 'filename.csv'
df = pd.read_csv(StringIO(temp), sep=";")

print (type(df))
<class 'pandas.core.frame.DataFrame'>

print (df)
   year        location state  success
0  2009    New York, NY    NY        1
1  2009    New York, NY    NY        1
2  2009     Chicago, IL    IL        1
3  2009    New York, NY    NY        1
4  2009      Boston, MA    MA        1
5  2009  Long Beach, CA    CA        1
6  2009     Atlanta, GA    GA        1

Then split by str.split and select first list by str[0]:

df['location'] = df['location'].str.split(', ').str[0]
print (df)
   year    location state  success
0  2009    New York    NY        1
1  2009    New York    NY        1
2  2009     Chicago    IL        1
3  2009    New York    NY        1
4  2009      Boston    MA        1
5  2009  Long Beach    CA        1
6  2009     Atlanta    GA        1

Last if necessary convert by values to numpy array:

arr = df.values
print (arr)
[[2009 'New York' 'NY' 1]
 [2009 'New York' 'NY' 1]
 [2009 'Chicago' 'IL' 1]
 [2009 'New York' 'NY' 1]
 [2009 'Boston' 'MA' 1]
 [2009 'Long Beach' 'CA' 1]
 [2009 'Atlanta' 'GA' 1]]