How to hide the rows index

Question

I want to write this DataFrame to an xlsx file without the index values. How can I do it?

writer=pd.ExcelWriter(r"D:\pandas.xlsx")
today=datetime.datetime.today()
header = pd.MultiIndex.from_product([[today],["name","lastname","age"]])
data=pd.DataFrame(newList, columns=header)
data.to_excel(writer)
writer.save()

result:

  2019-09-16 18:23:20.851291              
                        name  lastname age
0                        John  McBrain  22
1                     Patrick    Heszke 33
2                      Luk         Nans 21

I need:

  2019-09-16 18:23:20.851291              
                        name  lastname age
                        John  McBrain  22
                     Patrick    Heszke 33
                         Luk      Nans 21

There's an `index=False` argument in `to_excel`; is that what you need? Otherwise what about `print(df.to_string(index=False))`? — ALollz, Sep 16 '19 at 16:36
When I using index=False in to_excel , I get error (Writing to Excel with MultiIndex columns and no index ('index'=False) is not yet implemented) — Mican, Sep 16 '19 at 16:41
Well that is rather unfortunate. Just to be clear, is the goal here to write to the excel file without the index, or are you looking simply to print this result to some console or text file? — ALollz, Sep 16 '19 at 16:43
You can also do `df.reset_index(drop=True).to_excel(index=False)`? — Quang Hoang, Sep 16 '19 at 16:47
df.reset_index(drop=True).to_excel(index=False) - the same error. I need write it to excel — Mican, Sep 16 '19 at 16:55

unutbu · Answer 1 · 2019-09-17T11:56:40.007

Here is a workaround for the Exception:

NotImplementedError: Writing to Excel with MultiIndex columns and no index ('index'=False) is not yet implemented.

that arises when calling df.to_excel(writer, index=False) when df.columns is a MultiIndex.

import numpy as np
import pandas as pd

np.random.seed(2019)

def write_excel(df, path, *args, **kwargs):
    """
    Write df as an excel file to path, roughly similar to `df.to_excel` except that it handles
    `df` with MultiIndex columns and `index=False`.
    """
    writer = pd.ExcelWriter(path)

    header = pd.DataFrame(df.columns.to_list()).T
    header.to_excel(writer, header=False, *args, **kwargs)

    # Avoid the "NotImplementedError: Writing to Excel with MultiIndex columns"
    # exception by temporarily changing the columns to a single-level index
    orig_columns = df.columns
    df.columns = range(len(df.columns))
    df.to_excel(
        writer, startrow=len(header), header=False, *args, **kwargs
    )
    df.columns = orig_columns
    writer.save()

df = pd.DataFrame(np.random.randint(10, size=(5, 4)), columns=list("ABCD"))
df = df.set_index(list("AB")).unstack("B")
write_excel(df, r"/tmp/pandas.xlsx", sheet_name="Sheet1", index=False)    
print(df)

converts the DataFrame, df:

     C              D          
B    2    5    8    2    5    8
A                              
0  5.0  NaN  NaN  7.0  NaN  NaN
6  NaN  NaN  0.0  NaN  NaN  0.0
7  NaN  NaN  5.0  NaN  NaN  3.0
8  5.0  4.0  NaN  8.0  0.0  NaN

to a spreadsheet that looks like

How to hide the rows index

1 Answers1

Linked