snipt

Ctrl+h for KB shortcuts

Python

Export a Python pandas dataframe in Excel

import pandas, sys, subprocess

def open_in_excel(df, index=True, excel_path="excel.exe", tmp_path='.'):
    """Open dataframe df in excel (proposed by Ross R on 
    http://stackoverflow.com/questions/10636024/python-pandas-gui-for-viewing-a-dataframe-or-matrix)

    excel_path - path to your copy of excel
    index=True - export the index of the dataframe as the first columns
    tmp_path    - directory to save the file in


    This creates a temporary file name, exports the dataframe to a csv of that file name,
    and then tells excel to open the file (in read only mode). (It uses df.to_csv instead
    of to_excel because if you don't have excel, you still get the csv.)

    Note - this does NOT delete the file when you exit. 
    """

    f=tempfile.NamedTemporaryFile(delete=False, dir=tmp_path, suffix='.csv', prefix='tmp_')
    tmp_name=f.name
    f.close()

    df.to_csv(tmp_name, index=index)
    cmd=[excel_path, '/r', '/e', tmp_name]
    try:
        ret_val=subprocess.Popen(cmd).pid
    except:
        print "open_in_excel(): failed to open excel"
        print "filename = ", tmp_name
        print "command line = ", cmd
        print "Unexpected error:", sys.exc_info()[0]
    # end try

