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
