Writing pandas data frame to csv but no space between columns- sep'\t' argument is ignored in Python 3

user_9111050 0
Viewed 1384

I have a problem and found many related questions asked here and read them all, but still can`t solve it. So far I didn't get any answer.

I have two files one is .csv and the other is .xlsx. They have a different number of rows and columns. I would like to merge these two according to filenames. Very simplified the two files look like as follows;

The csv file;

enter image description here

the excel file;

enter image description here

First i converted them to panda data frame;

import pandas as pd
import csv,xlrd

df1 = pd.read_csv('mycsv.csv')
df2=pd.read_excel(myexcel.xlsx', sheetname=0)

To merge the two files on the same column I remove the white space in column names in df2 using the first line below and, then I merge them and print the merged data frame in csv file.

df2.columns=df2.columns.str.replace(' ', '')
df=pd.merge(df1, df2, on="filename")
df.to_csv('myfolder \\merged_file.csv', sep="\t ")

When I check my folder, I see merged_file.csv exists but when I opened it there is no space between columns and values. I want to see nice normal csv or excel look, like my example files above. Just to make sure I tried everything, I also converted the Excel file to a csv file and then merged two csv but still merged data is written without spaces. Again, the above files are very simplified, but my real merged data look like this;

enter image description here

pandas csv python-3.x
1 Answers

Finally, figured it out. I am putting the answer here just in case if anyone else also manages the same mistake as me. Just remove the sep="\t" and use below line instead;

df.to_csv('myfolder \\merged_file.csv')

Just realized the two csv files were comma separated and using tab delimiter for merge didn`t work.

user_91110500
Related