Combine multiple excel files Using python pandas module

Combine multiple  Excel files using python pandas module

from operator import index

import pandas as pd

import datetime as dt

files = ['1_data.xlsx','2_data.xlsx','3_data.xlsx','4_data.xlsx','5_data.xlsx','6_data.xlsx','7_data.xlsx','8_data.xlsx']

combined = pd.DataFrame()

for file in files:

    df=pd.read_excel(file)

    combined=combined.append(df,ignore_index=True)

combined.to_excel('combine.xlsx',index=False,sheet_name='data')

Excel and Frontline Solver - INDEX function

Heuristic Methods
INDEX function

problem #6, p. 333 [1] [2]

a.

Data			Amsterdam	Athens	Berlin	Copenhagen	Dublin	Lisbon	London	Luxembourg	Madrid	Paris	Rome	Brussels
	From/To		1	2	3	4	5	6	7	8	9	10	11	12
	Amsterdam	1	0	2166	577	622	712	1889	339	319	1462	430	1297	175
	Athens	2	2166	0	1806	2132	2817	2899	2377	1905	2313	2100	1053	2092
	Berlin	3	577	1806	0	348	1273	2345	912	598	1836	878	1184	653
	Copenhagen	4	622	2132	348	0	1203	2505	942	797	2046	1027	1527	768
	Dublin	5	712	2817	1273	1203	0	1656	440	914	1452	743	1849	732
	Lisbon	6	1889	2899	2345	2505	1656	0	1616	1747	600	1482	1907	1738
	London	7	339	2377	912	942	440	1616	0	475	1259	331	1419	300
	Luxembourg	8	319	1905	598	797	914	1747	475	0	1254	293	987	190
	Madrid	9	1462	2313	1836	2046	1452	600	1259	1254	0	1033	1308	1293
	Paris	10	430	2100	878	1027	743	1482	331	293	1033	0	1108	262
	Rome	11	1297	1053	1184	1527	1849	1907	1419	987	1308	1108	0	1173
	Brussels	12	175	2092	653	768	732	1738	300	190	1293	262	1173	0

Order	1	2	3	4	5	6	7	8	9	10	11	12	1
City	12	1	4	3	2	11	9	6	5	7	10	8	12
Distaance	175	622	348	1806	1053	1308	600	1656	440	331	293	190

Minimum-distance = 8822

        The sequence is Brussels, Amsterdan, Copenhagen, Berlin, Athens, Rome, Madrid, Lisbon, Dublin, London, Paris, Luxembourg, and then Brussels. The length of the minimum-distance tour is 8822.

b.

Data			Amsterdam	Athens	Berlin	Copenhagen	Dublin	Lisbon	London	Luxembourg	Madrid	Paris	Rome	Brussels
	From/To		1	2	3	4	5	6	7	8	9	10	11	12
	Amsterdam	1	0	2166	577	622	712	1889	339	319	1462	430	1297	175
	Athens	2	2166	0	1806	2132	2817	2899	2377	1905	2313	2100	1053	2092
	Berlin	3	577	1806	0	348	1273	2345	912	598	1836	878	1184	653
	Copenhagen	4	622	2132	348	0	1203	2505	942	797	2046	1027	1527	768
	Dublin	5	712	2817	1273	1203	0	1656	440	914	1452	743	1849	732
	Lisbon	6	1889	2899	2345	2505	1656	0	1616	1747	600	1482	1907	1738
	London	7	339	2377	912	942	440	1616	0	475	1259	331	1419	300
	Luxembourg	8	319	1905	598	797	914	1747	475	0	1254	293	987	190
	Madrid	9	1462	2313	1836	2046	1452	600	1259	1254	0	1033	1308	1293
	Paris	10	430	2100	878	1027	743	1482	331	293	1033	0	1108	262
	Rome	11	1297	1053	1184	1527	1849	1907	1419	987	1308	1108	0	1173
	Brussels	12	175	2092	653	768	732	1738	300	190	1293	262	1173	0

Order	1	2	3	4	5	6	7	8	9	10	11	12
City	12	1	4	3	8	10	7	5	6	9	11	2
Distaance	175	622	348	598	293	331	440	1656	600	1308	1053

