How to access substrings in pandas column and store it into new columns?

Question

I'm working on a dataset for building permits. In the dataset there is a column that gives the location (lattitude and longitude) for the building permit. The data in the location column look like this:

0    (37.785719256680785, -122.40852313194863)
1     (37.78733980600732, -122.41063199757738)
2      (37.7946573324287, -122.42232562979227)
3     (37.79595867909168, -122.41557405519474)
4     (37.78315261897309, -122.40950883997789)
Name: location, dtype: object

As you can see, the data is stored as strings. I wanted to store the lattitude and longitude in two separate columns, so I wrote the following code to accomplish this:

df.location = df.location.str.replace('(','')
df.location = df.location.str.replace(')','')


for i in range(len(df)):
    if df.location[i] == np.nan:
        df['lattitude'] = np.nan
    else:
        df['lattitude'] = df.location[i][0:df['location'][i].index(',')]

for i in range(len(df)):
    if df.location[i] == np.nan:
        df['longitude'] = np.nan
    else:
        df['longitude'] = df.location[i][0:df['location'][i].index(',')]

There is some missing data in the column, 1700 entries to be exact. So in order to avoid a key error, I wrote the if-else statement to fill in the new columns with np.nan anytime the loop would iterate to a missing entry.

When I ran the code, I got the following error:

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-72-81826748e81c> in <module>()
      7         df['lattitude'] = np.nan
      8     else:
----> 9         df['lattitude'] = df.location[i][0:df['location'][i].index(',')]
     10 
     11 for i in range(len(df)):

AttributeError: 'float' object has no attribute 'index'

Can anybody spot the error I'm making?

Answer 1

This is perhaps more suited for StackOverflow. I would also use a better/more descriptive title for the question itself; that way others that are facing a similar problem are able to find it.

The reason you are seeing that error is because of the nan values, which are of type float. So while most of the rows in df['location'] contain strings, every row instance of an nan in the column is a float, and str.index() is not available for floats.

Your check of if df.location[i] == np.nan: is pointless, because np.nan == np.nan is always False due to the very definition of nan. Refer to this question on the topic. Because your check fails, the loop enters the else block and encounters a float object attempting to invoke a string method.

In my opinion you are using a very complicated approach to get what you want.

Replace your code with this. It should give you what you are looking for. Any nan values encountered will be handled by python.

df['location']=df['location'].str.replace(" ","").str.strip('(').str.strip(')')
df['latitude']=df['location'].str.split(',').str[0]
df['longitude']=df['location'].str.split(',').str[1]

I tested this using the following code segment:

df=pd.DataFrame()

df['location']=(
"(37.785719256680785, -122.40852313194863)",
"(37.78733980600732, -122.41063199757738)",
"(37.7946573324287, -122.42232562979227)",
"(37.79595867909168, -122.41557405519474)",
"(37.78315261897309, -122.40950883997789)",
np.nan,
"(37.78615261897309, -122.405550883997789)")

df['location']=df['location'].str.replace(" ", "").str.strip('(').str.strip(')')
df['latitude']=df['location'].str.split(',').str[0]
df['longitude']=df['location'].str.split(',').str[1]

print(df[['latitude','longitude']])

This produces the output:

             latitude             longitude
0  37.785719256680785   -122.40852313194863
1   37.78733980600732   -122.41063199757738
2    37.7946573324287   -122.42232562979227
3   37.79595867909168   -122.41557405519474
4   37.78315261897309   -122.40950883997789
5                 NaN                   NaN
6   37.78615261897309  -122.405550883997789

Answer 2

If I got it right from this one, the df['location'][i] should be some kind of a float and you can't get index from the float type. Check the type of the df['location'][i] with the isinstance(df['location'][i], float) you don't need to but just so you can see that it is really float. The error tells you everything. Maybe you are expecting string? There is not really much to say.

Answer 3

Hope you have figured out what cause the error.

However, I will propose another way to create new columns latitude and longitude from existing column location.

If values in column location are string, then

t = df.loc[df.location.isnull()]  # keep NaN rows in t for awhile
df.dropna(inplace=True)           # get rid of NaN rows

# create new columns
df['latitude'] = df.location.apply(lambda x: x.strip('()').split(',')[0])
df['longitude'] = df.location.apply(lambda x: x.strip('()').split(',')[1])

# put NaN rows back in their place
df = df.append(t, sort=False).sort_index()

But if values in column location are tuple of 2 float numbers, create new columns by this:

# create new columns
df['latitude'] = df.location.apply(lambda x: x[0])
df['longitude'] = df.location.apply(lambda x: x[1])