{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Combine Multiple Excel Worksheets Into a Single Pandas Dataframe\n",
"\n",
"Article posted [here](https://pbpython.com/pandas-excel-tabs.html)"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"workbook_url = 'https://github.com/chris1610/pbpython/raw/master/data/2018_Sales_Total_Tabs.xlsx'"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [],
"source": [
"single_df = pd.read_excel(workbook_url, sheet_name='Sheet1')"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" account number | \n",
" name | \n",
" sku | \n",
" quantity | \n",
" unit price | \n",
" ext price | \n",
" date | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 412290 | \n",
" Jerde-Hilpert | \n",
" S2-77896 | \n",
" 43 | \n",
" 76.66 | \n",
" 3296.38 | \n",
" 2018-03-04 23:10:28 | \n",
"
\n",
" \n",
" 1 | \n",
" 383080 | \n",
" Will LLC | \n",
" S1-93683 | \n",
" 28 | \n",
" 90.86 | \n",
" 2544.08 | \n",
" 2018-03-05 05:11:49 | \n",
"
\n",
" \n",
" 2 | \n",
" 729833 | \n",
" Koepp Ltd | \n",
" S1-30248 | \n",
" 13 | \n",
" 44.84 | \n",
" 582.92 | \n",
" 2018-03-05 17:33:52 | \n",
"
\n",
" \n",
" 3 | \n",
" 424914 | \n",
" White-Trantow | \n",
" S2-82423 | \n",
" 38 | \n",
" 50.93 | \n",
" 1935.34 | \n",
" 2018-03-05 21:40:10 | \n",
"
\n",
" \n",
" 4 | \n",
" 672390 | \n",
" Kuhn-Gusikowski | \n",
" S1-50961 | \n",
" 34 | \n",
" 48.20 | \n",
" 1638.80 | \n",
" 2018-03-06 11:59:00 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" account number name sku quantity unit price ext price \\\n",
"0 412290 Jerde-Hilpert S2-77896 43 76.66 3296.38 \n",
"1 383080 Will LLC S1-93683 28 90.86 2544.08 \n",
"2 729833 Koepp Ltd S1-30248 13 44.84 582.92 \n",
"3 424914 White-Trantow S2-82423 38 50.93 1935.34 \n",
"4 672390 Kuhn-Gusikowski S1-50961 34 48.20 1638.80 \n",
"\n",
" date \n",
"0 2018-03-04 23:10:28 \n",
"1 2018-03-05 05:11:49 \n",
"2 2018-03-05 17:33:52 \n",
"3 2018-03-05 21:40:10 \n",
"4 2018-03-06 11:59:00 "
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"single_df.head()"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [],
"source": [
"all_dfs = pd.read_excel(workbook_url, sheet_name=None)"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"collections.OrderedDict"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"type(all_dfs)"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"collections.OrderedDict"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"type(all_dfs)"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"odict_keys(['Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Sheet5', 'Sheet6'])"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"all_dfs.keys()"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" account number | \n",
" name | \n",
" sku | \n",
" quantity | \n",
" unit price | \n",
" ext price | \n",
" date | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 412290 | \n",
" Jerde-Hilpert | \n",
" S2-77896 | \n",
" 43 | \n",
" 76.66 | \n",
" 3296.38 | \n",
" 2018-03-04 23:10:28 | \n",
"
\n",
" \n",
" 1 | \n",
" 383080 | \n",
" Will LLC | \n",
" S1-93683 | \n",
" 28 | \n",
" 90.86 | \n",
" 2544.08 | \n",
" 2018-03-05 05:11:49 | \n",
"
\n",
" \n",
" 2 | \n",
" 729833 | \n",
" Koepp Ltd | \n",
" S1-30248 | \n",
" 13 | \n",
" 44.84 | \n",
" 582.92 | \n",
" 2018-03-05 17:33:52 | \n",
"
\n",
" \n",
" 3 | \n",
" 424914 | \n",
" White-Trantow | \n",
" S2-82423 | \n",
" 38 | \n",
" 50.93 | \n",
" 1935.34 | \n",
" 2018-03-05 21:40:10 | \n",
"
\n",
" \n",
" 4 | \n",
" 672390 | \n",
" Kuhn-Gusikowski | \n",
" S1-50961 | \n",
" 34 | \n",
" 48.20 | \n",
" 1638.80 | \n",
" 2018-03-06 11:59:00 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" account number name sku quantity unit price ext price \\\n",
"0 412290 Jerde-Hilpert S2-77896 43 76.66 3296.38 \n",
"1 383080 Will LLC S1-93683 28 90.86 2544.08 \n",
"2 729833 Koepp Ltd S1-30248 13 44.84 582.92 \n",
"3 424914 White-Trantow S2-82423 38 50.93 1935.34 \n",
"4 672390 Kuhn-Gusikowski S1-50961 34 48.20 1638.80 \n",
"\n",
" date \n",
"0 2018-03-04 23:10:28 \n",
"1 2018-03-05 05:11:49 \n",
"2 2018-03-05 17:33:52 \n",
"3 2018-03-05 21:40:10 \n",
"4 2018-03-06 11:59:00 "
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"all_dfs['Sheet1'].head()"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" account number | \n",
" name | \n",
" sku | \n",
" quantity | \n",
" unit price | \n",
" ext price | \n",
" date | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 740150 | \n",
" Barton LLC | \n",
" B1-20000 | \n",
" 39 | \n",
" 86.69 | \n",
" 3380.91 | \n",
" 2018-01-01 07:21:51 | \n",
"
\n",
" \n",
" 1 | \n",
" 714466 | \n",
" Trantow-Barrows | \n",
" S2-77896 | \n",
" -1 | \n",
" 63.16 | \n",
" -63.16 | \n",
" 2018-01-01 10:00:47 | \n",
"
\n",
" \n",
" 2 | \n",
" 218895 | \n",
" Kulas Inc | \n",
" B1-69924 | \n",
" 23 | \n",
" 90.70 | \n",
" 2086.10 | \n",
" 2018-01-01 13:24:58 | \n",
"
\n",
" \n",
" 3 | \n",
" 307599 | \n",
" Kassulke, Ondricka and Metz | \n",
" S1-65481 | \n",
" 41 | \n",
" 21.05 | \n",
" 863.05 | \n",
" 2018-01-01 15:05:22 | \n",
"
\n",
" \n",
" 4 | \n",
" 412290 | \n",
" Jerde-Hilpert | \n",
" S2-34077 | \n",
" 6 | \n",
" 83.21 | \n",
" 499.26 | \n",
" 2018-01-01 23:26:55 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" account number name sku quantity \\\n",
"0 740150 Barton LLC B1-20000 39 \n",
"1 714466 Trantow-Barrows S2-77896 -1 \n",
"2 218895 Kulas Inc B1-69924 23 \n",
"3 307599 Kassulke, Ondricka and Metz S1-65481 41 \n",
"4 412290 Jerde-Hilpert S2-34077 6 \n",
"\n",
" unit price ext price date \n",
"0 86.69 3380.91 2018-01-01 07:21:51 \n",
"1 63.16 -63.16 2018-01-01 10:00:47 \n",
"2 90.70 2086.10 2018-01-01 13:24:58 \n",
"3 21.05 863.05 2018-01-01 15:05:22 \n",
"4 83.21 499.26 2018-01-01 23:26:55 "
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"all_dfs['Sheet2'].head()"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Sheet1 - (39, 7)\n",
"Sheet2 - (35, 7)\n",
"Sheet3 - (47, 7)\n",
"Sheet4 - (47, 7)\n",
"Sheet5 - (81, 7)\n",
"Sheet6 - (50, 7)\n"
]
}
],
"source": [
"for sheet in all_dfs:\n",
" print(f\"{sheet} - {all_dfs[sheet].shape}\")"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [],
"source": [
"df = pd.concat(all_dfs)"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"(299, 7)"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.shape"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" | \n",
" account number | \n",
" name | \n",
" sku | \n",
" quantity | \n",
" unit price | \n",
" ext price | \n",
" date | \n",
"
\n",
" \n",
" \n",
" \n",
" Sheet1 | \n",
" 0 | \n",
" 412290 | \n",
" Jerde-Hilpert | \n",
" S2-77896 | \n",
" 43 | \n",
" 76.66 | \n",
" 3296.38 | \n",
" 2018-03-04 23:10:28 | \n",
"
\n",
" \n",
" 1 | \n",
" 383080 | \n",
" Will LLC | \n",
" S1-93683 | \n",
" 28 | \n",
" 90.86 | \n",
" 2544.08 | \n",
" 2018-03-05 05:11:49 | \n",
"
\n",
" \n",
" 2 | \n",
" 729833 | \n",
" Koepp Ltd | \n",
" S1-30248 | \n",
" 13 | \n",
" 44.84 | \n",
" 582.92 | \n",
" 2018-03-05 17:33:52 | \n",
"
\n",
" \n",
" 3 | \n",
" 424914 | \n",
" White-Trantow | \n",
" S2-82423 | \n",
" 38 | \n",
" 50.93 | \n",
" 1935.34 | \n",
" 2018-03-05 21:40:10 | \n",
"
\n",
" \n",
" 4 | \n",
" 672390 | \n",
" Kuhn-Gusikowski | \n",
" S1-50961 | \n",
" 34 | \n",
" 48.20 | \n",
" 1638.80 | \n",
" 2018-03-06 11:59:00 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" account number name sku quantity unit price \\\n",
"Sheet1 0 412290 Jerde-Hilpert S2-77896 43 76.66 \n",
" 1 383080 Will LLC S1-93683 28 90.86 \n",
" 2 729833 Koepp Ltd S1-30248 13 44.84 \n",
" 3 424914 White-Trantow S2-82423 38 50.93 \n",
" 4 672390 Kuhn-Gusikowski S1-50961 34 48.20 \n",
"\n",
" ext price date \n",
"Sheet1 0 3296.38 2018-03-04 23:10:28 \n",
" 1 2544.08 2018-03-05 05:11:49 \n",
" 2 582.92 2018-03-05 17:33:52 \n",
" 3 1935.34 2018-03-05 21:40:10 \n",
" 4 1638.80 2018-03-06 11:59:00 "
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.head()"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" | \n",
" account number | \n",
" name | \n",
" sku | \n",
" quantity | \n",
" unit price | \n",
" ext price | \n",
" date | \n",
"
\n",
" \n",
" \n",
" \n",
" Sheet6 | \n",
" 45 | \n",
" 239344 | \n",
" Stokes LLC | \n",
" S1-82801 | \n",
" 41 | \n",
" 78.90 | \n",
" 3234.90 | \n",
" 2018-03-04 01:06:20 | \n",
"
\n",
" \n",
" 46 | \n",
" 218895 | \n",
" Kulas Inc | \n",
" S2-78676 | \n",
" 38 | \n",
" 89.02 | \n",
" 3382.76 | \n",
" 2018-03-04 01:17:11 | \n",
"
\n",
" \n",
" 47 | \n",
" 642753 | \n",
" Pollich LLC | \n",
" S2-10342 | \n",
" 40 | \n",
" 56.85 | \n",
" 2274.00 | \n",
" 2018-03-04 01:49:22 | \n",
"
\n",
" \n",
" 48 | \n",
" 737550 | \n",
" Fritsch, Russel and Anderson | \n",
" S2-83881 | \n",
" 12 | \n",
" 63.60 | \n",
" 763.20 | \n",
" 2018-03-04 15:26:20 | \n",
"
\n",
" \n",
" 49 | \n",
" 146832 | \n",
" Kiehn-Spinka | \n",
" B1-53636 | \n",
" 5 | \n",
" 72.16 | \n",
" 360.80 | \n",
" 2018-03-04 21:18:04 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" account number name sku quantity \\\n",
"Sheet6 45 239344 Stokes LLC S1-82801 41 \n",
" 46 218895 Kulas Inc S2-78676 38 \n",
" 47 642753 Pollich LLC S2-10342 40 \n",
" 48 737550 Fritsch, Russel and Anderson S2-83881 12 \n",
" 49 146832 Kiehn-Spinka B1-53636 5 \n",
"\n",
" unit price ext price date \n",
"Sheet6 45 78.90 3234.90 2018-03-04 01:06:20 \n",
" 46 89.02 3382.76 2018-03-04 01:17:11 \n",
" 47 56.85 2274.00 2018-03-04 01:49:22 \n",
" 48 63.60 763.20 2018-03-04 15:26:20 \n",
" 49 72.16 360.80 2018-03-04 21:18:04 "
]
},
"execution_count": 15,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.tail()"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" account number | \n",
" name | \n",
" sku | \n",
" quantity | \n",
" unit price | \n",
" ext price | \n",
" date | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 412290 | \n",
" Jerde-Hilpert | \n",
" S2-77896 | \n",
" 43 | \n",
" 76.66 | \n",
" 3296.38 | \n",
" 2018-03-04 23:10:28 | \n",
"
\n",
" \n",
" 1 | \n",
" 383080 | \n",
" Will LLC | \n",
" S1-93683 | \n",
" 28 | \n",
" 90.86 | \n",
" 2544.08 | \n",
" 2018-03-05 05:11:49 | \n",
"
\n",
" \n",
" 2 | \n",
" 729833 | \n",
" Koepp Ltd | \n",
" S1-30248 | \n",
" 13 | \n",
" 44.84 | \n",
" 582.92 | \n",
" 2018-03-05 17:33:52 | \n",
"
\n",
" \n",
" 3 | \n",
" 424914 | \n",
" White-Trantow | \n",
" S2-82423 | \n",
" 38 | \n",
" 50.93 | \n",
" 1935.34 | \n",
" 2018-03-05 21:40:10 | \n",
"
\n",
" \n",
" 4 | \n",
" 672390 | \n",
" Kuhn-Gusikowski | \n",
" S1-50961 | \n",
" 34 | \n",
" 48.20 | \n",
" 1638.80 | \n",
" 2018-03-06 11:59:00 | \n",
"
\n",
" \n",
" 5 | \n",
" 239344 | \n",
" Stokes LLC | \n",
" S2-10342 | \n",
" 34 | \n",
" 36.93 | \n",
" 1255.62 | \n",
" 2018-03-06 23:49:16 | \n",
"
\n",
" \n",
" 6 | \n",
" 218895 | \n",
" Kulas Inc | \n",
" S2-34077 | \n",
" 30 | \n",
" 99.73 | \n",
" 2991.90 | \n",
" 2018-03-07 05:15:29 | \n",
"
\n",
" \n",
" 7 | \n",
" 672390 | \n",
" Kuhn-Gusikowski | \n",
" B1-05914 | \n",
" 25 | \n",
" 89.86 | \n",
" 2246.50 | \n",
" 2018-03-07 06:25:52 | \n",
"
\n",
" \n",
" 8 | \n",
" 740150 | \n",
" Barton LLC | \n",
" S1-82801 | \n",
" 29 | \n",
" 60.81 | \n",
" 1763.49 | \n",
" 2018-03-07 10:24:54 | \n",
"
\n",
" \n",
" 9 | \n",
" 218895 | \n",
" Kulas Inc | \n",
" B1-20000 | \n",
" 23 | \n",
" 99.57 | \n",
" 2290.11 | \n",
" 2018-03-07 13:34:00 | \n",
"
\n",
" \n",
" 10 | \n",
" 257198 | \n",
" Cronin, Oberbrunner and Spencer | \n",
" S1-30248 | \n",
" 14 | \n",
" 91.37 | \n",
" 1279.18 | \n",
" 2018-03-07 16:21:58 | \n",
"
\n",
" \n",
" 11 | \n",
" 672390 | \n",
" Kuhn-Gusikowski | \n",
" S2-83881 | \n",
" 26 | \n",
" 88.38 | \n",
" 2297.88 | \n",
" 2018-03-07 20:18:18 | \n",
"
\n",
" \n",
" 12 | \n",
" 424914 | \n",
" White-Trantow | \n",
" B1-53636 | \n",
" 44 | \n",
" 59.93 | \n",
" 2636.92 | \n",
" 2018-03-07 21:16:24 | \n",
"
\n",
" \n",
" 13 | \n",
" 307599 | \n",
" Kassulke, Ondricka and Metz | \n",
" S2-10342 | \n",
" 4 | \n",
" 12.99 | \n",
" 51.96 | \n",
" 2018-03-07 23:59:26 | \n",
"
\n",
" \n",
" 14 | \n",
" 383080 | \n",
" Will LLC | \n",
" B1-20000 | \n",
" 45 | \n",
" 29.90 | \n",
" 1345.50 | \n",
" 2018-03-08 01:50:36 | \n",
"
\n",
" \n",
" 15 | \n",
" 527099 | \n",
" Sanford and Sons | \n",
" S2-82423 | \n",
" 44 | \n",
" 23.30 | \n",
" 1025.20 | \n",
" 2018-03-09 00:18:55 | \n",
"
\n",
" \n",
" 16 | \n",
" 146832 | \n",
" Kiehn-Spinka | \n",
" S2-77896 | \n",
" 27 | \n",
" 70.76 | \n",
" 1910.52 | \n",
" 2018-03-09 11:05:47 | \n",
"
\n",
" \n",
" 17 | \n",
" 642753 | \n",
" Pollich LLC | \n",
" B1-05914 | \n",
" 15 | \n",
" 65.50 | \n",
" 982.50 | \n",
" 2018-03-09 11:22:09 | \n",
"
\n",
" \n",
" 18 | \n",
" 786968 | \n",
" Frami, Hills and Schmidt | \n",
" S1-65481 | \n",
" 14 | \n",
" 44.67 | \n",
" 625.38 | \n",
" 2018-03-09 19:13:11 | \n",
"
\n",
" \n",
" 19 | \n",
" 527099 | \n",
" Sanford and Sons | \n",
" S1-82801 | \n",
" 35 | \n",
" 95.17 | \n",
" 3330.95 | \n",
" 2018-03-09 19:13:20 | \n",
"
\n",
" \n",
" 20 | \n",
" 257198 | \n",
" Cronin, Oberbrunner and Spencer | \n",
" S2-23246 | \n",
" 43 | \n",
" 87.40 | \n",
" 3758.20 | \n",
" 2018-03-10 05:39:36 | \n",
"
\n",
" \n",
" 21 | \n",
" 527099 | \n",
" Sanford and Sons | \n",
" B1-65551 | \n",
" 6 | \n",
" 58.04 | \n",
" 348.24 | \n",
" 2018-03-10 05:52:17 | \n",
"
\n",
" \n",
" 22 | \n",
" 218895 | \n",
" Kulas Inc | \n",
" S1-47412 | \n",
" 21 | \n",
" 94.45 | \n",
" 1983.45 | \n",
" 2018-03-10 11:02:14 | \n",
"
\n",
" \n",
" 23 | \n",
" 729833 | \n",
" Koepp Ltd | \n",
" S2-10342 | \n",
" 12 | \n",
" 93.64 | \n",
" 1123.68 | \n",
" 2018-03-10 13:08:45 | \n",
"
\n",
" \n",
" 24 | \n",
" 412290 | \n",
" Jerde-Hilpert | \n",
" S1-82801 | \n",
" 26 | \n",
" 74.79 | \n",
" 1944.54 | \n",
" 2018-03-11 02:49:11 | \n",
"
\n",
" \n",
" 25 | \n",
" 642753 | \n",
" Pollich LLC | \n",
" S2-77896 | \n",
" 13 | \n",
" 48.14 | \n",
" 625.82 | \n",
" 2018-03-11 06:19:26 | \n",
"
\n",
" \n",
" 26 | \n",
" 218895 | \n",
" Kulas Inc | \n",
" B1-33364 | \n",
" 17 | \n",
" 93.01 | \n",
" 1581.17 | \n",
" 2018-03-11 08:20:57 | \n",
"
\n",
" \n",
" 27 | \n",
" 307599 | \n",
" Kassulke, Ondricka and Metz | \n",
" S1-50961 | \n",
" 28 | \n",
" 34.87 | \n",
" 976.36 | \n",
" 2018-03-11 10:49:24 | \n",
"
\n",
" \n",
" 28 | \n",
" 146832 | \n",
" Kiehn-Spinka | \n",
" S1-93683 | \n",
" 17 | \n",
" 10.63 | \n",
" 180.71 | \n",
" 2018-03-11 12:43:03 | \n",
"
\n",
" \n",
" 29 | \n",
" 383080 | \n",
" Will LLC | \n",
" S1-82801 | \n",
" 3 | \n",
" 77.06 | \n",
" 231.18 | \n",
" 2018-03-11 16:38:10 | \n",
"
\n",
" \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
"
\n",
" \n",
" 269 | \n",
" 141962 | \n",
" Herman LLC | \n",
" B1-69924 | \n",
" 21 | \n",
" 95.00 | \n",
" 1995.00 | \n",
" 2018-02-26 19:08:02 | \n",
"
\n",
" \n",
" 270 | \n",
" 383080 | \n",
" Will LLC | \n",
" S2-23246 | \n",
" 47 | \n",
" 10.93 | \n",
" 513.71 | \n",
" 2018-02-26 20:41:02 | \n",
"
\n",
" \n",
" 271 | \n",
" 383080 | \n",
" Will LLC | \n",
" B1-20000 | \n",
" 4 | \n",
" 35.04 | \n",
" 140.16 | \n",
" 2018-02-26 23:56:15 | \n",
"
\n",
" \n",
" 272 | \n",
" 672390 | \n",
" Kuhn-Gusikowski | \n",
" S1-93683 | \n",
" 10 | \n",
" 34.96 | \n",
" 349.60 | \n",
" 2018-02-27 02:58:58 | \n",
"
\n",
" \n",
" 273 | \n",
" 424914 | \n",
" White-Trantow | \n",
" S1-93683 | \n",
" -1 | \n",
" 25.01 | \n",
" -25.01 | \n",
" 2018-02-27 10:32:25 | \n",
"
\n",
" \n",
" 274 | \n",
" 383080 | \n",
" Will LLC | \n",
" S1-93683 | \n",
" 41 | \n",
" 97.29 | \n",
" 3988.89 | \n",
" 2018-02-27 14:54:11 | \n",
"
\n",
" \n",
" 275 | \n",
" 714466 | \n",
" Trantow-Barrows | \n",
" S2-11481 | \n",
" 46 | \n",
" 39.61 | \n",
" 1822.06 | \n",
" 2018-02-27 16:12:51 | \n",
"
\n",
" \n",
" 276 | \n",
" 740150 | \n",
" Barton LLC | \n",
" S1-30248 | \n",
" 21 | \n",
" 14.05 | \n",
" 295.05 | \n",
" 2018-02-28 02:29:03 | \n",
"
\n",
" \n",
" 277 | \n",
" 218895 | \n",
" Kulas Inc | \n",
" B1-69924 | \n",
" 44 | \n",
" 72.43 | \n",
" 3186.92 | \n",
" 2018-02-28 08:42:56 | \n",
"
\n",
" \n",
" 278 | \n",
" 383080 | \n",
" Will LLC | \n",
" B1-53102 | \n",
" 6 | \n",
" 32.80 | \n",
" 196.80 | \n",
" 2018-02-28 17:35:23 | \n",
"
\n",
" \n",
" 279 | \n",
" 688981 | \n",
" Keeling LLC | \n",
" S2-23246 | \n",
" 18 | \n",
" 64.71 | \n",
" 1164.78 | \n",
" 2018-02-28 23:21:04 | \n",
"
\n",
" \n",
" 280 | \n",
" 642753 | \n",
" Pollich LLC | \n",
" B1-04202 | \n",
" 8 | \n",
" 95.86 | \n",
" 766.88 | \n",
" 2018-02-28 23:47:32 | \n",
"
\n",
" \n",
" 281 | \n",
" 163416 | \n",
" Purdy-Kunde | \n",
" S1-30248 | \n",
" 19 | \n",
" 65.03 | \n",
" 1235.57 | \n",
" 2018-03-01 16:07:40 | \n",
"
\n",
" \n",
" 282 | \n",
" 527099 | \n",
" Sanford and Sons | \n",
" S2-82423 | \n",
" 3 | \n",
" 76.21 | \n",
" 228.63 | \n",
" 2018-03-01 17:18:01 | \n",
"
\n",
" \n",
" 283 | \n",
" 527099 | \n",
" Sanford and Sons | \n",
" B1-50809 | \n",
" 8 | \n",
" 70.78 | \n",
" 566.24 | \n",
" 2018-03-01 18:53:09 | \n",
"
\n",
" \n",
" 284 | \n",
" 737550 | \n",
" Fritsch, Russel and Anderson | \n",
" B1-50809 | \n",
" 20 | \n",
" 50.11 | \n",
" 1002.20 | \n",
" 2018-03-01 23:47:17 | \n",
"
\n",
" \n",
" 285 | \n",
" 688981 | \n",
" Keeling LLC | \n",
" B1-86481 | \n",
" -1 | \n",
" 97.16 | \n",
" -97.16 | \n",
" 2018-03-02 01:46:44 | \n",
"
\n",
" \n",
" 286 | \n",
" 729833 | \n",
" Koepp Ltd | \n",
" S1-82801 | \n",
" 26 | \n",
" 50.29 | \n",
" 1307.54 | \n",
" 2018-03-02 02:59:26 | \n",
"
\n",
" \n",
" 287 | \n",
" 307599 | \n",
" Kassulke, Ondricka and Metz | \n",
" S1-93683 | \n",
" -1 | \n",
" 86.38 | \n",
" -86.38 | \n",
" 2018-03-02 03:29:04 | \n",
"
\n",
" \n",
" 288 | \n",
" 412290 | \n",
" Jerde-Hilpert | \n",
" S2-23246 | \n",
" 27 | \n",
" 58.87 | \n",
" 1589.49 | \n",
" 2018-03-02 12:10:30 | \n",
"
\n",
" \n",
" 289 | \n",
" 307599 | \n",
" Kassulke, Ondricka and Metz | \n",
" S2-34077 | \n",
" 48 | \n",
" 53.36 | \n",
" 2561.28 | \n",
" 2018-03-02 14:12:28 | \n",
"
\n",
" \n",
" 290 | \n",
" 141962 | \n",
" Herman LLC | \n",
" S2-10342 | \n",
" 49 | \n",
" 31.39 | \n",
" 1538.11 | \n",
" 2018-03-02 23:08:02 | \n",
"
\n",
" \n",
" 291 | \n",
" 257198 | \n",
" Cronin, Oberbrunner and Spencer | \n",
" S2-16558 | \n",
" 7 | \n",
" 26.21 | \n",
" 183.47 | \n",
" 2018-03-03 00:29:24 | \n",
"
\n",
" \n",
" 292 | \n",
" 257198 | \n",
" Cronin, Oberbrunner and Spencer | \n",
" S2-23246 | \n",
" 40 | \n",
" 49.19 | \n",
" 1967.60 | \n",
" 2018-03-03 10:17:24 | \n",
"
\n",
" \n",
" 293 | \n",
" 688981 | \n",
" Keeling LLC | \n",
" B1-38851 | \n",
" 25 | \n",
" 74.68 | \n",
" 1867.00 | \n",
" 2018-03-03 18:18:54 | \n",
"
\n",
" \n",
" 294 | \n",
" 239344 | \n",
" Stokes LLC | \n",
" S1-82801 | \n",
" 41 | \n",
" 78.90 | \n",
" 3234.90 | \n",
" 2018-03-04 01:06:20 | \n",
"
\n",
" \n",
" 295 | \n",
" 218895 | \n",
" Kulas Inc | \n",
" S2-78676 | \n",
" 38 | \n",
" 89.02 | \n",
" 3382.76 | \n",
" 2018-03-04 01:17:11 | \n",
"
\n",
" \n",
" 296 | \n",
" 642753 | \n",
" Pollich LLC | \n",
" S2-10342 | \n",
" 40 | \n",
" 56.85 | \n",
" 2274.00 | \n",
" 2018-03-04 01:49:22 | \n",
"
\n",
" \n",
" 297 | \n",
" 737550 | \n",
" Fritsch, Russel and Anderson | \n",
" S2-83881 | \n",
" 12 | \n",
" 63.60 | \n",
" 763.20 | \n",
" 2018-03-04 15:26:20 | \n",
"
\n",
" \n",
" 298 | \n",
" 146832 | \n",
" Kiehn-Spinka | \n",
" B1-53636 | \n",
" 5 | \n",
" 72.16 | \n",
" 360.80 | \n",
" 2018-03-04 21:18:04 | \n",
"
\n",
" \n",
"
\n",
"
299 rows × 7 columns
\n",
"
"
],
"text/plain": [
" account number name sku quantity \\\n",
"0 412290 Jerde-Hilpert S2-77896 43 \n",
"1 383080 Will LLC S1-93683 28 \n",
"2 729833 Koepp Ltd S1-30248 13 \n",
"3 424914 White-Trantow S2-82423 38 \n",
"4 672390 Kuhn-Gusikowski S1-50961 34 \n",
"5 239344 Stokes LLC S2-10342 34 \n",
"6 218895 Kulas Inc S2-34077 30 \n",
"7 672390 Kuhn-Gusikowski B1-05914 25 \n",
"8 740150 Barton LLC S1-82801 29 \n",
"9 218895 Kulas Inc B1-20000 23 \n",
"10 257198 Cronin, Oberbrunner and Spencer S1-30248 14 \n",
"11 672390 Kuhn-Gusikowski S2-83881 26 \n",
"12 424914 White-Trantow B1-53636 44 \n",
"13 307599 Kassulke, Ondricka and Metz S2-10342 4 \n",
"14 383080 Will LLC B1-20000 45 \n",
"15 527099 Sanford and Sons S2-82423 44 \n",
"16 146832 Kiehn-Spinka S2-77896 27 \n",
"17 642753 Pollich LLC B1-05914 15 \n",
"18 786968 Frami, Hills and Schmidt S1-65481 14 \n",
"19 527099 Sanford and Sons S1-82801 35 \n",
"20 257198 Cronin, Oberbrunner and Spencer S2-23246 43 \n",
"21 527099 Sanford and Sons B1-65551 6 \n",
"22 218895 Kulas Inc S1-47412 21 \n",
"23 729833 Koepp Ltd S2-10342 12 \n",
"24 412290 Jerde-Hilpert S1-82801 26 \n",
"25 642753 Pollich LLC S2-77896 13 \n",
"26 218895 Kulas Inc B1-33364 17 \n",
"27 307599 Kassulke, Ondricka and Metz S1-50961 28 \n",
"28 146832 Kiehn-Spinka S1-93683 17 \n",
"29 383080 Will LLC S1-82801 3 \n",
".. ... ... ... ... \n",
"269 141962 Herman LLC B1-69924 21 \n",
"270 383080 Will LLC S2-23246 47 \n",
"271 383080 Will LLC B1-20000 4 \n",
"272 672390 Kuhn-Gusikowski S1-93683 10 \n",
"273 424914 White-Trantow S1-93683 -1 \n",
"274 383080 Will LLC S1-93683 41 \n",
"275 714466 Trantow-Barrows S2-11481 46 \n",
"276 740150 Barton LLC S1-30248 21 \n",
"277 218895 Kulas Inc B1-69924 44 \n",
"278 383080 Will LLC B1-53102 6 \n",
"279 688981 Keeling LLC S2-23246 18 \n",
"280 642753 Pollich LLC B1-04202 8 \n",
"281 163416 Purdy-Kunde S1-30248 19 \n",
"282 527099 Sanford and Sons S2-82423 3 \n",
"283 527099 Sanford and Sons B1-50809 8 \n",
"284 737550 Fritsch, Russel and Anderson B1-50809 20 \n",
"285 688981 Keeling LLC B1-86481 -1 \n",
"286 729833 Koepp Ltd S1-82801 26 \n",
"287 307599 Kassulke, Ondricka and Metz S1-93683 -1 \n",
"288 412290 Jerde-Hilpert S2-23246 27 \n",
"289 307599 Kassulke, Ondricka and Metz S2-34077 48 \n",
"290 141962 Herman LLC S2-10342 49 \n",
"291 257198 Cronin, Oberbrunner and Spencer S2-16558 7 \n",
"292 257198 Cronin, Oberbrunner and Spencer S2-23246 40 \n",
"293 688981 Keeling LLC B1-38851 25 \n",
"294 239344 Stokes LLC S1-82801 41 \n",
"295 218895 Kulas Inc S2-78676 38 \n",
"296 642753 Pollich LLC S2-10342 40 \n",
"297 737550 Fritsch, Russel and Anderson S2-83881 12 \n",
"298 146832 Kiehn-Spinka B1-53636 5 \n",
"\n",
" unit price ext price date \n",
"0 76.66 3296.38 2018-03-04 23:10:28 \n",
"1 90.86 2544.08 2018-03-05 05:11:49 \n",
"2 44.84 582.92 2018-03-05 17:33:52 \n",
"3 50.93 1935.34 2018-03-05 21:40:10 \n",
"4 48.20 1638.80 2018-03-06 11:59:00 \n",
"5 36.93 1255.62 2018-03-06 23:49:16 \n",
"6 99.73 2991.90 2018-03-07 05:15:29 \n",
"7 89.86 2246.50 2018-03-07 06:25:52 \n",
"8 60.81 1763.49 2018-03-07 10:24:54 \n",
"9 99.57 2290.11 2018-03-07 13:34:00 \n",
"10 91.37 1279.18 2018-03-07 16:21:58 \n",
"11 88.38 2297.88 2018-03-07 20:18:18 \n",
"12 59.93 2636.92 2018-03-07 21:16:24 \n",
"13 12.99 51.96 2018-03-07 23:59:26 \n",
"14 29.90 1345.50 2018-03-08 01:50:36 \n",
"15 23.30 1025.20 2018-03-09 00:18:55 \n",
"16 70.76 1910.52 2018-03-09 11:05:47 \n",
"17 65.50 982.50 2018-03-09 11:22:09 \n",
"18 44.67 625.38 2018-03-09 19:13:11 \n",
"19 95.17 3330.95 2018-03-09 19:13:20 \n",
"20 87.40 3758.20 2018-03-10 05:39:36 \n",
"21 58.04 348.24 2018-03-10 05:52:17 \n",
"22 94.45 1983.45 2018-03-10 11:02:14 \n",
"23 93.64 1123.68 2018-03-10 13:08:45 \n",
"24 74.79 1944.54 2018-03-11 02:49:11 \n",
"25 48.14 625.82 2018-03-11 06:19:26 \n",
"26 93.01 1581.17 2018-03-11 08:20:57 \n",
"27 34.87 976.36 2018-03-11 10:49:24 \n",
"28 10.63 180.71 2018-03-11 12:43:03 \n",
"29 77.06 231.18 2018-03-11 16:38:10 \n",
".. ... ... ... \n",
"269 95.00 1995.00 2018-02-26 19:08:02 \n",
"270 10.93 513.71 2018-02-26 20:41:02 \n",
"271 35.04 140.16 2018-02-26 23:56:15 \n",
"272 34.96 349.60 2018-02-27 02:58:58 \n",
"273 25.01 -25.01 2018-02-27 10:32:25 \n",
"274 97.29 3988.89 2018-02-27 14:54:11 \n",
"275 39.61 1822.06 2018-02-27 16:12:51 \n",
"276 14.05 295.05 2018-02-28 02:29:03 \n",
"277 72.43 3186.92 2018-02-28 08:42:56 \n",
"278 32.80 196.80 2018-02-28 17:35:23 \n",
"279 64.71 1164.78 2018-02-28 23:21:04 \n",
"280 95.86 766.88 2018-02-28 23:47:32 \n",
"281 65.03 1235.57 2018-03-01 16:07:40 \n",
"282 76.21 228.63 2018-03-01 17:18:01 \n",
"283 70.78 566.24 2018-03-01 18:53:09 \n",
"284 50.11 1002.20 2018-03-01 23:47:17 \n",
"285 97.16 -97.16 2018-03-02 01:46:44 \n",
"286 50.29 1307.54 2018-03-02 02:59:26 \n",
"287 86.38 -86.38 2018-03-02 03:29:04 \n",
"288 58.87 1589.49 2018-03-02 12:10:30 \n",
"289 53.36 2561.28 2018-03-02 14:12:28 \n",
"290 31.39 1538.11 2018-03-02 23:08:02 \n",
"291 26.21 183.47 2018-03-03 00:29:24 \n",
"292 49.19 1967.60 2018-03-03 10:17:24 \n",
"293 74.68 1867.00 2018-03-03 18:18:54 \n",
"294 78.90 3234.90 2018-03-04 01:06:20 \n",
"295 89.02 3382.76 2018-03-04 01:17:11 \n",
"296 56.85 2274.00 2018-03-04 01:49:22 \n",
"297 63.60 763.20 2018-03-04 15:26:20 \n",
"298 72.16 360.80 2018-03-04 21:18:04 \n",
"\n",
"[299 rows x 7 columns]"
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.concat(pd.read_excel(workbook_url, sheet_name=None), ignore_index=True)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.7.2"
}
},
"nbformat": 4,
"nbformat_minor": 2
}