def _get_closed_and_cancelled_statusid(): sql = """ select SubStatusID from InstallationSubStatus where StatusID IN (5,7) """ return list(sw.readsql(sql)['SubStatusID'].values)
def _get_status_history(): sql = """ select sh.InstallationID, sh.NewSubStatusID, sh.Changed from StatusHistory sh join Installations i on sh.InstallationID = i.InstallationID where i.markettypeid = 1 and i.producttypeid = 1 """ return sw.readsql(sql)
def _get_installation_status_names(): sql = """ select isst.SubStatusID, ist.StatusName, isst.SubStatusName from InstallationStatus ist join InstallationSubStatus isst on ist.StatusID = isst.StatusID """ return sw.readsql(sql)
from bonkers.io import solarworks as sw # import my solarworks macros import pandas as pd # import pandas which is the library that allows me to use # DataFrames and gives me access to functions like groupby, resample, etc sql = """ select top 10000 lf.LeaseFundName, fl.* from forecastledger fl join tranche t on fl.trancheid = t.trancheid join leasefund lf on lf.leasefundid = t.leasefundid """ df = sw.readsql(sql) # convert DueDate from a string to a datetime (don't worry about what map() and lambda functions do # just yet, we'll learn how useful they are later) df['DueDate'] = df['DueDate'].map(lambda dt: datetime.strptime(dt,'%Y-%m-%d')) def dummy(group): # group is a chunk of the ta ble that's being fed in one at a time temp = group.set_index('DueDate') # set this table's index to a date, which converts this table to a timeseries temp2 = temp['Amount'] # select out the Amount column temp3 = temp2.resample('m',how='sum') # resample my time series to months return temp # return the compressed table x = df.groupby('LeaseFundName') # group the table into separate chunks based on the fund name x = x.apply(dummy) # apply the dummy function to each chunk from the above grouping x = x.unstack() # unstack the dates so they become columns x.to_csv('test.csv') # output to a file