notebook.community

Edit and run

OOI Equipment mapping

  • 6/22: tweaked and tested by Emilio
  • by Landung Setiawan
  • 6/14/2016
  • This notebook is for retrieving information from google sheets and then mapping to a JSON file, each instrument has its own JSON file configuration
  • The required libraries for this manipulation is gspread, oauth2client, and pycrypto

TO-DO:

  • 6/22: CHANGE depth_m TO BE A STRING? eg, '-1m'
  • Handle "measurements" sheet, too




In [1]:



    


import os
import json
import numpy as np
import pandas as pd



    











In [2]:



    


def get_googlesheet_doc(gdocjson_pth, doc_name):
    import oauth2client
    import gspread
    
    # Get Google docs json token and scope for google sheets
    gdocstoken_json = os.path.join(gdocjson_pth, '.gdocs_Nanoos-fcdeeb760f83.json')
    scope = ['https://spreadsheets.google.com/feeds']

    # Retrieve credentials from JSON key of service account
    # oauth_ver = oauth2client.__version__
    try:
        from oauth2client.service_account import ServiceAccountCredentials
        credentials = ServiceAccountCredentials.from_json_keyfile_name(gdocstoken_json, scope)
    except:
        from oauth2client.client import SignedJwtAssertionCredentials
        with open(gdocstoken_json) as f:
            json_key = json.load(f)
        credentials = SignedJwtAssertionCredentials(json_key['client_email'],
                                                    json_key['private_key'], scope)
    gc = gspread.authorize(credentials)
    sheetgdoc = gc.open(doc_name)
    
    return sheetgdoc

Testing with vizer google credentials





In [3]:



    


import vizer.tsharvest.util as vhutil

vizer = vhutil.Vizer('nvs', False)



    











In [4]:



    


gdoc = get_googlesheet_doc(vizer.vizerspath, "sensor_configurations_mappings")



    











In [5]:



    


sheet = gdoc.worksheet('instruments')

Parsing data to a pandas dataframe

Now that connection has been established, data is parsed to be viewed





In [6]:



    


sheetvalues = sheet.get_all_values()



    











In [7]:



    


# Convert data into pandas dataframe
df = pd.DataFrame(sheetvalues[1:], columns=sheetvalues[0])
df = df.convert_objects(convert_numeric=True)
df.head()



    


















    






/home/mayorga/miniconda/envs/uwapl_em_mc_1aui/lib/python2.7/site-packages/ipykernel/__main__.py:3: FutureWarning: convert_objects is deprecated.  Use the data-type specific converters pd.to_datetime, pd.to_timedelta and pd.to_numeric.
  app.launch_new_instance()










    
Out[7]:










  
    

      
      platform
      depth_m
      mfn
      deployment
      data_logger
      instrument
      subtype
      magnetic_declin_correction
      raw_url
    

  
  
    

      0
      CE01ISSM
      -7
      
      D00005
      dcl16
      ctdbp1
      ctd_type::2
      NaN
      https://rawdata.oceanobservatories.org/files/C...
    

    

      1
      CE01ISSM
      -25
      x
      D00001
      dcl37
      ctdbp2
      ctd_type::2
      NaN
      https://rawdata.oceanobservatories.org/files/C...
    

    

      2
      CE02SHBP
      -80
      
      
      LJ01D
      
      
      NaN
      https://rawdata.oceanobservatories.org/files/C...
    

    

      3
      CE02SHSM
      -7
      
      D00003
      cg_data/dcl27
      ctdbp
      ctd_type::1
      NaN
      https://rawdata.oceanobservatories.org/files/C...
    

    

      4
      CE02SHSM
      0
      
      D00003
      cg_data/dcl11
      metbk
      
      15.58
      https://rawdata.oceanobservatories.org/files/C...
    

  




















In [8]:



    


# Get Platforms
json_data = df[['platform', 'instrument', 'depth_m', 'mfn',
                'deployment', 'data_logger', 'subtype',
                'magnetic_declin_correction']].reset_index(drop=True)
platforms = json_data['platform'].unique()



    











In [9]:



    


# Create platform dictionary. Eliminate instruments with blank instrument strings,
# and platforms containing only such instruments.
platforms_dct = {}
for platform in platforms:
    instruments_df = json_data.loc[json_data['platform'] == platform]    
    instruments_tmp_dct = {}
    for idx, instruments_df_row in instruments_df.iterrows():
        row_dct = instruments_df_row.to_dict()
        instrument = row_dct['instrument']
        
        row_dct['mfn'] = True if row_dct['mfn'] == 'x' else False
        if row_dct['subtype'] != '':
            row_dct['subtype'] = int(row_dct['subtype'].split('::')[1])
        else:
            row_dct['subtype'] = None
        if np.isnan(row_dct['magnetic_declin_correction']):
            row_dct['magnetic_declin_correction'] = None
        
        row_dct.pop('platform', None)
        row_dct.pop('instrument', None)
        
        if len(instrument) > 1:
            instruments_tmp_dct[instrument] = row_dct
        
    if instruments_tmp_dct:
        platforms_dct[platform] = instruments_tmp_dct



    











In [10]:



    


platforms_dct.keys()



    


















    
Out[10]:








