Issue when exporting dataframe to csv

Question

I'm working on a mechanical engineering project. For the following code, the user enters the number of cylinders that their compressor has. A dataframe is then created with the correct number of columns and is exported to Excel as a CSV file.

The outputted dataframe looks exactly like I want it to as shown in the first link, but when opened in Excel it looks like the image in the second link: 1.my dataframe

2.Excel Table

Why is my dataframe not exporting properly to Excel and what can I do to get the same dataframe in Excel?


import pandas as pd 
CylinderNo=int(input('Enter CylinderNo: '))  
new_number=CylinderNo*3
list1=[]
for i in range(1,CylinderNo+1):
    for j in range(0,3):
        Cylinder_name=str('CylinderNo ')+str(i)
        list1.append(Cylinder_name)
    
df = pd.DataFrame(list1,columns =['Kurbel/Zylinder'])

list2=['Triebwerk', 'Packung','Ventile']*CylinderNo
Bauteil = {'Bauteil':  list2}
df2 = pd.DataFrame (Bauteil, columns = ['Bauteil'])
new=pd.concat([df, df2], axis=1)

list3=['Nan','Nan','Nan']*CylinderNo
Bewertung={'Bewertung':  list3}
df3 = pd.DataFrame (Bewertung, columns = ['Bewertung'])
new2=pd.concat([new, df3], axis=1)

Empfehlung={'Empfehlung':  list3}
df4 = pd.DataFrame (Empfehlung, columns = ['Empfehlung'])
new3=pd.concat([new2, df4], axis=1)

new3.set_index('Kurbel/Zylinder')
new3 = new3.set_index('Kurbel/Zylinder', append=True).swaplevel(0,1)

#export dataframe to csv
new3.to_csv('new3.csv')

Parfait · Accepted Answer · 2020-09-25 14:35:39Z

To be clear, a comma-separated values (CSV) file is not an Excel format type or table. It is a delimited text file that Excel like other applications can open.

What you are comparing is simply presentation. Both data frames are exactly the same. For multindex data frames, Pandas print output does not repeat index values for readability on the console or IDE like Jupyter. But such values are not removed from underlying data frame only its presentation. If you re-order indexes, you will see this presentation changes. The full complete data frame is what is exported to CSV. And ideally for data integrity, you want the full data set exported with to_csv to be import-able back into Pandas with read_csv (which can set indexes) or other languages and applications.

Essentially, CSV is an industry format to store and transfer data. Consider using Excel spreadsheets, HTML markdown, or other reporting formats for your presentation needs. Therefore, to_csv may not be the best method. You can try to build text file manually with Python i/o write methods, with open('new.csv', 'w') as f, but will be an extensive workaround See also @Jeff's answer here but do note the latter part of solution does remove data.

Collectives™ on Stack Overflow

Issue when exporting dataframe to csv

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related