def test_dataframe_1(self): _skip_if_no_pandas() df_expected = df_1 Range('Sheet5', 'A1').value = df_expected df_result = Range('Sheet5', 'A1:C5').options(pd.DataFrame).value df_result.index = pd.Int64Index(df_result.index) assert_frame_equal(df_expected, df_result)
def _get_statement_dates(self): ''' Populates self.start_date and self.end_date with the statement start and end dates respectively in datetime date format. If no statement dates available sets both fields to None. :return: ''' cell = 'C2' dates_string = Range(self.default_worksheet, cell, workbook=self.statement_workbook).value if dates_string: start_date_string = dates_string[:dates_string.index(' to')] self.start_date = datetime.strptime(start_date_string, self.date_format).date() end_date_string = dates_string[dates_string.index(' to') + 4:] self.end_date = datetime.strptime(end_date_string, self.date_format).date() else: self.start_date, self.end_date = None, None
def _get_account_number(self): ''' Populates self.account_number with the statement account number as a string. If no account number on statement returns None. :return: ''' cell = 'B3' account_string = Range(self.default_worksheet, cell, workbook=self.statement_workbook).value if account_string: self.account_number = account_string[account_string.index('Account Number: ') + 16:] else: self.account_number = None
def _get_account_holder(self): ''' Populates self.account_holder with the account holder as a string. If no account_holder in statement the returns None. :return: ''' cell = 'B2' account_holder_string = Range(self.default_worksheet, cell, workbook=self.statement_workbook).value if account_holder_string: self.account_holder = account_holder_string[account_holder_string.index('Prepared for: ') + 14:] else: self.account_holder = None
def test_read_df_2header_1unnamedindex(self): _skip_if_no_pandas() Range('A1').value = [[None, 'a', 'a', 'b'], [None, 'c', 'd', 'c'], [1, 1, 2, 3], [2, 4, 5, 6]] df1 = pd.DataFrame([[1., 2., 3.], [4., 5., 6.]], index=pd.Index([1, 2]), columns=pd.MultiIndex.from_arrays([['a', 'a', 'b'], ['c', 'd', 'c']])) df2 = Range('A1:D4').options(pd.DataFrame, header=2).value df2.index = pd.Int64Index(df2.index) assert_frame_equal(df1, df2)
from xlwings import Workbook, Range import numpy as np import pandas as pd ROOT_DIR = os.path.dirname(os.getcwd()) wb = Workbook(os.path.join(ROOT_DIR, 'reference/StaticData-mod.xlsx')) # Storage and Markets storages = Range('S->M', 'C1:BU1').value markets = [ '{0} ({1})'.format(row[1], row[0]) for row in Range('S->M', 'A2:B101', atleast_2d=True).value ] regions = Range('S->M', 'A2:A101').value D = np.array(Range('S->M', 'C2:BU101', atleast_2d=True).value) dist_df = pd.DataFrame({ 'region': regions, 'market': markets, 'S15_dist': D[:, storages.index('S15')], 'S61_dist': D[:, storages.index('S61')] }) av_dists = dist_df.groupby('region').mean() av_dists['d'] = np.array(av_dists.min(1)) av_dists.to_csv('region_storage_dists.csv')
from xlwings import Workbook, Range import numpy as np import pandas as pd ROOT_DIR = os.path.dirname(os.getcwd()) wb = Workbook(os.path.join(ROOT_DIR, 'reference/StaticData-mod.xlsx')) # Storage and Markets storages = Range('S->M', 'C1:BU1').value markets = ['{0} ({1})'.format(row[1], row[0]) for row in Range('S->M', 'A2:B101', atleast_2d=True).value] regions = Range('S->M', 'A2:A101').value D = np.array(Range('S->M', 'C2:BU101', atleast_2d=True).value) # [S35, S51, S59, S73], [P02, P03, P05, P09] dist_df = pd.DataFrame({'region': regions, 'market': markets, 'S35_dist': D[:, storages.index('S35')], 'S51_dist': D[:, storages.index('S51')], 'S59_dist': D[:, storages.index('S59')], 'S73_dist': D[:, storages.index('S73')]}) av_dists = dist_df.groupby('region').mean() av_dists['d'] = np.array(av_dists.min(1)) av_dists.to_csv('region_storage_dists_opt.csv')
import numpy as np import pandas as pd import os import itertools ROOT_DIR = os.path.dirname(os.getcwd()) wb = Workbook(os.path.join(ROOT_DIR, 'reference/StaticData-mod.xlsx')) # Storage and Plants storages = Range('P->S', 'A2:A72').value plants = Range('P->S', 'B1:K1').value D = np.array(Range('P->S', 'B2:K72', atleast_2d=True).value) # Only include the 4 storages from previous analysis. storages_fixed = (u'S35', u'S51', u'S59', u'S73') D_fixed = D[[storages.index(s) for s in storages_fixed], :] # Seek to minimize the sum of the distance from each storage # to its closest open processing plant. # P = 1 total_distances = {} for p in xrange(0, D_fixed.shape[1]): total_distances[p] = sum(D_fixed[:, p]) best = min(total_distances, key=total_distances.get) # Return set of best plants for a given plant size P, as well as # total distance for that plant size P. def get_best_plants(P): combs = list(itertools.combinations(xrange(0, len(plants)), P)) total_distances = {}
print ("""S = 4: {0} gives the """ """min total dist to markets = {1}""").format(best4, total_distances_4[best4]) opt_sm = { 1: (best, total_distances[best]), 2: (best2, total_distances_2[best2]), 3: (best3, total_distances_3[best3]), 4: (best4, total_distances_4[best4]), } import cPickle as pickle with open("opt_sm.pkl", "wb") as f: pickle.dump(opt_sm, f) # current storage system4 i = storages.index("S15") j = storages.index("S61") total_distance = sum([min(D[r, i], D[r, j]) for r in xrange(0, len(D))]) # Check 4th week of May transportation costs dists = [min(D[m, storages.index("S15")], D[m, storages.index("S61")]) for m in xrange(0, len(markets))] wb = Workbook(os.path.join(os.getcwd(), "Results/notgrapefruit2015 - Practice 2.xlsm")) sales = Range("POJ", "BV6:BV105").value cost = sum(1.2 * d * s for d, s in zip(dists, sales)) # Compute assumed transportation costs (see minimize_travel_results.txt) total_D_vec = [77741, 64774, 52687, 45887] print [1.2 * (total_d / 100) * 100 * (50 + 40 + 35 + 30) * 48 for total_d in total_D_vec]
from xlwings import Workbook, Range import numpy as np import pandas as pd ROOT_DIR = os.path.dirname(os.getcwd()) wb = Workbook(os.path.join(ROOT_DIR, 'reference/StaticData-mod.xlsx')) # Storage and Markets storages = Range('S->M', 'C1:BU1').value markets = ['{0} ({1})'.format(row[1], row[0]) for row in Range('S->M', 'A2:B101', atleast_2d=True).value] regions = Range('S->M', 'A2:A101').value D = np.array(Range('S->M', 'C2:BU101', atleast_2d=True).value) dist_df = pd.DataFrame({'region': regions, 'market': markets, 'S15_dist': D[:, storages.index('S15')], 'S61_dist': D[:, storages.index('S61')]}) av_dists = dist_df.groupby('region').mean() av_dists['d'] = np.array(av_dists.min(1)) av_dists.to_csv('region_storage_dists.csv')
import numpy as np import pandas as pd import os import itertools ROOT_DIR = os.path.dirname(os.getcwd()) wb = Workbook(os.path.join(ROOT_DIR, 'reference/StaticData-mod.xlsx')) # Storage and Plants storages = Range('P->S', 'A2:A72').value plants = Range('P->S', 'B1:K1').value D = np.array(Range('P->S', 'B2:K72', atleast_2d=True).value) # Only include the 4 storages from previous analysis. storages_fixed = (u'S35', u'S51', u'S59', u'S73') D_fixed = D[[storages.index(s) for s in storages_fixed], :] # Seek to minimize the sum of the distance from each storage # to its closest open processing plant. # P = 1 total_distances = {} for p in xrange(0, D_fixed.shape[1]): total_distances[p] = sum(D_fixed[:, p]) best = min(total_distances, key=total_distances.get) # Return set of best plants for a given plant size P, as well as # total distance for that plant size P. def get_best_plants(P): combs = list(itertools.combinations(xrange(0, len(plants)), P))
from xlwings import Workbook, Range import numpy as np import pandas as pd import os ROOT_DIR = os.path.dirname(os.getcwd()) wb = Workbook(os.path.join(ROOT_DIR, 'reference/StaticData-mod.xlsx')) # Storage and Markets storages = Range('S->M', 'C1:BU1').value markets = Range('S->M', 'B2:B101').value regions = Range('S->M', 'A2:A101').value D = np.array(Range('S->M', 'C2:BU101', atleast_2d=True).value) opt_storages = ['S35', 'S51', 'S59', 'S73'] i = storages.index('S35') j = storages.index('S51') k = storages.index('S59') l = storages.index('S73') min_dists = [min(D[r, i], D[r, j], D[r, k], D[r, l]) for r in xrange(0, len(D))] closest_storage_ind = [[D[r, i], D[r, j], D[r, k], D[r, l]].index( min(D[r, i], D[r, j], D[r, k], D[r, l])) for r in xrange(0, len(D))] closest_df = pd.DataFrame( {'market': markets, 'region': regions, 'closest_storage': [opt_storages[s] for s in closest_storage_ind]})
print('''S = 4: {0} gives the ''' '''min total dist to markets = {1}''').format(best4, total_distances_4[best4]) opt_sm = { 1: (best, total_distances[best]), 2: (best2, total_distances_2[best2]), 3: (best3, total_distances_3[best3]), 4: (best4, total_distances_4[best4]) } import cPickle as pickle with open('opt_sm.pkl', 'wb') as f: pickle.dump(opt_sm, f) # current storage system4 i = storages.index('S15') j = storages.index('S61') total_distance = sum([min(D[r, i], D[r, j]) for r in xrange(0, len(D))]) # Check 4th week of May transportation costs dists = [ min(D[m, storages.index('S15')], D[m, storages.index('S61')]) for m in xrange(0, len(markets)) ] wb = Workbook( os.path.join(os.getcwd(), 'Results/notgrapefruit2015 - Practice 2.xlsm')) sales = Range('POJ', 'BV6:BV105').value cost = sum(1.2 * d * s for d, s in zip(dists, sales))
from xlwings import Workbook, Range import numpy as np import pandas as pd import os ROOT_DIR = os.path.dirname(os.getcwd()) wb = Workbook(os.path.join(ROOT_DIR, 'reference/StaticData-mod.xlsx')) # Storage and Markets storages = Range('S->M', 'C1:BU1').value markets = Range('S->M', 'B2:B101').value regions = Range('S->M', 'A2:A101').value D = np.array(Range('S->M', 'C2:BU101', atleast_2d=True).value) opt_storages = ['S35', 'S51', 'S59', 'S73'] i = storages.index('S35') j = storages.index('S51') k = storages.index('S59') l = storages.index('S73') min_dists = [ min(D[r, i], D[r, j], D[r, k], D[r, l]) for r in xrange(0, len(D)) ] closest_storage_ind = [[D[r, i], D[r, j], D[r, k], D[r, l]].index(min(D[r, i], D[r, j], D[r, k], D[r, l])) for r in xrange(0, len(D))] closest_df = pd.DataFrame({ 'market': markets, 'region': regions,