['CE04OSSM', 'CE02SHSM', 'CE01ISSM', 'CE06ISSM', 'CE09OSSM', 'CE07SHSM']

















In [11]:



    


platforms_dct['CE09OSSM'].keys()



    


















    
Out[11]:








['wavss', 'metbk', 'ctdbp1', 'ctdbp2']

















In [13]:



    


# prints the JSON structured dictionary
jsont_str = json.dumps(platforms_dct, sort_keys=True, indent=4)
print(jsont_str)



    


















    






{
    "CE01ISSM": {
        "ctdbp1": {
            "data_logger": "dcl16", 
            "deployment": "D00005", 
            "depth_m": -7, 
            "magnetic_declin_correction": null, 
            "mfn": false, 
            "subtype": 2
        }, 
        "ctdbp2": {
            "data_logger": "dcl37", 
            "deployment": "D00001", 
            "depth_m": -25, 
            "magnetic_declin_correction": null, 
            "mfn": true, 
            "subtype": 2
        }
    }, 
    "CE02SHSM": {
        "ctdbp": {
            "data_logger": "cg_data/dcl27", 
            "deployment": "D00003", 
            "depth_m": -7, 
            "magnetic_declin_correction": null, 
            "mfn": false, 
            "subtype": 1
        }, 
        "metbk": {
            "data_logger": "cg_data/dcl11", 
            "deployment": "D00003", 
            "depth_m": 0, 
            "magnetic_declin_correction": 15.58, 
            "mfn": false, 
            "subtype": null
        }, 
        "wavss": {
            "data_logger": "cg_data/dcl12", 
            "deployment": "D00003", 
            "depth_m": 0, 
            "magnetic_declin_correction": null, 
            "mfn": false, 
            "subtype": null
        }
    }, 
    "CE04OSSM": {
        "ctdbp": {
            "data_logger": "cg_data/dcl27", 
            "deployment": "D00002", 
            "depth_m": -7, 
            "magnetic_declin_correction": null, 
            "mfn": false, 
            "subtype": 1
        }, 
        "metbk": {
            "data_logger": "cg_data/dcl11", 
            "deployment": "D00002", 
            "depth_m": 0, 
            "magnetic_declin_correction": 15.6, 
            "mfn": false, 
            "subtype": null
        }, 
        "wavss": {
            "data_logger": "cg_data/dcl12", 
            "deployment": "D00002", 
            "depth_m": 0, 
            "magnetic_declin_correction": null, 
            "mfn": false, 
            "subtype": null
        }
    }, 
    "CE06ISSM": {
        "ctdbp1": {
            "data_logger": "dcl16", 
            "deployment": "D00004", 
            "depth_m": -7, 
            "magnetic_declin_correction": null, 
            "mfn": false, 
            "subtype": 2
        }, 
        "ctdbp2": {
            "data_logger": "dcl37", 
            "deployment": "D00004", 
            "depth_m": -29, 
            "magnetic_declin_correction": null, 
            "mfn": true, 
            "subtype": 2
        }
    }, 
    "CE07SHSM": {
        "ctdbp1": {
            "data_logger": "cg_data/dcl27", 
            "deployment": "D00003", 
            "depth_m": -7, 
            "magnetic_declin_correction": null, 
            "mfn": false, 
            "subtype": 1
        }, 
        "ctdbp2": {
            "data_logger": "cg_data/dcl37", 
            "deployment": "D00003", 
            "depth_m": -87, 
            "magnetic_declin_correction": null, 
            "mfn": true, 
            "subtype": 2
        }, 
        "metbk": {
            "data_logger": "cg_data/dcl11", 
            "deployment": "D00003", 
            "depth_m": 0, 
            "magnetic_declin_correction": 16.21, 
            "mfn": false, 
            "subtype": null
        }, 
        "wavss": {
            "data_logger": "cg_data/dcl12", 
            "deployment": "D00003", 
            "depth_m": 0, 
            "magnetic_declin_correction": null, 
            "mfn": false, 
            "subtype": null
        }
    }, 
    "CE09OSSM": {
        "ctdbp1": {
            "data_logger": "cg_data/dcl27", 
            "deployment": "D00003", 
            "depth_m": -7, 
            "magnetic_declin_correction": null, 
            "mfn": false, 
            "subtype": 1
        }, 
        "ctdbp2": {
            "data_logger": "cg_data/dcl37", 
            "deployment": "D00001", 
            "depth_m": -540, 
            "magnetic_declin_correction": null, 
            "mfn": true, 
            "subtype": 2
        }, 
        "metbk": {
            "data_logger": "cg_data/dcl11", 
            "deployment": "D00003", 
            "depth_m": 0, 
            "magnetic_declin_correction": 16.23, 
            "mfn": false, 
            "subtype": null
        }, 
        "wavss": {
            "data_logger": "cg_data/dcl12", 
            "deployment": "D00003", 
            "depth_m": 0, 
            "magnetic_declin_correction": null, 
            "mfn": false, 
            "subtype": null
        }
    }
}

Output to JSON





In [14]:



    


fpth = os.path.join(vizer.vizerspath, 'nvs', 'siso_ooi_harvest.json')
with open(fpth, 'w') as fojson:
    fojson.write(jsont_str)

Content source: rsignell-usgs/notebook

Similar notebooks:

notebook.community | gallery | about