+- Sick Gaming (https://www.sickgaming.net)
+-- Forum: Programming (https://www.sickgaming.net/forum-76.html)
+--- Forum: Python (https://www.sickgaming.net/forum-83.html)
+--- Thread: [Tut] pd.to_excel() – An Unofficial Guide to Saving Data to Excel (/thread-99186.html)
[Tut] pd.to_excel() – An Unofficial Guide to Saving Data to Excel - xSicKxBot - 04-11-2022
pd.to_excel() – An Unofficial Guide to Saving Data to Excel
<div><p>Microsoft Excel is a cross-platform and battle-tested spreadsheet software widely used for data analysis and visualization. It is a powerful and user-friendly tool indeed! But how can we bring it to the next level? </p>
<p>We can combine Excel with Python to</p>
<ul>
<li><strong><a rel="noreferrer noopener" href="https://blog.finxter.com/pandas-read-excel/" data-type="post" data-id="268455" target="_blank">read</a> data from Excel, </strong></li>
<li><strong><a rel="noreferrer noopener" href="https://blog.finxter.com/pandas-read-and-write-excel-files/" data-type="post" data-id="53481" target="_blank">save</a> data to Excel with formats, formulas, and even charts, and </strong></li>
<li><strong><a rel="noreferrer noopener" href="https://blog.finxter.com/python-excel-basic-worksheet-operations/" data-type="post" data-id="37043" target="_blank">automate</a> Excel tasks in Python</strong>! </li>
</ul>
<p>Please continue reading and stay tuned for my Excel in Python series if it sounds great!</p>
<p>This tutorial is all about saving data to Excel. </p>
<p>Concretely, I will first introduce Excel’s data structure and lingos. </p>
<p>Then, you will learn the difference between four popular Python ways to save data to excel, including <code>pandas</code> and <code>openpyxl</code>. </p>
<p>Finally, I will focus on the <code><strong>pandas.DataFrame.to_excel()</strong></code> method. I will guide you through four actual use cases in <code>pandas.DataFrame.to_excel()</code>, ranging from one Excel worksheet, multiple Excel worksheets, multiple Excel <a href="https://blog.finxter.com/python-excel-styling-your-worksheets/" data-type="post" data-id="37112" target="_blank" rel="noreferrer noopener">workbooks</a>, and dealing with index cases. </p>
<p>You can find all datasets and codes in this tutorial <a href="https://github.com/anqiwoo/InterestingPythonPuzzles/tree/master/learn_excel">here</a>. The data in our example datasets are not actual data and are only used for educational purposes.</p>
<h2>Quick Introduction to Excel</h2>
<p>Before jumping into reading data from Excel, let’s look at how data is stored in Excel and get ourselves familiar with some Excel lingos.</p>
<p>Simply put, data is stored in cells in Excel, and each cell can be identified with its unique row and column number pair. </p>
<p>Columns in Excel are labeled in alphabets, starting from ‘<code>A</code>’, and rows in Excel are labeled in roman numbers, starting from ‘<code>1</code>’. For example, in the following Excel picture, <code>A1</code> is the cell in the intersection of the first column and first row, ‘ID’.</p>
<figure class="wp-block-image size-full"><img loading="lazy" width="942" height="693" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-28.png" alt="" class="wp-image-282101" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-28.png 942w, https://blog.finxter.com/wp-content/uploads/2022/04/image-28-300x221.png 300w, https://blog.finxter.com/wp-content/uploads/2022/04/image-28-768x565.png 768w" sizes="(max-width: 942px) 100vw, 942px" /></figure>
<p>Yes, you got the idea! It is just like the DataFrame data structure in our friends, <a href="https://blog.finxter.com/pandas-quickstart/" data-type="post" data-id="16511" target="_blank" rel="noreferrer noopener">pandas</a>.</p>
<p>And if you want to select an area to dump data into Excel, you can imagine drawing a rectangle data region based on the upper-left cell of the part.</p>
<p>On top of that, other commonly seen Excel lingos include worksheets and workbooks. </p>
<ul>
<li>A <strong>worksheet</strong> means a single spreadsheet in an Excel file. </li>
<li>A <strong>workbook </strong>means a single Excel file with extensions like <code>.xlsx</code> and <code>.xls</code>. </li>
</ul>
<p><em><strong>Tip</strong>: More file extensions supported by Excel can be found in the Microsoft official doc </em><a href="https://support.microsoft.com/en-us/office/file-formats-that-are-supported-in-excel-0943ff2c-6014-4e8d-aaea-b83d51d46247"><em>here</em></a><em>.</em></p>
<p>Now, you are ready to know how to read data from Excel!</p>
<h2>Popular Python ways to Save Data to Excel</h2>
<p>Here is the summary of popular Python ways to save data to excel:</p>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="1024" height="495" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-29-1024x495.png" alt="save data to excel python pandas" class="wp-image-282102" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-29-1024x495.png 1024w, https://blog.finxter.com/wp-content/uploads/2022/04/image-29-300x145.png 300w, https://blog.finxter.com/wp-content/uploads/2022/04/image-29-768x371.png 768w, https://blog.finxter.com/wp-content/uploads/2022/04/image-29-1536x742.png 1536w, https://blog.finxter.com/wp-content/uploads/2022/04/image-29.png 1600w" sizes="(max-width: 1024px) 100vw, 1024px" /></figure>
</div>
<p>One of <code>pandas.DataFrame.to_excel()</code>’s advantages is engine compatibility. It uses multiple external engines for different Excel extensions, including <code>openpyxl</code>, <code>xlwt</code>, and <code>xlsxwriter</code>.</p>
<ul>
<li>“<code>xlwt</code>” supports old-style Microsoft Excel versions 95 to 2003 files (<code>.xls</code>).</li>
<li>“<code>openpyxl</code>” supports newer Excel 2010 file formats (<code>.xlsx, .xlsm, .xltx, .xltm</code>).</li>
<li>“<code>xlsxwriter</code>” supports Excel 2007+ file format (<code>.xlsx</code>).</li>
</ul>
<p>In summary, you can use <code>openpyxl</code> and other libraries for specific Excel file formats and basic data processing. And please remember that <strong><code>openpyxl</code> and <code>xlsxwriter</code> support newer Excel file formats</strong>. </p>
<p>However, I <strong>recommend using <code>pandas.DataFrame.to_excel()</code> for data science and analytics applications</strong> because it supports most Excel file formats and accepts a DataFrame object with powerful methods.</p>
<p>Therefore, let’s see how to use <code>pandas.DataFrame.to_excel()</code> to save data to Excel!</p>
<h2>Saving Data to Excel – pandas.DataFrame.to_excel()</h2>
<p>Since <code>pandas.DataFrame.to_excel()</code> is the most powerful and inclusive way to save data from Excel files with different extensions, I will first introduce its syntax and walk you through three use cases with real Python codes below using <code>pandas.DataFrame.to_excel()</code>.</p>
<h3>Meet pandas.ExcelWriter</h3>
<p>Before heading over to the <code>pandas.DataFrame.to_excel</code> method, we need to know a new friend, <code>pandas.ExcelWriter</code>. It is a class for writing <code>pandas.DataFrame</code> objects into excel sheets.</p>
<p>When you are trying to write to <a href="https://blog.finxter.com/how-to-read-and-write-excel-files-with-pandas/" data-type="post" data-id="36842" target="_blank" rel="noreferrer noopener">multiple sheets</a>, you need to create an <code>ExcelWriter</code> object and pass it to <code>pandas.DataFrame.to_excel()</code> as the first parameter.</p>
<p>To create an <code>ExcelWriter</code> object, we pass something to it according to its syntax:</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group=""># Syntax for pandas.ExcelWriter
pandas.ExcelWriter(path[, engine=None[, date_format=None[, datetime_format=None[, mode='w'[, storage_options=None[, if_sheet_exists=None[, engine_kwargs=None[, **kwargs]]]]]])
</pre>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="1024" height="260" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-30-1024x260.png" alt="" class="wp-image-282103" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-30-1024x260.png 1024w, https://blog.finxter.com/wp-content/uploads/2022/04/image-30-300x76.png 300w, https://blog.finxter.com/wp-content/uploads/2022/04/image-30-768x195.png 768w, https://blog.finxter.com/wp-content/uploads/2022/04/image-30-1536x391.png 1536w, https://blog.finxter.com/wp-content/uploads/2022/04/image-30.png 1600w" sizes="(max-width: 1024px) 100vw, 1024px" /></figure>
</div>
<figure class="wp-block-table is-style-stripes">
<table>
<thead>
<tr>
<th>Parameter</th>
<th>Meta</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>path</code></td>
<td>required</td>
<td>String or <code>typing.BinaryIO</code> to an Excel file.</td>
</tr>
<tr>
<td><code>engine</code></td>
<td>optional</td>
<td>String specifying the engine to use for writing. Default is to use : <br /> <code>xlwt</code> for <code>xls </code><br /> <code>xlsxwriter</code> for <code>xlsx</code> if <code>xlsxwriter</code> is installed otherwise <code>openpyxl</code><br /> <code>odf</code> for <code>ods</code></td>
</tr>
<tr>
<td><strong><code>date_format</code></strong></td>
<td>optional</td>
<td>Format string for dates written into Excel files (e.g. <code>'YYYY-MM-DD'</code>).</td>
</tr>
<tr>
<td><strong><code>datetime_format</code></strong></td>
<td>optional</td>
<td>Format string for <code>datetime</code> objects written into Excel files. (e.g. <code>'YYYY-MM-DD HH:MM:SS'</code>).</td>
</tr>
<tr>
<td><strong><code>mode</code></strong></td>
<td>optional</td>
<td>File mode to use (<code>'w'</code> for write; <code>'a'</code> for append). The default is to use <code>'w'</code>.</td>
</tr>
<tr>
<td><code>storage_options</code></td>
<td>optional</td>
<td>A <a rel="noreferrer noopener" href="https://blog.finxter.com/python-dictionary/" data-type="post" data-id="5232" target="_blank">dictionary</a> that makes sense for a particular storage connection, e.g. host, port, username, password, etc.</td>
</tr>
<tr>
<td><code>if_sheet_exists</code></td>
<td>optional</td>
<td>What to do if a sheet exists under the append mode. Accepts <code>{'error', 'new', 'replace', 'overlay'}</code>. Default is <code>'error'</code>.<br /><code>error</code>: raise a <code>ValueError</code>.<br /><code>new</code>: create a new sheet, with a name determined by the engine.<br /><code>replace</code>: delete the contents of the sheet before writing to it.<br /><code>overlay</code>: write contents to the existing sheet without removing the old contents.</td>
</tr>
<tr>
<td><code>engine_kwargs</code></td>
<td>optional</td>
<td>A dictionary containing keyword arguments passed into the engine. </td>
</tr>
</tbody>
</table>
</figure>
<p class="has-global-color-8-background-color has-background"><img src="https://s.w.org/images/core/emoji/13.1.0/72x72/1f4a1.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /> <strong>Tip</strong>: Generally, you only need to use the four parameters in bold format. The default engine is automatically chosen depending on the file extension.</p>
<p>A small note: For compatibility with CSV writers, <code>ExcelWriter</code> serializes <a href="https://blog.finxter.com/python-lists/" data-type="post" data-id="7332" target="_blank" rel="noreferrer noopener">lists</a> and <a href="https://blog.finxter.com/how-to-serialize-a-python-dict-into-a-string-and-back/" data-type="post" data-id="33832" target="_blank" rel="noreferrer noopener">dicts to strings</a> before writing.</p>
<p>Last, if you use the <code><a rel="noreferrer noopener" href="https://blog.finxter.com/python-one-line-with-statement/" data-type="post" data-id="11436" target="_blank">with</a></code> statement to create an <code>ExcelWriter</code>, you do not need to worry about saving the change in the end! An example code to create an <code>ExcelWriter</code> in the <code>with</code> statement is:</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">import pandas as pd df = pd.DataFrame()
excel_wb56_filepath = 'learn_excel_56.xlsx' # Syntax for create an ExcelWriter object in the with statement
# To create a new empty Excel file, learn_excel_56.xlsx with two new sheets!
with pd.ExcelWriter(excel_wb56_filepath) as writer: df.to_excel(writer, sheet_name='Class 5') df.to_excel(writer, sheet_name='Class 6')
</pre>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="1024" height="417" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-31-1024x417.png" alt="" class="wp-image-282104" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-31-1024x417.png 1024w, https://blog.finxter.com/wp-content/uploads/2022/04/image-31-300x122.png 300w, https://blog.finxter.com/wp-content/uploads/2022/04/image-31-768x313.png 768w, https://blog.finxter.com/wp-content/uploads/2022/04/image-31-1536x626.png 1536w, https://blog.finxter.com/wp-content/uploads/2022/04/image-31.png 1600w" sizes="(max-width: 1024px) 100vw, 1024px" /></figure>
</div>
<p>After running the code, we create a new empty Excel file, <code>learn_excel_56.xlsx</code> with two new sheets, Class 5 and Class 6!</p>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="888" height="1024" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-32-888x1024.png" alt="" class="wp-image-282105" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-32-888x1024.png 888w, https://blog.finxter.com/wp-content/uploads/2022/04/image-32-260x300.png 260w, https://blog.finxter.com/wp-content/uploads/2022/04/image-32-768x886.png 768w, https://blog.finxter.com/wp-content/uploads/2022/04/image-32.png 997w" sizes="(max-width: 888px) 100vw, 888px" /></figure>
</div>
<h3>Meet pandas.DataFrame.to_excel</h3>
<p>Hooray! Now, let’s look at the syntax and parameters of the <code>pandas.DataFrame.to_excel</code> method and get ourselves prepared for later examples!</p>
<p>Here is the syntax for <code>pandas.DataFrame.to_excel</code>:</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group=""># Syntax for pandas.DataFrame.to_excel
DataFrame.to_excel(excel_writer[, sheet_name='Sheet1'[, na_rep=''[, float_format=None[, columns=None[, header=True[, index=True[, index_label=None[, startrow=0[, startcol=0[, engine=None[, merge_cells=True[, encoding=None[, inf_rep='inf'[, verbose=True[, freeze_panes=None[, storage_options=None]]]]]]]]]]]]]]]])</pre>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="1024" height="312" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-33-1024x312.png" alt="" class="wp-image-282106" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-33-1024x312.png 1024w, https://blog.finxter.com/wp-content/uploads/2022/04/image-33-300x91.png 300w, https://blog.finxter.com/wp-content/uploads/2022/04/image-33-768x234.png 768w, https://blog.finxter.com/wp-content/uploads/2022/04/image-33-1536x468.png 1536w, https://blog.finxter.com/wp-content/uploads/2022/04/image-33.png 1600w" sizes="(max-width: 1024px) 100vw, 1024px" /></figure>
</div>
<p>To write a single object (<code>pandas.DataFrame</code>) to an Excel <code>.xlsx</code> file, it is only necessary to specify a target file name. To write to multiple sheets, it is necessary to create an <code>ExcelWriter</code> object with a target file name.</p>
<figure class="wp-block-table is-style-stripes">
<table>
<thead>
<tr>
<th>Parameter</th>
<th>Meta</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr>
<td><strong><code>excel_writer</code></strong></td>
<td>Required</td>
<td>Target file name or <code>ExcelWriter</code> object.</td>
</tr>
<tr>
<td><strong><code>sheet_name</code></strong></td>
<td>Optional</td>
<td>Name of sheet containing DataFrame. Default is <code>"Sheet 1"</code>.</td>
</tr>
<tr>
<td><strong><code>na_rep</code></strong></td>
<td>Optional</td>
<td>Missing data representation. <code>Default = ""</code></td>
</tr>
<tr>
<td><code>float_format</code></td>
<td>Optional</td>
<td><a href="https://blog.finxter.com/string-formatting-vs-format-vs-formatted-string-literal/" data-type="post" data-id="13190" target="_blank" rel="noreferrer noopener">Format string</a> for floating point numbers. For example <code>float_format="%.2f"</code> will format 0.1234 to 0.12.</td>
</tr>
<tr>
<td><strong><code>column</code></strong></td>
<td>Optional</td>
<td>Columns (in the DataFrame) to write.</td>
</tr>
<tr>
<td><strong><code>header</code></strong></td>
<td>Optional</td>
<td>Row to be considered as the header, excluding from the data part. <code>Default = True</code>, which means the first row. If <code>None</code>, no header.</td>
</tr>
<tr>
<td><strong><code>index</code></strong></td>
<td>Optional</td>
<td>Write row names (index). Default is <code>True</code>, which means show index. If set to be <code>False</code>, it means no index in the output Excel worksheet.</td>
</tr>
<tr>
<td><strong><code>index_label</code></strong></td>
<td>Optional</td>
<td>Column label for the index.</td>
</tr>
<tr>
<td><strong><code>startrow</code></strong></td>
<td>Optional</td>
<td>Upper left cell row to dump data frame. Default is 0.</td>
</tr>
<tr>
<td><strong><code>startcol</code></strong></td>
<td>Optional</td>
<td>Upper left cell column to dump data frame. Default is 0.</td>
</tr>
<tr>
<td><code>engine</code></td>
<td>Optional</td>
<td>String specifying the engine to use for writing. Default is <code>xlwt</code> for <code>xls</code>. <br /><code>xlsxwriter</code> for <code>xlsx</code> if <code>xlsxwriter</code> is installed; otherwise <code>openpyxl</code>.</td>
</tr>
<tr>
<td><code>merge_cells</code></td>
<td>Optional</td>
<td>Write <code>MultiIndex</code> and Hierarchical Rows as merged cells.</td>
</tr>
<tr>
<td><code>encoding</code></td>
<td>Optional</td>
<td>Encoding of the resulting excel file. Only necessary for <code>xlwt</code>, other writers support Unicode natively.</td>
</tr>
<tr>
<td><code>inf_rep</code></td>
<td>Optional</td>
<td>Representation for infinity (there is no native representation for infinity in Excel).</td>
</tr>
<tr>
<td><code>verbose</code></td>
<td>Optional</td>
<td>Display more information in the error logs.</td>
</tr>
<tr>
<td><code>freeze_panes</code></td>
<td>Optional</td>
<td>Specifies the one-based bottom-most row and right-most column that is to be frozen.</td>
</tr>
<tr>
<td><code>storage_options</code></td>
<td>Optional</td>
<td>Extra options that make sense for a particular storage connection, e.g. host, port, username, password, etc.</td>
</tr>
</tbody>
</table>
</figure>
<p><em>Tip: Generally, you only need to use the four parameters in bold format. </em></p>
<p>Please try to have an intuition for these parameters right now and I will cover the details for <code>sheet_name</code>, <code>index</code>, and <code>index_label</code> parameters in our next exciting examples!</p>
<h3>Getting started</h3>
<p>To use the <code>pandas.DataFrame.to_excel</code> method, you need to first <a href="https://blog.finxter.com/how-to-install-pandas-in-python/" data-type="post" data-id="35926" target="_blank" rel="noreferrer noopener">install </a>the <code>pandas</code> package in your command line:</p>
<pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">$ pip install pandas </pre>
<p><strong>Tip</strong>: you might need to use <code>pip3</code> instead of <code>pip</code>, depending on your environment.</p>
<p>Given the engine compatibility mentioned above, you also need to install respective engine libraries. For example, to use <code>openpyxl</code>, you need to install this package on your command line:</p>
<pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">$ pip install openpyxl</pre>
<p><strong>Tip</strong>: you might need to use <code>pip3</code> instead of <code>pip</code>, depending on your environment.</p>
<p>By the way, if you have already installed <a href="https://blog.finxter.com/python-version-anaconda/" data-type="post" data-id="34921" target="_blank" rel="noreferrer noopener">Anaconda</a>, you can skip this step <img src="https://s.w.org/images/core/emoji/13.1.0/72x72/1f642.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /></p>
<h3>Dataset</h3>
<p>In our examples, we will create Excel workbooks like the two Excel workbooks (<code>.xlsx</code>), <code>learn_excel_12</code> and <code>learn_excel_34</code>, in our first Excel in Python series.</p>
<div class="wp-block-image">
<figure class="aligncenter size-full"><img loading="lazy" width="365" height="92" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-34.png" alt="" class="wp-image-282107" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-34.png 365w, https://blog.finxter.com/wp-content/uploads/2022/04/image-34-300x76.png 300w" sizes="(max-width: 365px) 100vw, 365px" /></figure>
</div>
<p>These workbooks have the same data structures and column names in each worksheet. For example, the following is the data in the <code>Class_1</code> worksheet in the <code>learn_excel_12</code> workbook.</p>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="1024" height="821" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-35-1024x821.png" alt="" class="wp-image-282108" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-35-1024x821.png 1024w, https://blog.finxter.com/wp-content/uploads/2022/04/image-35-300x241.png 300w, https://blog.finxter.com/wp-content/uploads/2022/04/image-35-768x616.png 768w, https://blog.finxter.com/wp-content/uploads/2022/04/image-35.png 1435w" sizes="(max-width: 1024px) 100vw, 1024px" /></figure>
</div>
<p>One row in each sheet represents a student in each class and the columns represent the student’s ID, Major, and Score respectively. </p>
<p>Concretely, <code>learn_excel_12</code> has two worksheets, Class 1 and Class 2. And <code>learn_excel_34</code> has two worksheets, Class 3 and Class 4.</p>
<p>You can find all datasets and codes in this tutorial <a href="https://github.com/anqiwoo/InterestingPythonPuzzles/tree/master/learn_excel" target="_blank" rel="noreferrer noopener">here</a>. The data in our example datasets are not actual data and are only used for educational purposes.</p>
<h3>Save Data to One Worksheet</h3>
<p>So, how can we save data to a single excel sheet? We can pass a target file name or create an <code>ExcelWriter</code> object to do so!</p>
<p>For our example, we can create a new Excel file, <code>learn_excel_56</code> and write some student data into the Class 5 sheet, specifying through the parameter <code>sheet_name</code>.</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">import pandas as pd # For our writing mode example,
# we can create a new Excel file, learn_excel_56
# and write some student data into it.
excel_fp = 'learn_excel_56.xlsx'
class5_df = pd.DataFrame( {'ID': [51, 52], 'Major': ['English', 'Math'], 'Score': [98, 89]}) with pd.ExcelWriter(excel_fp, mode='w') as writer: class5_df.to_excel(writer, sheet_name='Class 5', index=False)
</pre>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="1024" height="444" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-36-1024x444.png" alt="" class="wp-image-282109" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-36-1024x444.png 1024w, https://blog.finxter.com/wp-content/uploads/2022/04/image-36-300x130.png 300w, https://blog.finxter.com/wp-content/uploads/2022/04/image-36-768x333.png 768w, https://blog.finxter.com/wp-content/uploads/2022/04/image-36-1536x665.png 1536w, https://blog.finxter.com/wp-content/uploads/2022/04/image-36.png 1600w" sizes="(max-width: 1024px) 100vw, 1024px" /></figure>
</div>
<p>After running the code, we can get the output Excel file:</p>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="827" height="1024" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-37-827x1024.png" alt="" class="wp-image-282110" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-37-827x1024.png 827w, https://blog.finxter.com/wp-content/uploads/2022/04/image-37-242x300.png 242w, https://blog.finxter.com/wp-content/uploads/2022/04/image-37-768x951.png 768w, https://blog.finxter.com/wp-content/uploads/2022/04/image-37.png 946w" sizes="(max-width: 827px) 100vw, 827px" /></figure>
</div>
<h3>Save Data to Multiple Worksheets</h3>
<p>Likewise, we can save data to multiple worksheets by calling <code>pandas.DataFrame.to_excel()</code> method multiple times.</p>
<p>For our example, we can create a new Excel file, <code>learn_excel_56</code> and write some student data into the Class 5 sheet and Class 6 sheet, specifying through the parameter <code>sheet_name</code>.</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">import pandas as pd # For our writing mode example,
# we can create a new Excel file, learn_excel_56
# and write some student data into it.
excel_fp = 'learn_excel_56.xlsx'
class5_df = pd.DataFrame( {'ID': [51, 52], 'Major': ['English', 'Math'], 'Score': [98, 89]})
class6_df = pd.DataFrame( {'ID': [61, 62], 'Major': ['History', 'Math'], 'Score': [78, 96]}) with pd.ExcelWriter(excel_fp, mode='w') as writer: class5_df.to_excel(writer, sheet_name='Class 5', index=False) class6_df.to_excel(writer, sheet_name='Class 6', index=False)
</pre>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="1024" height="521" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-38-1024x521.png" alt="" class="wp-image-282111" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-38-1024x521.png 1024w, https://blog.finxter.com/wp-content/uploads/2022/04/image-38-300x153.png 300w, https://blog.finxter.com/wp-content/uploads/2022/04/image-38-768x391.png 768w, https://blog.finxter.com/wp-content/uploads/2022/04/image-38-1536x781.png 1536w, https://blog.finxter.com/wp-content/uploads/2022/04/image-38.png 1600w" sizes="(max-width: 1024px) 100vw, 1024px" /></figure>
</div>
<p>After running the code, we can get the output Excel file:</p>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="736" height="1024" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-39-736x1024.png" alt="" class="wp-image-282112" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-39-736x1024.png 736w, https://blog.finxter.com/wp-content/uploads/2022/04/image-39-216x300.png 216w, https://blog.finxter.com/wp-content/uploads/2022/04/image-39-768x1068.png 768w, https://blog.finxter.com/wp-content/uploads/2022/04/image-39.png 823w" sizes="(max-width: 736px) 100vw, 736px" /></figure>
</div>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="760" height="1024" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-40-760x1024.png" alt="" class="wp-image-282113" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-40-760x1024.png 760w, https://blog.finxter.com/wp-content/uploads/2022/04/image-40-223x300.png 223w, https://blog.finxter.com/wp-content/uploads/2022/04/image-40-768x1034.png 768w, https://blog.finxter.com/wp-content/uploads/2022/04/image-40.png 860w" sizes="(max-width: 760px) 100vw, 760px" /></figure>
</div>
<h3>Save Data to Multiple Workbooks</h3>
<p>To get multiple workbooks, we can just create multiple <code>pandas.ExcelWriter</code> objects.</p>
<p><img src="https://s.w.org/images/core/emoji/13.1.0/72x72/1f642.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /></p>
<p>To give a quick example, let’s create two workbooks, <code>learn_excel_78</code> and <code>learn_excel_910</code>.</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">import pandas as pd # let’s create two workbooks, learn_excel_78 and learn_excel_910.
wb78_fp = 'learn_excel_78.xlsx'
wb910_fp = 'learn_excel_910.xlsx' df = pd.DataFrame() with pd.ExcelWriter(wb78_fp, mode='w') as writer_78, pd.ExcelWriter(wb910_fp, mode='w') as writer_910: df.to_excel(writer_78, sheet_name='Class 7', index=False) df.to_excel(writer_78, sheet_name='Class 8', index=False) df.to_excel(writer_910, sheet_name='Class 9', index=False) df.to_excel(writer_910, sheet_name='Class 10', index=False)
</pre>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="1024" height="547" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-41-1024x547.png" alt="" class="wp-image-282114" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-41-1024x547.png 1024w, https://blog.finxter.com/wp-content/uploads/2022/04/image-41-300x160.png 300w, https://blog.finxter.com/wp-content/uploads/2022/04/image-41-768x410.png 768w, https://blog.finxter.com/wp-content/uploads/2022/04/image-41-1536x820.png 1536w, https://blog.finxter.com/wp-content/uploads/2022/04/image-41.png 1600w" sizes="(max-width: 1024px) 100vw, 1024px" /></figure>
</div>
<p>After running the code, we can get the output workbooks:</p>
<div class="wp-block-image">
<figure class="aligncenter size-full"><img loading="lazy" width="357" height="102" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-42.png" alt="" class="wp-image-282115" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-42.png 357w, https://blog.finxter.com/wp-content/uploads/2022/04/image-42-300x86.png 300w" sizes="(max-width: 357px) 100vw, 357px" /></figure>
</div>
<p>In the <code>learn_excel_78</code> file, we can see that we have created two empty worksheets:</p>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="602" height="1024" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-43-602x1024.png" alt="" class="wp-image-282116" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-43-602x1024.png 602w, https://blog.finxter.com/wp-content/uploads/2022/04/image-43-176x300.png 176w, https://blog.finxter.com/wp-content/uploads/2022/04/image-43.png 679w" sizes="(max-width: 602px) 100vw, 602px" /></figure>
</div>
<p>So far, we have understood the basic writing operations. Let’s move forward and deal with the most common issues—index and date <img src="https://s.w.org/images/core/emoji/13.1.0/72x72/1f642.png" alt="?" class="wp-smiley" style="height: 1em; max-height: 1em;" /></p>
<h3>Deal with Index</h3>
<p>You can set up the index and index’ column label when calling the <code>pandas.DataFrame.to_excel()</code> method.</p>
<p>Previously, our example codes set the index to be <code>False</code>, which means no index column in the output Excel file. Let’s see what will happen if we set the index to be <code>True</code> in the multiple worksheets scenario.</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">import pandas as pd # For our writing mode example,
# we can create a new Excel file, learn_excel_56
# and write some student data into it.
excel_fp = 'learn_excel_56.xlsx'
class5_df = pd.DataFrame( {'ID': [51, 52], 'Major': ['English', 'Math'], 'Score': [98, 89]})
class6_df = pd.DataFrame( {'ID': [61, 62], 'Major': ['History', 'Math'], 'Score': [78, 96]}) with pd.ExcelWriter(excel_fp, mode='w') as writer: class5_df.to_excel(writer, sheet_name='Class 5', index=True) class6_df.to_excel(writer, sheet_name='Class 6', index=True)
</pre>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="1024" height="521" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-44-1024x521.png" alt="" class="wp-image-282117" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-44-1024x521.png 1024w, https://blog.finxter.com/wp-content/uploads/2022/04/image-44-300x153.png 300w, https://blog.finxter.com/wp-content/uploads/2022/04/image-44-768x391.png 768w, https://blog.finxter.com/wp-content/uploads/2022/04/image-44-1536x781.png 1536w, https://blog.finxter.com/wp-content/uploads/2022/04/image-44.png 1600w" sizes="(max-width: 1024px) 100vw, 1024px" /></figure>
</div>
<p>After running the code, we can see that we now have an index column, counting from zero.</p>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="973" height="1024" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-45-973x1024.png" alt="" class="wp-image-282118" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-45-973x1024.png 973w, https://blog.finxter.com/wp-content/uploads/2022/04/image-45-285x300.png 285w, https://blog.finxter.com/wp-content/uploads/2022/04/image-45-768x808.png 768w, https://blog.finxter.com/wp-content/uploads/2022/04/image-45.png 1083w" sizes="(max-width: 973px) 100vw, 973px" /></figure>
</div>
<p>On top of that, we can give a column name to the index column by specifying the parameter, <code>index_label</code>.</p>
<pre class="EnlighterJSRAW" data-enlighter-language="python" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">import pandas as pd # For our writing mode example,
# we can create a new Excel file, learn_excel_56
# and write some student data into it.
excel_fp = 'learn_excel_56.xlsx'
class5_df = pd.DataFrame( {'ID': [51, 52], 'Major': ['English', 'Math'], 'Score': [98, 89]})
class6_df = pd.DataFrame( {'ID': [61, 62], 'Major': ['History', 'Math'], 'Score': [78, 96]}) with pd.ExcelWriter(excel_fp, mode='w') as writer: class5_df.to_excel(writer, sheet_name='Class 5', index=True, index_label='No.') class6_df.to_excel(writer, sheet_name='Class 6', index=True, index_label='No.')
</pre>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="1024" height="573" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-46-1024x573.png" alt="" class="wp-image-282119" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-46-1024x573.png 1024w, https://blog.finxter.com/wp-content/uploads/2022/04/image-46-300x168.png 300w, https://blog.finxter.com/wp-content/uploads/2022/04/image-46-768x430.png 768w, https://blog.finxter.com/wp-content/uploads/2022/04/image-46-1536x859.png 1536w, https://blog.finxter.com/wp-content/uploads/2022/04/image-46.png 1600w" sizes="(max-width: 1024px) 100vw, 1024px" /></figure>
</div>
<p>After running the code, we can see that we now have an index column with a name, “No.”!</p>
<div class="wp-block-image">
<figure class="aligncenter size-large"><img loading="lazy" width="1024" height="853" src="https://blog.finxter.com/wp-content/uploads/2022/04/image-47-1024x853.png" alt="" class="wp-image-282120" srcset="https://blog.finxter.com/wp-content/uploads/2022/04/image-47-1024x853.png 1024w, https://blog.finxter.com/wp-content/uploads/2022/04/image-47-300x250.png 300w, https://blog.finxter.com/wp-content/uploads/2022/04/image-47-768x639.png 768w, https://blog.finxter.com/wp-content/uploads/2022/04/image-47.png 1380w" sizes="(max-width: 1024px) 100vw, 1024px" /></figure>
</div>
<h2>Conclusion</h2>
<p>That’s how to save data to Excel. This is the second article in our Excel in Python series. </p>
<p>We learned about Excel’s data structure and commonly used lingos and four popular ways to save data to Excel in Python, including <code>pandas</code> and <code>openpyxl</code>. </p>
<p>Finally, we looked at four actual use cases in <code>pandas.DataFrame.to_excel</code>, ranging from one Excel worksheet, multiple Excel worksheets, multiple Excel workbooks, and dealing with index cases.</p>
<p>I hope you enjoy all this, and stay tuned for our following Excel in Python article on saving data to Excel! Happy coding!</p>
<hr class="wp-block-separator"/>
</div>
https://www.sickgaming.net/blog/2022/04/04/pd-to_excel-an-unofficial-guide-to-saving-data-to-excel/