pandas utils for smartsheets

sspd.py

from smartsheet import *
import pandas as pd
from credentials import smartsheet_token

smartsheet = smartsheet.Smartsheet(smartsheet_token)

def get_sheet_as_df(sheet_id):
    ss1 = smartsheet.Sheets.get_sheet(sheet_id, page_size=0)
    row_count = ss1.total_row_count
    ss1 = smartsheet.Sheets.get_sheet(sheet_id, page_size=row_count)
    df = get_values(ss1)
    s2 = get_columns(ss1)
    df.columns = s2
    return df

def get_columns(ss):
    cl = ss.get_columns()
    d3 = cl.to_dict()
    df = pd.DataFrame(d3['data'])
    df = df.set_index('id')
    return df.title

def get_values(ss):
    d = ss.to_dict()
    drows = d['rows']
    rownumber = [x['rowNumber'] for x in drows]
    rows = [x['cells'] for x in drows]
    values = [[x['displayValue'] for x in y] for y in rows]
    return pd.DataFrame(values)

def check_overlaps(list1, list2):
#check for overlaps and remainders
#from http://stackoverflow.com/questions/5094083/find-the-overlap-between-2-python-lists
    intersection = set(list1) & set(list2)
    l1_remainder = set(list1) - set(list2)
    l2_remainder = set(list2) - set(list1)
    d = {'intersection':intersection,
        'list1_remainder': l1_remainder,
        'list2_remainder': l2_remainder
        }
    return d