Minimum-distance = 7424

        The sequence is Brussels, Amsterdan, Copenhagen, Berlin, Luxembourg, Paris, London, Dublin, Lisbon, Madrid, Rome, and Athens.

Additional questions to answer for Part B:

		Amsterdam	Athens	Berlin	Copenhagen	Dublin	Lisbon	London	Luxembourg	Madrid	Paris	Rome	Brussels
From/To		1	2	3	4	5	6	7	8	9	10	11	12
Amsterdam	1	0	2166	577	622	712	1889	339	319	1462	430	1297	175
Athens	2	2166	0	1806	2132	2817	2899	2377	1905	2313	2100	1053	2092
Berlin	3	577	1806	0	348	1273	2345	912	598	1836	878	1184	653
Copenhagen	4	622	2132	348	0	1203	2505	942	797	2046	1027	1527	768
Dublin	5	712	2817	1273	1203	0	1656	440	914	1452	743	1849	732
Lisbon	6	1889	2899	2345	2505	1656	0	1616	1747	600	1482	1907	1738
London	7	339	2377	912	942	440	1616	0	475	1259	331	1419	300
Luxembourg	8	319	1905	598	797	914	1747	475	0	1254	293	987	190
Madrid	9	1462	2313	1836	2046	1452	600	1259	1254	0	1033	1308	1293
Paris	10	430	2100	878	1027	743	1482	331	293	1033	0	1108	262
Rome	11	1297	1053	1184	1527	1849	1907	1419	987	1308	1108	0	1173
Brussels	12	175	2092	653	768	732	1738	300	190	1293	262	1173	0

Order	1	2	3	4	5	6	7	8	9	10	11	12
City	6	9	5	7	10	8	12	1	4	3	11	2
Distaance	600	1452	440	331	293	190	175	622	348	1184	1053

Minimum-distance = 6688

        If the sequence is Athens, Rome, Madrid, Lisbon, Dublin, London, Paris, Luxembourg, Berlin, Copenhagen, Amsterdan, and Brussels, the solution will have same objective value as answer b.

Because the route is same, the distance is same.

        In this sheet, I calculate the length of the minimum-distance tour. If the trip start from Lisbon, the length of the minimum-distance tour is shortest. Therefore, I should tell my decision-maker these solutions are not truely optimal.

        If the trip don't need to go back to Brussels, the  order will change and reduce distance 1398. The solution of answer b is fewer than answer a..

Reference:
[1] Powell, Stephen G. Management Science: The Art Of Modeling With Spreadsheets, 4Th Edition. 1st ed. John Wiley & Sons, 2013. Print.
[2] "Solving Travelling Salesman Problem(TSP) using Excel Solver", YouTube, 2017. [Online]. Available: https://www.youtube.com/watch?v=-E3rSoClgMI. [Accessed: 08- Jun- 2017].

Excel and Frontline Solver - SUMPRODUCT function (Linear Optimization)

Linear Optimization

Linear objective functions and linear constraints

problem #10, p. 252 [1]

a. and b.

--- Excel and Frontline Solver  ---

Data
Componet	Hotel	Restaurant	Market
Decision	10000	32500	30000
wholesale Price	1.25	1.5	1.4	Total sales =	103250

Component	Hotel	Restaurant	Market	Cost Per Pound	Max num
Robusta	20%	35%	10%	$0.60	40000
Javan Arabica	40%	15%	35%	$0.80	25000
Liberica	15%	20%	40%	$0.55	20000
Brazilian Arabica	25%	30%	15%	$0.70	45000
Minimum	10000	25000	30000	No more than	100000

Constraints	Hotel	Restaurant	Market	Each bean total	Each bean expense
Robusta	2000	11375	3000	16375	$9,825.00
Javan Arabica	4000	4875	10500	19375	$15,500.00
Liberica	1500	6500	12000	20000	$11,000.00
Brazilian Arabica	2500	9750	4500	16750	$11,725.00
Each brand total	10000	32500	30000	Total Expense =	$48,050.00
			Bean total=	72500

Profits = $55,200.00

a.

     The company should buy 16375 pounds Robusta, 19375 pounds Javan Arabica, 20000 poundsLiberrica,

and 16750 pounds Brazilian Arbica.

