{"id":327,"date":"2022-05-01T00:00:00","date_gmt":"2022-05-01T00:00:00","guid":{"rendered":"https:\/\/tac.debuzzify.com\/?p=327"},"modified":"2023-06-27T06:30:15","modified_gmt":"2023-06-27T06:30:15","slug":"pandas-excel-multiple-sheets","status":"publish","type":"post","link":"https:\/\/www.the-analytics.club\/pandas-excel-multiple-sheets\/","title":{"rendered":"How Do I Read Multiple Excel Sheets in Python?"},"content":{"rendered":"\n<p>Pandas have a few limitations when working with multiple sheets in an Excel file. However, there is a small hack that can help us work around these limitations and make working with numerous sheets more efficient.<\/p>\n\n\n\n<p>One of the challenges when working with multiple sheets in Pandas is keeping track of which sheet data is coming from. We can alleviate this issue by creating a dictionary of sheet names and DataFrames. We can reference the sheets by name instead of remembering which sheet number corresponds to which DataFrame.<\/p>\n\n\n\n<p><b>Related:<\/b> <a href=\"\/summarize-datasets-in-pandas\"><b><i>A Better Way to Summarize Pandas Dataframes.<\/i><\/b><\/a><\/p>\n\n\n\n<p>Another limitation when working with multiple sheets in Pandas is that the read_excel() function can only read one sheet at a time. If we want to read data from various sheets, we must use the read_excel() function multiple times.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The trick to efficiently reading Excel files with multiple sheets in Pandas<\/h2>\n\n\n\n<p>We can work around this limitation by setting the sheet name argument to None in the read_excel function.<\/p>\n\n\n\n<p><script><br \/>\n            \/* This is ugly, but to ensure we only create this function once, and only call<br \/>\n            each JS library once, we need to check here in case there are multiple Wagtail<br \/>\n            blocks on this page. This will ensure we only load the minimum payload. *\/<br \/>\n            if(typeof loadPrismLanguage != 'function') {<br \/>\n                window.loadPrismLanguage = function(language) {<br \/>\n                    var libraries = [<br \/>\n                        {<br \/>\n                            \"id\": \"code-block-prismjs\",<br \/>\n                            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/prism.min.js\"<br \/>\n                        },<br \/>\n                        {<br \/>\n                            \"id\": \"code-block-prismjs-\" + language,<br \/>\n                            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/components\/prism-\" + language + \".min.js\"<br \/>\n                        },<br \/>\n        {<br \/>\n            \"id\": \"code-block-line-numbers\",<br \/>\n            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/plugins\/line-numbers\/prism-line-numbers.min.js\"<br \/>\n        }<\/p>\n<p>                    ];<\/p>\n<p>                    for(const library of libraries) {<br \/>\n                        if(document.getElementById(library[\"id\"]) == null) {<br \/>\n                            var s = document.createElement(\"script\");<br \/>\n                            s.id = library[\"id\"];<br \/>\n                            s.type = \"text\/javascript\";<br \/>\n                            s.src = library[\"url\"];<br \/>\n                            s.async = false;<br \/>\n                            document.body.appendChild(s);<br \/>\n                        }<br \/>\n                    }<br \/>\n                };<br \/>\n            }<\/p>\n<p>            loadPrismLanguage('python');<\/p>\n<p>            language_class_name = 'language-python';<br \/>\n            <\/script><\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro padding-bottom-disabled\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:16px;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:20px\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"df_dict = pd.read_excel('\/path\/to\/exel.xlsx', sheet_name=None)\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D8DEE9FF\">df_dict <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pd<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">read_excel<\/span><span style=\"color: #ECEFF4\">(<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">\/path\/to\/exel.xlsx<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">sheet_name<\/span><span style=\"color: #81A1C1\">=None<\/span><span style=\"color: #ECEFF4\">)<\/span><\/span><\/code><\/pre><span style=\"display:flex;align-items:flex-end;padding:10px;width:100%;justify-content:flex-end;background-color:#2e3440ff;color:#c8d0e0;font-size:12px;line-height:1;position:relative\">Python<\/span><\/div>\n\n\n\n<p><script><br \/>\n                var block_num = (typeof block_num === 'undefined') ? 0 : block_num;<br \/>\n                block_num++;<br \/>\n                document.getElementById('target-element-current').className = language_class_name;<br \/>\n                document.getElementById('target-element-current').id = 'target-element-' + block_num;<br \/>\n            <\/script><\/p>\n\n\n\n<p>The above function creates a dictionary with sheet names in the Excel files as keys and dataframe as values. You can now access the dataframe with its sheet name.<\/p>\n\n\n\n<p><script><br \/>\n            \/* This is ugly, but to ensure we only create this function once, and only call<br \/>\n            each JS library once, we need to check here in case there are multiple Wagtail<br \/>\n            blocks on this page. This will ensure we only load the minimum payload. *\/<br \/>\n            if(typeof loadPrismLanguage != 'function') {<br \/>\n                window.loadPrismLanguage = function(language) {<br \/>\n                    var libraries = [<br \/>\n                        {<br \/>\n                            \"id\": \"code-block-prismjs\",<br \/>\n                            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/prism.min.js\"<br \/>\n                        },<br \/>\n                        {<br \/>\n                            \"id\": \"code-block-prismjs-\" + language,<br \/>\n                            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/components\/prism-\" + language + \".min.js\"<br \/>\n                        },<br \/>\n        {<br \/>\n            \"id\": \"code-block-line-numbers\",<br \/>\n            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/plugins\/line-numbers\/prism-line-numbers.min.js\"<br \/>\n        }<\/p>\n<p>                    ];<\/p>\n<p>                    for(const library of libraries) {<br \/>\n                        if(document.getElementById(library[\"id\"]) == null) {<br \/>\n                            var s = document.createElement(\"script\");<br \/>\n                            s.id = library[\"id\"];<br \/>\n                            s.type = \"text\/javascript\";<br \/>\n                            s.src = library[\"url\"];<br \/>\n                            s.async = false;<br \/>\n                            document.body.appendChild(s);<br \/>\n                        }<br \/>\n                    }<br \/>\n                };<br \/>\n            }<\/p>\n<p>            loadPrismLanguage('json');<\/p>\n<p>            language_class_name = 'language-json';<br \/>\n            <\/script><\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro padding-bottom-disabled\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:16px;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:20px\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"{\n  'sheet1': pd.DataFrame,\n  'sheet2': pd.DataFrame,\n  'sheet3': pd.DataFrame,\n}\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ECEFF4\">{<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">sheet1<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #ECEFF4\">:<\/span><span style=\"color: #D8DEE9FF\"> pd<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9FF\">DataFrame<\/span><span style=\"color: #ECEFF4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">sheet2<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #ECEFF4\">:<\/span><span style=\"color: #D8DEE9FF\"> pd<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9FF\">DataFrame<\/span><span style=\"color: #ECEFF4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">  <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">sheet3<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #ECEFF4\">:<\/span><span style=\"color: #D8DEE9FF\"> pd<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9FF\">DataFrame<\/span><span style=\"color: #ECEFF4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ECEFF4\">}<\/span><\/span><\/code><\/pre><span style=\"display:flex;align-items:flex-end;padding:10px;width:100%;justify-content:flex-end;background-color:#2e3440ff;color:#c8d0e0;font-size:12px;line-height:1;position:relative\">Python<\/span><\/div>\n\n\n\n<p><script><br \/>\n                var block_num = (typeof block_num === 'undefined') ? 0 : block_num;<br \/>\n                block_num++;<br \/>\n                document.getElementById('target-element-current').className = language_class_name;<br \/>\n                document.getElementById('target-element-current').id = 'target-element-' + block_num;<br \/>\n            <\/script><\/p>\n\n\n\n<p><script><br \/>\n            \/* This is ugly, but to ensure we only create this function once, and only call<br \/>\n            each JS library once, we need to check here in case there are multiple Wagtail<br \/>\n            blocks on this page. This will ensure we only load the minimum payload. *\/<br \/>\n            if(typeof loadPrismLanguage != 'function') {<br \/>\n                window.loadPrismLanguage = function(language) {<br \/>\n                    var libraries = [<br \/>\n                        {<br \/>\n                            \"id\": \"code-block-prismjs\",<br \/>\n                            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/prism.min.js\"<br \/>\n                        },<br \/>\n                        {<br \/>\n                            \"id\": \"code-block-prismjs-\" + language,<br \/>\n                            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/components\/prism-\" + language + \".min.js\"<br \/>\n                        },<br \/>\n        {<br \/>\n            \"id\": \"code-block-line-numbers\",<br \/>\n            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/plugins\/line-numbers\/prism-line-numbers.min.js\"<br \/>\n        }<\/p>\n<p>                    ];<\/p>\n<p>                    for(const library of libraries) {<br \/>\n                        if(document.getElementById(library[\"id\"]) == null) {<br \/>\n                            var s = document.createElement(\"script\");<br \/>\n                            s.id = library[\"id\"];<br \/>\n                            s.type = \"text\/javascript\";<br \/>\n                            s.src = library[\"url\"];<br \/>\n                            s.async = false;<br \/>\n                            document.body.appendChild(s);<br \/>\n                        }<br \/>\n                    }<br \/>\n                };<br \/>\n            }<\/p>\n<p>            loadPrismLanguage('python');<\/p>\n<p>            language_class_name = 'language-python';<br \/>\n            <\/script><\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro padding-bottom-disabled\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:16px;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:20px\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"df1 = df_dict.get('sheet1')\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D8DEE9FF\">df1 <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> df_dict<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">get<\/span><span style=\"color: #ECEFF4\">(<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">sheet1<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #ECEFF4\">)<\/span><\/span><\/code><\/pre><span style=\"display:flex;align-items:flex-end;padding:10px;width:100%;justify-content:flex-end;background-color:#2e3440ff;color:#c8d0e0;font-size:12px;line-height:1;position:relative\">Python<\/span><\/div>\n\n\n\n<p><script><br \/>\n                var block_num = (typeof block_num === 'undefined') ? 0 : block_num;<br \/>\n                block_num++;<br \/>\n                document.getElementById('target-element-current').className = language_class_name;<br \/>\n                document.getElementById('target-element-current').id = 'target-element-' + block_num;<br \/>\n            <\/script><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Combining multiple sheets in an Excel file using Pandas<\/h2>\n\n\n\n<p><a href=\"https:\/\/www.the-analytics.club\/welcome-to-the-age-of-citizen-data-scientists\/\" title=\"Welcome to the Age of Citizen Data Scientists\">Often data scientists and analysts receive Excel<\/a> files with multiple sheets. However, they are just a logical partitioning of a larger dataset in most cases.<\/p>\n\n\n\n<p>For instance, a dataset is partitioned by month and stored in individual sheets rather than one big file.<\/p>\n\n\n\n<p>It is also possible to conveniently combine sheets in an Excel file using the values method of the df_dict.<\/p>\n\n\n\n<p><script><br \/>\n            \/* This is ugly, but to ensure we only create this function once, and only call<br \/>\n            each JS library once, we need to check here in case there are multiple Wagtail<br \/>\n            blocks on this page. This will ensure we only load the minimum payload. *\/<br \/>\n            if(typeof loadPrismLanguage != 'function') {<br \/>\n                window.loadPrismLanguage = function(language) {<br \/>\n                    var libraries = [<br \/>\n                        {<br \/>\n                            \"id\": \"code-block-prismjs\",<br \/>\n                            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/prism.min.js\"<br \/>\n                        },<br \/>\n                        {<br \/>\n                            \"id\": \"code-block-prismjs-\" + language,<br \/>\n                            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/components\/prism-\" + language + \".min.js\"<br \/>\n                        },<br \/>\n        {<br \/>\n            \"id\": \"code-block-line-numbers\",<br \/>\n            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/plugins\/line-numbers\/prism-line-numbers.min.js\"<br \/>\n        }<\/p>\n<p>                    ];<\/p>\n<p>                    for(const library of libraries) {<br \/>\n                        if(document.getElementById(library[\"id\"]) == null) {<br \/>\n                            var s = document.createElement(\"script\");<br \/>\n                            s.id = library[\"id\"];<br \/>\n                            s.type = \"text\/javascript\";<br \/>\n                            s.src = library[\"url\"];<br \/>\n                            s.async = false;<br \/>\n                            document.body.appendChild(s);<br \/>\n                        }<br \/>\n                    }<br \/>\n                };<br \/>\n            }<\/p>\n<p>            loadPrismLanguage('python');<\/p>\n<p>            language_class_name = 'language-python';<br \/>\n            <\/script><\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro padding-bottom-disabled\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:16px;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:20px\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"df = pd.concat(df_dict.values())\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D8DEE9FF\">df <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pd<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">concat<\/span><span style=\"color: #ECEFF4\">(<\/span><span style=\"color: #D8DEE9FF\">df_dict<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">values<\/span><span style=\"color: #ECEFF4\">())<\/span><\/span><\/code><\/pre><span style=\"display:flex;align-items:flex-end;padding:10px;width:100%;justify-content:flex-end;background-color:#2e3440ff;color:#c8d0e0;font-size:12px;line-height:1;position:relative\">Python<\/span><\/div>\n\n\n\n<p><script><br \/>\n                var block_num = (typeof block_num === 'undefined') ? 0 : block_num;<br \/>\n                block_num++;<br \/>\n                document.getElementById('target-element-current').className = language_class_name;<br \/>\n                document.getElementById('target-element-current').id = 'target-element-' + block_num;<br \/>\n            <\/script><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Please don&#8217;t loop through sheet names to read them<\/h2>\n\n\n\n<p>Before I learned this trick, I was, in fact, looping through the sheet names and reading them individually. The code snippet to do this would look like the one below.<\/p>\n\n\n\n<p><script><br \/>\n            \/* This is ugly, but to ensure we only create this function once, and only call<br \/>\n            each JS library once, we need to check here in case there are multiple Wagtail<br \/>\n            blocks on this page. This will ensure we only load the minimum payload. *\/<br \/>\n            if(typeof loadPrismLanguage != 'function') {<br \/>\n                window.loadPrismLanguage = function(language) {<br \/>\n                    var libraries = [<br \/>\n                        {<br \/>\n                            \"id\": \"code-block-prismjs\",<br \/>\n                            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/prism.min.js\"<br \/>\n                        },<br \/>\n                        {<br \/>\n                            \"id\": \"code-block-prismjs-\" + language,<br \/>\n                            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/components\/prism-\" + language + \".min.js\"<br \/>\n                        },<br \/>\n        {<br \/>\n            \"id\": \"code-block-line-numbers\",<br \/>\n            \"url\": \"\/\/cdnjs.cloudflare.com\/ajax\/libs\/prism\/1.25.0\/plugins\/line-numbers\/prism-line-numbers.min.js\"<br \/>\n        }<\/p>\n<p>                    ];<\/p>\n<p>                    for(const library of libraries) {<br \/>\n                        if(document.getElementById(library[\"id\"]) == null) {<br \/>\n                            var s = document.createElement(\"script\");<br \/>\n                            s.id = library[\"id\"];<br \/>\n                            s.type = \"text\/javascript\";<br \/>\n                            s.src = library[\"url\"];<br \/>\n                            s.async = false;<br \/>\n                            document.body.appendChild(s);<br \/>\n                        }<br \/>\n                    }<br \/>\n                };<br \/>\n            }<\/p>\n<p>            loadPrismLanguage('python');<\/p>\n<p>            language_class_name = 'language-python';<br \/>\n            <\/script><\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro padding-bottom-disabled\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:16px;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:20px\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"# Create an ExcelFile object by reading the file\nf = pd.ExcelFile('path\/to\/excel.xlsx')\n\n# Create a placeholder array to store the dataframes\ndfs = []\n\n# Iterate through each sheet's name\nfor sheet in f.sheet_names:\n    \n    # Parse the sheet and convert it to a Pandas dataframe\n    df = f.parse(sheet)\n\n    # append it to our dataframes collection\n    dfs.append(df)\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #616E88\"># Create an ExcelFile object by reading the file<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">f <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pd<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">ExcelFile<\/span><span style=\"color: #ECEFF4\">(<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">path\/to\/excel.xlsx<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #ECEFF4\">)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #616E88\"># Create a placeholder array to store the dataframes<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">dfs <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">[]<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #616E88\"># Iterate through each sheet&#39;s name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">for<\/span><span style=\"color: #D8DEE9FF\"> sheet <\/span><span style=\"color: #81A1C1\">in<\/span><span style=\"color: #D8DEE9FF\"> f<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9FF\">sheet_names<\/span><span style=\"color: #ECEFF4\">:<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #616E88\"># Parse the sheet and convert it to a Pandas dataframe<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    df <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> f<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">parse<\/span><span style=\"color: #ECEFF4\">(<\/span><span style=\"color: #D8DEE9FF\">sheet<\/span><span style=\"color: #ECEFF4\">)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #616E88\"># append it to our dataframes collection<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    dfs<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">append<\/span><span style=\"color: #ECEFF4\">(<\/span><span style=\"color: #D8DEE9FF\">df<\/span><span style=\"color: #ECEFF4\">)<\/span><\/span><\/code><\/pre><span style=\"display:flex;align-items:flex-end;padding:10px;width:100%;justify-content:flex-end;background-color:#2e3440ff;color:#c8d0e0;font-size:12px;line-height:1;position:relative\">Python<\/span><\/div>\n\n\n\n<p><script><br \/>\n                var block_num = (typeof block_num === 'undefined') ? 0 : block_num;<br \/>\n                block_num++;<br \/>\n                document.getElementById('target-element-current').className = language_class_name;<br \/>\n                document.getElementById('target-element-current').id = 'target-element-' + block_num;<br \/>\n            <\/script><\/p>\n\n\n\n<p>This code does the job with not much difficulty. However, we can eliminate these extra lines of code by simply setting sheetname=None when reading the file.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>It&#8217;s difficult to work with multiple sheets in Pandas when parsing Excel files. Yet, a small hack will help us do it better.<\/p>\n","protected":false},"author":2,"featured_media":161,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_kad_blocks_custom_css":"","_kad_blocks_head_custom_js":"","_kad_blocks_body_custom_js":"","_kad_blocks_footer_custom_js":"","_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"footnotes":""},"categories":[3,5],"tags":[],"taxonomy_info":{"category":[{"value":3,"label":"Python"},{"value":5,"label":"Programming"}]},"featured_image_src_large":["https:\/\/www.the-analytics.club\/wp-content\/uploads\/2023\/06\/many-sheets-1024x683.webp",1024,683,true],"author_info":{"display_name":"Thuwarakesh","author_link":"https:\/\/www.the-analytics.club\/author\/thuwarakesh\/"},"comment_info":0,"category_info":[{"term_id":3,"name":"Python","slug":"python","term_group":0,"term_taxonomy_id":3,"taxonomy":"category","description":"","parent":5,"count":52,"filter":"raw","cat_ID":3,"category_count":52,"category_description":"","cat_name":"Python","category_nicename":"python","category_parent":5},{"term_id":5,"name":"Programming","slug":"programming","term_group":0,"term_taxonomy_id":5,"taxonomy":"category","description":"","parent":0,"count":43,"filter":"raw","cat_ID":5,"category_count":43,"category_description":"","cat_name":"Programming","category_nicename":"programming","category_parent":0}],"tag_info":false,"_links":{"self":[{"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/posts\/327"}],"collection":[{"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/comments?post=327"}],"version-history":[{"count":3,"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/posts\/327\/revisions"}],"predecessor-version":[{"id":1311,"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/posts\/327\/revisions\/1311"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/media\/161"}],"wp:attachment":[{"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/media?parent=327"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/categories?post=327"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.the-analytics.club\/wp-json\/wp\/v2\/tags?post=327"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}