Iterating through rows and saving result in a new column

[kiton]

Hello everyone! I am seeking your help on the following task.

So, thankfully to you I was able to extract geo coordinates from a tweet, and save latitude/longitude as float64 in separate columns. Now, I need to convert coordinates to FIPS codes. The easiest way seems to be using -stateplane-:

import stateplane
stateplane.identify(-78.873165, 39.207769, fmt='fips') # lon first
'4701'

To create a new column with identifed FIPS code, I do the following:

for index, row in df.iterrow():
     df["FIPS"] = df[stateplane.identify('lon', 'lat', fmt='fips')]

But I guess I am doing it incorrectly -- i.e., specifying lon/lat columns to get values from, as I get the following error:

Error:
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
/Users/mymac/anaconda/lib/python3.6/site-packages/shapely/geometry/point.py in geos_point_from_py(ob, update_geom, update_ndim)
    201         # From array protocol
--> 202         array = ob.__array_interface__
    203         assert len(array['shape']) == 1

AttributeError: 'tuple' object has no attribute '__array_interface__'

During handling of the above exception, another exception occurred:

TypeError                                 Traceback (most recent call last)
<ipython-input-20-e8453e36b340> in <module>()
      1 for index, row in test.iterrows():
----> 2     text["FIPS"] = stateplane.identify('lon', 'lat', fmt='fips') # lon then lat

/Users/mymac/anaconda/lib/python3.6/site-packages/stateplane/stateplane.py in identify(lon, lat, fmt, statefp, countyfp)
     83     '''
     84 
---> 85     result = _id(lon, lat, statefp=statefp, countyfp=countyfp)
     86 
     87     try:

/Users/mymac/anaconda/lib/python3.6/site-packages/stateplane/stateplane.py in _id(lon, lat, statefp, countyfp)
     63         raise ValueError("SPCS not found for statefp={}".format(statefp))
     64 
---> 65     target = Point(lon, lat)
     66     result = None
     67 

/Users/mymac/anaconda/lib/python3.6/site-packages/shapely/geometry/point.py in __init__(self, *args)
     47         BaseGeometry.__init__(self)
     48         if len(args) > 0:
---> 49             self._set_coords(*args)
     50 
     51     # Coordinate getters and setters

/Users/mymac/anaconda/lib/python3.6/site-packages/shapely/geometry/point.py in _set_coords(self, *args)
    127             self._geom, self._ndim = geos_point_from_py(args[0])
    128         else:
--> 129             self._geom, self._ndim = geos_point_from_py(tuple(args))
    130 
    131     coords = property(BaseGeometry._get_coords, _set_coords)

/Users/mymac/anaconda/lib/python3.6/site-packages/shapely/geometry/point.py in geos_point_from_py(ob, update_geom, update_ndim)
    233             coords = ob
    234         n = len(coords)
--> 235         dx = c_double(coords[0])
    236         dy = c_double(coords[1])
    237         dz = None

TypeError: must be real number, not str

Please advise how to do it right.

[Larz60+]

The only FIPS code that I am aware of is: Federal Information Processing Standards (USA)
what does fips stand for here?

As for the question, need to see the structure of the df list.

[zivoni]

To create a new column with identifed FIPS code, I do the following:

for index, row in df.iterrow():
     df["FIPS"] = df[stateplane.identify('lon', 'lat', fmt='fips')]

This code contains quite big amount of errors. Iterating over df.iterrows() (not df.iterrow()) means iterating over pairs (index, row) as you correctly use in for clausule, but you dont use it at all in the body of the loop...  steteplane.identify() seems to accept only numerical arguments for longitude and latitude, not 'lon' and 'lat' strings (it cant know that these should be substitued with values of appropriate row of dataframe). Morever indexing dataframe with returning value of identify would almost never work, neither trying to assign it to a new df column.

It is possible to rewrite your for loop to work as you want:

df['FIPS'] = 0  # dummy value
for index, row in df.iterrows():
    df['FIPS'][index] = stateplane.identify(row['lon'], row['lat'], fmt='fips')

But standard (and more effective) way to apply some function over dataframe columns or rows is to use .apply():

df['fips'] = df.apply(lambda row: stateplane.identify(row['lon'], row['lat'], fmt='FIPS'), axis=1)

[kiton]

zivoni, your code (bottom one) worked perfectly. I am so thankful for your help. There is a lot for me to learn form you.

Larz60+, I appreciate your feedback as well. Thank you!

[kiton]

Update for future viewers of this post: -stateplane- appeared to be a bit tricky, because their FIPS (The Federal Information Processing Standard) is not a "classic" FIPS (https://www.nrcs.usda.gov/wps/portal/nrc...143_013697), but rather an ESPG one (https://www.epsg-registry.org). I am not sure of all the details related to the differences between the two, but undoubtedly they are different.

Therefore, those interested in the former ("classic") FIPS should obtain those via FCC API. Adapting zivoni's code, here is a solution:

from fcc.census_block_conversions import census_block_fips
df['fips'] = df.apply(lambda row: census_block_fips(row['lat'], row['lon']), axis=1)

P.S. I am running 2.7Million rows now, we'll see how long it takes.