CODEWITHSUNDEEP
pythonexcelpandasdataframeipython
Deepak M
Deepak M

Reputation: 1224

Not getting back the column names after reading into an xlsx file

Hello I have xlsx files and merged them into one dataframe by using pandas. It worked but instead of getting back the column names that I had in the xlsx file I got numbers as columns instead and the column titles became a row: Like this:

 Output:      1           2           3
            COLTITLE1    COLTITLE2     COLTITLE3

When they should be like this:

Output: COLTITLE1     COLTITLE2      COLTITLE3

The column titles are not column titles but rather they have become a row. How can I get back the rightful column names that I had within the xlsx file. Just for clarity all the column names are the same within both the xlsx files. Help would be appreciated heres my code below:

# import modules
from IPython.display import display
import pandas as pd
import numpy as np
pd.set_option("display.max_rows", 999)
pd.set_option('max_colwidth',100)
%matplotlib inline

# filenames
file_names = ["data/OrderReport.xlsx", "data/OrderReport2.xlsx"]

# read them in
excels = [pd.ExcelFile(name) for name in file_names]

# turn them into dataframes
frames = [x.parse(x.sheet_names[0], header=None,index_col=None) for x in excels]

# concatenate them
atlantic_data = pd.concat(frames)

# write it out
combined.to_excel("c.xlsx", header=False, index=False)

Upvotes: 0

Views: 1088

Answers (1)

J. Spark
J. Spark

Reputation: 128

I hope I understood your question correctly. You just need to get rid of the index_col=None and it will return the column name as usual:

frames = [x.parse(x.sheet_names[0], header=None) for x in excels]

If you add index_col=None pandas will treat your column name as 1 row of data rather than a column for the dataframe.

Upvotes: 2

Related Questions