b. 

     According to solver's solution, only Liberica reach max weekly availability, so Liberica is the economic

value componet. However, the number of Liberica already reach maximum number. There is no economic

value of an additional pound's worth of plant capacity.

--------------------------------------------------------------------------------------------------------------------------

c.

--- Excel and Frontline Solver  ---

Data
Componet	Hotel	Restaurant	Market
Decision	10000	32505	30000
wholesale Price	1.25	1.5	1.4	Total sales =	103257.5

Component	Hotel	Restaurant	Market	Cost Per Pound	Max num
Robusta	20%	35%	10%	$0.60	40000
Javan Arabica	40%	15%	35%	$0.80	25000
Liberica	15%	20%	40%	$0.55	20001
Brazilian Arabica	25%	30%	15%	$0.70	45000
Minimum	10000	25000	30000	No more than	100000

Constraints	Hotel	Restaurant	Market	Each bean total	Each bean expense
Robusta	2000	11376.75	3000	16376.75	$9,826.05
Javan Arabica	4000	4875.75	10500	19375.75	$15,500.60
Liberica	1500	6501	12000	20001	$11,000.55
Brazilian Arabica	2500	9751.5	4500	16751.5	$11,726.05
Each brand total	10000	32505	30000	Total Expense =	$48,053.25

                   Bean total = 72505

Profits = $55,204.25

	Previous	Add one pound
Liberica	20000	20001
Profits	55200	55204.25		value =	4.25
				4.25 + 0.55 =	4.8

The company can earn more $4.25 than before, but Solver already minus 0.55 as additional pound ofs

Liberica's expense. Thus, if the company want to earn more money, the company should pay less than $4.8.

d.

Data
Component	Hotel	Restaurant	Market
Decision	20000	25000	30000
wholesale Price	1.25	1.5	1.4	Total sales =	104500
Component	Hotel	Restaurant	Market	Cost Per Pound	Max num
Robusta	20%	35%	10%	$0.60	40000
Javan Arabica	40%	15%	35%	$0.80	25000
Liberica	15%	20%	40%	$0.55	20000
Brazilian Arabica	25%	30%	15%	$0.70	45000
Minimum	25000	25000	30000	No more than	100000
Constraints	Hotel	Restaurant	Market	Each bean total	Each bean expense
Robusta	4000	8750	3000	15750	$9,450.00
Javan Arabica	8000	3750	10500	22250	$17,800.00
Liberica	3000	5000	12000	20000	$11,000.00
Brazilian Arabica	5000	7500	4500	17000	$11,900.00
Each brand total	20000	25000	30000	Total  Expense	$50,150.00
			Bean total=	75000
				Profits =	$54,350.00
Hotel Minimum	0	5000	10000	15000	20000	25000
Profits	56050	55625	55200	54775	54350	54350

e.

Data
Componet	Hotel	Restaurant	Market
Decision	10000	32500	30000
wholesale Price	1.25	1.5	1.4	Total sales =	103250
Component	Hotel	Restaurant	Market	Cost Per Pound	Max num
Robusta	20%	35%	10%	$1.20	40000
Javan Arabica	40%	15%	35%	$0.80	25000
Liberica	15%	20%	40%	$0.55	20000
Brazilian Arabica	25%	30%	15%	$0.70	45000
Minimum	10000	25000	30000	No more than	100000
Constraints	Hotel	Restaurant	Market	Each bean total	Each bean expense
Robusta	2000	11375	3000	16375	$19,650.00
Javan Arabica	4000	4875	10500	19375	$15,500.00
Liberica	1500	6500	12000	20000	$11,000.00
Brazilian Arabica	2500	9750	4500	16750	$11,725.00
Each brand total	10000	32500	30000	Total Expense	$57,875.00
			Bean total=	72500
				Profits =	$45,375.00
Robusta
Cost Per Pound	0.20	0.40	0.60	0.80	1.00	1.20
Profits	61,750.00	58,475.00	55,200.00	51,925.00	48,650.00	45,375.00

Reference:
[1] Powell, Stephen G. Management Science: The Art Of Modeling With Spreadsheets, 4Th Edition. 1st ed. John Wiley & Sons, 2013. Print.