<p dir="auto">Today I discovered a strange behavior: When I am writing a DataFrame with <code class="notranslate">.to_excel()</code>, it cuts columns. Compared with the same DataFrame with <code class="notranslate">.to_csv()</code> or <code class="notranslate">.head()</code>, you can see the difference, that the last 8 columns are missing.</p>
<p dir="auto">You can reproduce this by downloading <code class="notranslate">Features.pkl</code> from <a href="https://dl.dropboxusercontent.com/u/16985007/Features.pkl" rel="nofollow">here</a> and then:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import pandas as pd
df = pd.read_pickle('Features.pkl')
df.head() # see the last 8 columns!
df.to_excel('Features.xlsx', index=False, header=False)
# see the Excel, you do not have these last 8 columns
# in a .to_csv() you have them"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">pandas</span> <span class="pl-k">as</span> <span class="pl-s1">pd</span>
<span class="pl-s1">df</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-en">read_pickle</span>(<span class="pl-s">'Features.pkl'</span>)
<span class="pl-s1">df</span>.<span class="pl-en">head</span>() <span class="pl-c"># see the last 8 columns!</span>
<span class="pl-s1">df</span>.<span class="pl-en">to_excel</span>(<span class="pl-s">'Features.xlsx'</span>, <span class="pl-s1">index</span><span class="pl-c1">=</span><span class="pl-c1">False</span>, <span class="pl-s1">header</span><span class="pl-c1">=</span><span class="pl-c1">False</span>)
<span class="pl-c"># see the Excel, you do not have these last 8 columns</span>
<span class="pl-c"># in a .to_csv() you have them</span></pre></div>
<p dir="auto">Funny part: If you <code class="notranslate">df.ix[:,-71:].to_excel('Features.xlsx', index=False, header=False)</code> you have one of the missing columns. If you do <code class="notranslate">df.ix[:,-70:].to_excel('Features.xlsx', index=False, header=False)</code> you have two and so on...</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="INSTALLED VERSIONS
------------------
commit: None
python: 2.7.10.final.0
python-bits: 64
OS: Darwin
OS-release: 14.5.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: de_DE.UTF-8

pandas: 0.16.2
nose: 1.3.7
Cython: 0.22.1
numpy: 1.9.2
scipy: 0.16.0
statsmodels: 0.6.1
IPython: 3.2.0
sphinx: 1.3.1
patsy: 0.3.0
dateutil: 2.4.2
pytz: 2015.4
bottleneck: 1.0.0
tables: 3.2.0
numexpr: 2.4.3
matplotlib: 1.4.3
openpyxl: 1.8.6
xlrd: 0.9.3
xlwt: 1.0.0
xlsxwriter: 0.7.3
lxml: 3.4.4
bs4: 4.3.2
html5lib: None
httplib2: 0.9
apiclient: None
sqlalchemy: 1.0.5
pymysql: None
psycopg2: None"><pre class="notranslate"><span class="pl-v">INSTALLED</span> <span class="pl-v">VERSIONS</span>
<span class="pl-c1">-</span><span class="pl-c1">-</span><span class="pl-c1">-</span><span class="pl-c1">-</span><span class="pl-c1">-</span><span class="pl-c1">-</span><span class="pl-c1">-</span><span class="pl-c1">-</span><span class="pl-c1">-</span><span class="pl-c1">-</span><span class="pl-c1">-</span><span class="pl-c1">-</span><span class="pl-c1">-</span><span class="pl-c1">-</span><span class="pl-c1">-</span><span class="pl-c1">-</span><span class="pl-c1">-</span><span class="pl-c1">-</span>
<span class="pl-s1">commit</span>: <span class="pl-c1">None</span>
<span class="pl-s1">python</span>: <span class="pl-c1">2.7</span>.<span class="pl-c1">10.</span><span class="pl-s1">final</span>.<span class="pl-c1">0</span>
<span class="pl-s1">python</span><span class="pl-c1">-</span><span class="pl-s1">bits</span>: <span class="pl-c1">64</span>
<span class="pl-v">OS</span>: <span class="pl-v">Darwin</span>
<span class="pl-v">OS</span><span class="pl-c1">-</span><span class="pl-s1">release</span>: <span class="pl-c1">14.5</span><span class="pl-c1">.0</span>
<span class="pl-s1">machine</span>: <span class="pl-s1">x86_64</span>
<span class="pl-s1">processor</span>: <span class="pl-s1">i386</span>
<span class="pl-s1">byteorder</span>: <span class="pl-s1">little</span>
<span class="pl-v">LC_ALL</span>: <span class="pl-c1">None</span>
<span class="pl-v">LANG</span>: <span class="pl-s1">de_DE</span>.<span class="pl-v">UTF</span><span class="pl-c1">-</span><span class="pl-c1">8</span>

