In [50]:

    

import pandas as pd
import geopandas as gpd
import shapely.geometry as shpg
import salem


    

In [76]:

    

t = pd.read_excel('C:\\Users\\jlandman\\Desktop\\NEW_GTD_20\\TTT_GTD20_colors_removed.xlsx', skiprows=2)


    

In [77]:

    

t_su = t[t.POLITICAL_UNIT.str.contains('SU')]


    

    




---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-77-bb54f84b747d> in <module>()
----> 1 t_su = t[t.POLITICAL_UNIT.str.contains('SU')]

C:\Users\jlandman\Anaconda3\lib\site-packages\pandas\core\frame.py in __getitem__(self, key)
   1984         if isinstance(key, (Series, np.ndarray, Index, list)):
   1985             # either boolean or fancy integer index
-> 1986             return self._getitem_array(key)
   1987         elif isinstance(key, DataFrame):
   1988             return self._getitem_frame(key)

C:\Users\jlandman\Anaconda3\lib\site-packages\pandas\core\frame.py in _getitem_array(self, key)
   2011     def _getitem_array(self, key):
   2012         # also raises Exception if object array with NA values
-> 2013         if com.is_bool_indexer(key):
   2014             # warning here just in case -- previously __setitem__ was
   2015             # reindexing but __getitem__ was not; it seems more reasonable to

C:\Users\jlandman\Anaconda3\lib\site-packages\pandas\core\common.py in is_bool_indexer(key)
   1848             if not lib.is_bool_array(key):
   1849                 if isnull(key).any():
-> 1850                     raise ValueError('cannot index with vector containing '
   1851                                      'NA / NaN values')
   1852                 return False

ValueError: cannot index with vector containing NA / NaN values

In [ ]:

    

shapes = salem.utils.read_shapefile('C:\\Users\\jlandman\\Desktop\\gadm28.shp\\gadm_clip.shp')


    

In [ ]:

    

shapes.columns.values


    

In [ ]:

    

countries = {}

ct=0
for k, row in t_su.iterrows():
    gp = shpg.Point(row.LON, row.LAT)
    rectangle = shpg.Polygon([(row.LON-0.5, row.LAT-0.5), (row.LON-0.5, row.LAT+0.5), (row.LON+0.5, row.LAT-0.5), (row.LON+0.5, row.LAT-0.5)])
    subset = shapes[shapes.intersects(rectangle)]
    #if isinstance(subset, pd.DataFrame):
    for i, r in subset.iterrows():
        ct+=1
        if r.geometry.contains(gp):
            countries[row.GLACIER_NAME] = r['HASC_1'].split('.')[0]
            print(ct)
    """else:
        print(subset)
        countries[row.GLACIER_NAME] = subset.HASC_1.split('.')[0]"""

countries


    

In [ ]:

    

len(countries)


    

In [ ]:

    

test = pd.DataFrame(data={'name': t.GLACIER_NAME.values})
test['punit'] =''

for key, value in countries.items():
    t.loc[t.GLACIER_NAME == key, 'POLITICAL_UNIT'] = value
    t.loc[t.GLACIER_NAME == key, 'REMARKS'] = t.loc[t.GLACIER_NAME == key, 'REMARKS'] + ' POLITICAL_UNIT updated by spatial query with data from gadm.org.'


    

In [ ]:

    

t.to_excel('C:\\users\\jlandman\\Desktop\\TTT_GTD_20_colors_removed_SU_updated.xlsx', index=False)


    

In [ ]: