In [11]:
import pandas as pd
from glob import glob
from simpledbf import Dbf5
import os
import pyproj
import numpy as np
import warnings

In [12]:
files = glob('C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\*.dbf')
files


Out[12]:
['C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\bello.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\callaqui.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\cipreses_norte.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\del_potro.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\del_rincon.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\echaurren.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\esmeralda.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\juncal_n.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\la_paloma.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\llaima.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\marmolejo.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\meson_alto.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\mocho.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\mocho_terrestre.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\osorno.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\plomo.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\rio_blanco.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\san_francisco.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\sollipulli.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\s_velluda.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\tapado.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\tapado_terrestre.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\tinguiririca_1.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\tinguiririca_3.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\tinguiririca_4.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\tronador.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\tronquitos.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\tupungatito.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\tupungatito_terrestre.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\universidad.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\villarrica.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\villarrica_terrestre.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\yeso.dbf',
 'C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\Schaefer_rawdata-chile\\DD\\yeso_2.dbf']

Echaurren and Plomo have no GENERAL entry by M. Schaefer


In [13]:
pop_list = ['del_potro','tronquitos','apado','juncal_n','rio_blanco','tupungatito','marmolejo', 'echaurren', 'terrestre']

In [14]:
for i in files:
    print(i)
    for j in pop_list:
        if j in i:
            files.remove(i)
files


C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\bello.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\callaqui.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\cipreses_norte.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\del_potro.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\echaurren.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\juncal_n.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\llaima.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\marmolejo.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\mocho.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\mocho_terrestre.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\plomo.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\rio_blanco.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\sollipulli.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\s_velluda.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\tapado.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\tinguiririca_1.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\tinguiririca_3.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\tinguiririca_4.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\tronador.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\tronquitos.dbf
C:\Users\jlandman\Desktop\newData\CL_Schaefer\Schaefer_rawdata-chile\DD\tupungatito_terrestre.dbf
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-14-866401fd24c5> in <module>()
      3     for j in pop_list:
      4         if j in i:
----> 5             files.remove(i)
      6 files

ValueError: list.remove(x): x not in list

In [15]:
thin_dict = {'bello':11,
             'callaqui':21,
             'cipreses_norte':43,
             'del_rincon':4,
             'echaurren':1,
             'esmeralda':15,
             'la_paloma':7,
             'llaima':16,
             'meson_alto':11,
             'mocho':28,
             'mocho_terrestre':4,
             'osorno':37,
             'plomo':9,
             'san_francisco':4,
             'sollipulli':9,
             's_velluda':32,
             'tinguiririca_1':16,
             'tinguiririca_3':12,
             'tinguiririca_4':9,
             'tronador':18,
             'universidad':20,
             'villarrica':44,
             'villarrica_terrestre':5,
             'yeso':9,
             'yeso_2':4
             }

name_dict = {'bello':'Bello',
             'callaqui':'Callaqui',
             'cipreses_norte':'Cipreses Norte',
             'del_rincon':'Del Rincon',
             'echaurren':'Echaurren',
             'esmeralda':'Esmeralda',
             'la_paloma':'La Paloma',
             'llaima':'Llaima',
             'meson_alto':'Meson Alto',
             'mocho':'Mocho-Chosh',
             'mocho_terrestre':'Mocho-Chosh',
             'osorno':'Osorno',
             'plomo':'Cerro el Plomo',
             'san_francisco':'San Francisco',
             'sollipulli':'Sollipulli',
             's_velluda':'Velluda',
             'tinguiririca_1':'Tinguiririca 1',
             'tinguiririca_3':'Tinguiririca 3',
             'tinguiririca_4':'Tinguiririca 4',
             'tronador':'Tronador',
             'universidad':'Universidad',
             'villarrica':'Villarrica',
             'villarrica_terrestre':'Villarrica',
             'yeso':'Yeso',
             'yeso_2':'Yeso 2'
             }

In [16]:
# read the T table
general = pd.read_excel('C:\\Users\\jlandman\\Desktop\\newData\\CL_Schaefer\\GlaThiDa2-0_MS_Johannes.xls')