# end def open_in_excel
https://snipt.net/embed/8b48f1e8fd3920bf6afdaf0f53e684a6/
/raw/8b48f1e8fd3920bf6afdaf0f53e684a6/
8b48f1e8fd3920bf6afdaf0f53e684a6
python
Python
34
2019-07-16T03:55:41
True
False
False
/api/public/snipt/54475/
export-a-python-pandas-dataframe-in-excel
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1"> 1</a> <a href="#L-2"> 2</a> <a href="#L-3"> 3</a> <a href="#L-4"> 4</a> <a href="#L-5"> 5</a> <a href="#L-6"> 6</a> <a href="#L-7"> 7</a> <a href="#L-8"> 8</a> <a href="#L-9"> 9</a> <a href="#L-10">10</a> <a href="#L-11">11</a> <a href="#L-12">12</a> <a href="#L-13">13</a> <a href="#L-14">14</a> <a href="#L-15">15</a> <a href="#L-16">16</a> <a href="#L-17">17</a> <a href="#L-18">18</a> <a href="#L-19">19</a> <a href="#L-20">20</a> <a href="#L-21">21</a> <a href="#L-22">22</a> <a href="#L-23">23</a> <a href="#L-24">24</a> <a href="#L-25">25</a> <a href="#L-26">26</a> <a href="#L-27">27</a> <a href="#L-28">28</a> <a href="#L-29">29</a> <a href="#L-30">30</a> <a href="#L-31">31</a> <a href="#L-32">32</a> <a href="#L-33">33</a> <a href="#L-34">34</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="kn">import</span> <span class="nn">pandas</span><span class="o">,</span> <span class="nn">sys</span><span class="o">,</span> <span class="nn">subprocess</span> </span><span id="L-2"><a name="L-2"></a> </span><span id="L-3"><a name="L-3"></a><span class="k">def</span> <span class="nf">open_in_excel</span><span class="p">(</span><span class="n">df</span><span class="p">,</span> <span class="n">index</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">excel_path</span><span class="o">=</span><span class="s2">&quot;excel.exe&quot;</span><span class="p">,</span> <span class="n">tmp_path</span><span class="o">=</span><span class="s1">&#39;.&#39;</span><span class="p">):</span> </span><span id="L-4"><a name="L-4"></a> <span class="sd">&quot;&quot;&quot;Open dataframe df in excel (proposed by Ross R on </span> </span><span id="L-5"><a name="L-5"></a><span class="sd"> http://stackoverflow.com/questions/10636024/python-pandas-gui-for-viewing-a-dataframe-or-matrix)</span> </span><span id="L-6"><a name="L-6"></a> </span><span id="L-7"><a name="L-7"></a><span class="sd"> excel_path - path to your copy of excel</span> </span><span id="L-8"><a name="L-8"></a><span class="sd"> index=True - export the index of the dataframe as the first columns</span> </span><span id="L-9"><a name="L-9"></a><span class="sd"> tmp_path - directory to save the file in</span> </span><span id="L-10"><a name="L-10"></a> </span><span id="L-11"><a name="L-11"></a> </span><span id="L-12"><a name="L-12"></a><span class="sd"> This creates a temporary file name, exports the dataframe to a csv of that file name,</span> </span><span id="L-13"><a name="L-13"></a><span class="sd"> and then tells excel to open the file (in read only mode). (It uses df.to_csv instead</span> </span><span id="L-14"><a name="L-14"></a><span class="sd"> of to_excel because if you don&#39;t have excel, you still get the csv.)</span> </span><span id="L-15"><a name="L-15"></a> </span><span id="L-16"><a name="L-16"></a><span class="sd"> Note - this does NOT delete the file when you exit. </span> </span><span id="L-17"><a name="L-17"></a><span class="sd"> &quot;&quot;&quot;</span> </span><span id="L-18"><a name="L-18"></a> </span><span id="L-19"><a name="L-19"></a> <span class="n">f</span><span class="o">=</span><span class="n">tempfile</span><span class="o">.</span><span class="n">NamedTemporaryFile</span><span class="p">(</span><span class="n">delete</span><span class="o">=</span><span class="bp">False</span><span class="p">,</span> <span class="nb">dir</span><span class="o">=</span><span class="n">tmp_path</span><span class="p">,</span> <span class="n">suffix</span><span class="o">=</span><span class="s1">&#39;.csv&#39;</span><span class="p">,</span> <span class="n">prefix</span><span class="o">=</span><span class="s1">&#39;tmp_&#39;</span><span class="p">)</span> </span><span id="L-20"><a name="L-20"></a> <span class="n">tmp_name</span><span class="o">=</span><span class="n">f</span><span class="o">.</span><span class="n">name</span> </span><span id="L-21"><a name="L-21"></a> <span class="n">f</span><span class="o">.</span><span class="n">close</span><span class="p">()</span> </span><span id="L-22"><a name="L-22"></a> </span><span id="L-23"><a name="L-23"></a> <span class="n">df</span><span class="o">.</span><span class="n">to_csv</span><span class="p">(</span><span class="n">tmp_name</span><span class="p">,</span> <span class="n">index</span><span class="o">=</span><span class="n">index</span><span class="p">)</span> </span><span id="L-24"><a name="L-24"></a> <span class="n">cmd</span><span class="o">=</span><span class="p">[</span><span class="n">excel_path</span><span class="p">,</span> <span class="s1">&#39;/r&#39;</span><span class="p">,</span> <span class="s1">&#39;/e&#39;</span><span class="p">,</span> <span class="n">tmp_name</span><span class="p">]</span> </span><span id="L-25"><a name="L-25"></a> <span class="k">try</span><span class="p">:</span> </span><span id="L-26"><a name="L-26"></a> <span class="n">ret_val</span><span class="o">=</span><span class="n">subprocess</span><span class="o">.</span><span class="n">Popen</span><span class="p">(</span><span class="n">cmd</span><span class="p">)</span><span class="o">.</span><span class="n">pid</span> </span><span id="L-27"><a name="L-27"></a> <span class="k">except</span><span class="p">:</span> </span><span id="L-28"><a name="L-28"></a> <span class="k">print</span> <span class="s2">&quot;open_in_excel(): failed to open excel&quot;</span> </span><span id="L-29"><a name="L-29"></a> <span class="k">print</span> <span class="s2">&quot;filename = &quot;</span><span class="p">,</span> <span class="n">tmp_name</span> </span><span id="L-30"><a name="L-30"></a> <span class="k">print</span> <span class="s2">&quot;command line = &quot;</span><span class="p">,</span> <span class="n">cmd</span> </span><span id="L-31"><a name="L-31"></a> <span class="k">print</span> <span class="s2">&quot;Unexpected error:&quot;</span><span class="p">,</span> <span class="n">sys</span><span class="o">.</span><span class="n">exc_info</span><span class="p">()[</span><span class="mi">0</span><span class="p">]</span> </span><span id="L-32"><a name="L-32"></a> <span class="c1"># end try</span> </span><span id="L-33"><a name="L-33"></a> </span><span id="L-34"><a name="L-34"></a><span class="c1"># end def open_in_excel</span> </span></pre></div> </td></tr></table>
Python, pandas