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,