<span class="pl-s1">pandas</span>: <span class="pl-c1">0.16</span>.<span class="pl-c1">2</span>
<span class="pl-s1">nose</span>: <span class="pl-c1">1.3</span>.<span class="pl-c1">7</span>
<span class="pl-v">Cython</span>: <span class="pl-c1">0.22</span>.<span class="pl-c1">1</span>
<span class="pl-s1">numpy</span>: <span class="pl-c1">1.9</span>.<span class="pl-c1">2</span>
<span class="pl-s1">scipy</span>: <span class="pl-c1">0.16</span>.<span class="pl-c1">0</span>
<span class="pl-s1">statsmodels</span>: <span class="pl-c1">0.6</span>.<span class="pl-c1">1</span>
<span class="pl-v">IPython</span>: <span class="pl-c1">3.2</span>.<span class="pl-c1">0</span>
<span class="pl-s1">sphinx</span>: <span class="pl-c1">1.3</span>.<span class="pl-c1">1</span>
<span class="pl-s1">patsy</span>: <span class="pl-c1">0.3</span>.<span class="pl-c1">0</span>
<span class="pl-s1">dateutil</span>: <span class="pl-c1">2.4</span>.<span class="pl-c1">2</span>
<span class="pl-s1">pytz</span>: <span class="pl-c1">2015.4</span>
<span class="pl-s1">bottleneck</span>: <span class="pl-c1">1.0</span>.<span class="pl-c1">0</span>
<span class="pl-s1">tables</span>: <span class="pl-c1">3.2</span>.<span class="pl-c1">0</span>
<span class="pl-s1">numexpr</span>: <span class="pl-c1">2.4</span>.<span class="pl-c1">3</span>
<span class="pl-s1">matplotlib</span>: <span class="pl-c1">1.4</span>.<span class="pl-c1">3</span>
<span class="pl-s1">openpyxl</span>: <span class="pl-c1">1.8</span>.<span class="pl-c1">6</span>
<span class="pl-s1">xlrd</span>: <span class="pl-c1">0.9</span>.<span class="pl-c1">3</span>
<span class="pl-s1">xlwt</span>: <span class="pl-c1">1.0</span>.<span class="pl-c1">0</span>
<span class="pl-s1">xlsxwriter</span>: <span class="pl-c1">0.7</span>.<span class="pl-c1">3</span>
<span class="pl-s1">lxml</span>: <span class="pl-c1">3.4</span>.<span class="pl-c1">4</span>
<span class="pl-s1">bs4</span>: <span class="pl-c1">4.3</span>.<span class="pl-c1">2</span>
<span class="pl-s1">html5lib</span>: <span class="pl-c1">None</span>
<span class="pl-s1">httplib2</span>: <span class="pl-c1">0.9</span>
<span class="pl-s1">apiclient</span>: <span class="pl-c1">None</span>
<span class="pl-s1">sqlalchemy</span>: <span class="pl-c1">1.0</span>.<span class="pl-c1">5</span>
<span class="pl-s1">pymysql</span>: <span class="pl-c1">None</span>
<span class="pl-s1">psycopg2</span>: <span class="pl-c1">None</span></pre></div>
<h4 dir="auto">Code Sample, a copy-pastable example if possible</h4>
<p dir="auto">I often find my self doing</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="In [2]: df = pd.Series(index=pd.MultiIndex.from_product([['A', 'B'], ['a', 'b']]))