In [17]:
for file in files:
    new_data = pd.DataFrame([])
    fname = os.path.basename(file)
    dbf = Dbf5(file)
    data = dbf.to_dataframe()
    print(fname)
    # old point thinning
    # data = data.groupby(data.index // thin_dict[fname.split('.')[0]]).mean() # average over every x values as specified above
    
    # ice thickness
    try:
        new_data['THICKNESS'] = data['esp'].map('{:.1f}'.format)
    except KeyError:
        try:
            new_data['THICKNESS'] = data['Espesor'].map('{:.1f}'.format)
        except KeyError:
            new_data['THICKNESS'] = data['Esp'].map('{:.1f}'.format)
            
    # lat
    try:
        new_data['POINT_LAT'] = data['y'].map('{:.4f}'.format)
    except KeyError:
        try:
            new_data['POINT_LAT'] = data['Norte'].map('{:.4f}'.format)
        except KeyError:
            new_data['POINT_LAT'] = data['N'].map('{:.4f}'.format)
        
    # lon
    try:
        new_data['POINT_LON'] = data['x'].map('{:.4f}'.format)
    except KeyError:
        try:
            new_data['POINT_LON'] = data['Este'].map('{:.4f}'.format)
        except KeyError:
            new_data['POINT_LON'] = data['E'].map('{:.4f}'.format)
    
    new_data['ELEVATION'] = np.nan
    new_data['THICKNESS_UNCERTAINTY'] = np.nan
    new_data['GLACIER_NAME'] = general[general.GLACIER_NAME == name_dict[os.path.split(file)[1].split('.')[0]]].GLACIER_NAME.iloc[0]
    new_data['GlaThiDa_ID'] = general[general.GLACIER_NAME == name_dict[os.path.split(file)[1].split('.')[0]]].GlaThiDa_ID.iloc[0]
    new_data['POLITICAL_UNIT'] = general[general.GLACIER_NAME == name_dict[os.path.split(file)[1].split('.')[0]]].POLITICAL_UNIT.iloc[0]
    new_data['SURVEY_DATE'] = general[general.GLACIER_NAME == name_dict[os.path.split(file)[1].split('.')[0]]].SURVEY_DATE.iloc[0]
    new_data['POINT_ID'] = range(1, len(new_data)+1)
    new_data['DATA_FLAG'] = np.nan
    new_data['REMARKS'] = 'Point data have been thinned (mean) within a search distance of %s m.' % search_dist
    
    new_data = new_data[['GlaThiDa_ID','POLITICAL_UNIT','GLACIER_NAME','SURVEY_DATE','POINT_ID','POINT_LAT','POINT_LON',
                         'ELEVATION','THICKNESS','THICKNESS_UNCERTAINTY','DATA_FLAG','REMARKS']]

    new_data.to_excel(os.path.join(os.path.dirname(file),fname.split('.')[0]+'.xls'), index=False)


bello.dbf
---------------------------------------------------------------------------
IndexError                                Traceback (most recent call last)
<ipython-input-17-0997804a59a9> in <module>()
     37     new_data['ELEVATION'] = np.nan
     38     new_data['THICKNESS_UNCERTAINTY'] = np.nan
---> 39     new_data['GLACIER_NAME'] = general[general.GLACIER_NAME == name_dict[os.path.split(file)[1].split('.')[0]]].GLACIER_NAME.iloc[0]
     40     new_data['GlaThiDa_ID'] = general[general.GLACIER_NAME == name_dict[os.path.split(file)[1].split('.')[0]]].GlaThiDa_ID.iloc[0]
     41     new_data['POLITICAL_UNIT'] = general[general.GLACIER_NAME == name_dict[os.path.split(file)[1].split('.')[0]]].POLITICAL_UNIT.iloc[0]

C:\Users\jlandman\Anaconda3\lib\site-packages\pandas\core\indexing.py in __getitem__(self, key)
   1284             return self._getitem_tuple(key)
   1285         else:
-> 1286             return self._getitem_axis(key, axis=0)
   1287 
   1288     def _getitem_axis(self, key, axis=0):

C:\Users\jlandman\Anaconda3\lib\site-packages\pandas\core\indexing.py in _getitem_axis(self, key, axis)
   1571 
   1572                 # validate the location
-> 1573                 self._is_valid_integer(key, axis)
   1574 
   1575             return self._get_loc(key, axis=axis)

C:\Users\jlandman\Anaconda3\lib\site-packages\pandas\core\indexing.py in _is_valid_integer(self, key, axis)
   1485         l = len(ax)
   1486         if key >= l or key < -l:
-> 1487             raise IndexError("single positional indexer is out-of-bounds")
   1488         return True
   1489 

IndexError: single positional indexer is out-of-bounds

In [ ]: