Exemple #1
0
    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)
Exemple #2
0
    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
Exemple #3
0
    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
Exemple #4
0
    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
Exemple #5
0
    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))
Exemple #14
0
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,