In [3]: df.index.get_level_values(0).unique()
Out[3]: Index(['A', 'B'], dtype='object')"><pre class="notranslate"><span class="pl-v">In</span> [<span class="pl-c1">2</span>]: <span class="pl-s1">df</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">Series</span>(<span class="pl-s1">index</span><span class="pl-c1">=</span><span class="pl-s1">pd</span>.<span class="pl-v">MultiIndex</span>.<span class="pl-en">from_product</span>([[<span class="pl-s">'A'</span>, <span class="pl-s">'B'</span>], [<span class="pl-s">'a'</span>, <span class="pl-s">'b'</span>]]))

<span class="pl-v">In</span> [<span class="pl-c1">3</span>]: <span class="pl-s1">df</span>.<span class="pl-s1">index</span>.<span class="pl-en">get_level_values</span>(<span class="pl-c1">0</span>).<span class="pl-en">unique</span>()
<span class="pl-v">Out</span>[<span class="pl-c1">3</span>]: <span class="pl-v">Index</span>([<span class="pl-s">'A'</span>, <span class="pl-s">'B'</span>], <span class="pl-s1">dtype</span><span class="pl-c1">=</span><span class="pl-s">'object'</span>)</pre></div>
<h4 dir="auto">Problem description</h4>
<p dir="auto">The above is very inefficient, because first a <code class="notranslate">Series</code> is built which includes a copy of the entire level (possibly using way more memory than the index itself), and only then duplicates are stripped. <a href="https://stackoverflow.com/questions/24495695/pandas-get-unique-multiindex-level-values-by-label#comment44836216_24496435" rel="nofollow">Other people on SO</a> have faced the same problem, and this is also blocking a fix I wrote for <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="264495108" data-permission-text="Title is private" data-url="https://github.com/pandas-dev/pandas/issues/17845" data-hovercard-type="issue" data-hovercard-url="/pandas-dev/pandas/issues/17845/hovercard" href="https://github.com/pandas-dev/pandas/issues/17845">#17845</a>.</p>
<p dir="auto">I'm pushing a simple PR in seconds.</p>
<h4 dir="auto">Expected Output</h4>
<p dir="auto">Same as above, but in an efficient way.</p>
<h4 dir="auto">Output of <code class="notranslate">pd.show_versions()</code></h4>
<details>
<h2 dir="auto">INSTALLED VERSIONS</h2>
<p dir="auto">commit: None<br>
python: 3.5.3.final.0<br>
python-bits: 64<br>
OS: Linux<br>
OS-release: 4.9.0-3-amd64<br>
machine: x86_64<br>
processor:<br>
byteorder: little<br>
LC_ALL: None<br>
LANG: it_IT.UTF-8<br>
LOCALE: it_IT.UTF-8</p>
<p dir="auto">pandas: 0.21.0rc1+19.gb15d92d14<br>
pytest: 3.0.6<br>
pip: 9.0.1<br>
setuptools: None<br>
Cython: 0.25.2<br>
numpy: 1.12.1<br>
scipy: 0.19.0<br>
pyarrow: None<br>
xarray: None<br>
IPython: 5.1.0.dev<br>
sphinx: 1.5.6<br>
patsy: 0.4.1<br>
dateutil: 2.6.0<br>
pytz: 2017.2<br>
blosc: None<br>
bottleneck: 1.2.1<br>
tables: 3.3.0<br>
numexpr: 2.6.1<br>
feather: 0.3.1<br>
matplotlib: 2.0.0<br>
openpyxl: None<br>
xlrd: 1.0.0<br>
xlwt: 1.1.2<br>
xlsxwriter: 0.9.6<br>
lxml: None<br>
bs4: 4.5.3<br>
html5lib: 0.999999999<br>
sqlalchemy: 1.0.15<br>
pymysql: None<br>
psycopg2: None<br>
jinja2: 2.9.6<br>
s3fs: None<br>
fastparquet: None<br>
pandas_gbq: None<br>
pandas_datareader: 0.2.1</p>
</details>