list.to_csv in pandas dataframe not working correctly due to a comma in a variable-CodePudding

im using list.to_csv to make a csv file from panda.dataframe. everything works great except if theres a comma in variables, it the one after comma would go to next column. is there any way to fix this?

datalist = {'Owner/Occupant': ["andrew"],
            'Job Address': ["test, newyork"],
            'Buyer': ["andrew"],
            'Phone': ["12312341234"]}

data_list= pd.DataFrame(datalist)
data_list.to_csv('test.csv', sep = '\t')

and if i open test.csv it would look like this

CodePudding user response：

This is most likely due to the csv reader (Excel, it looks like). Excel is going to make assumptions about delimiters.

Try opening the .csv file in a text editor (notepad), copy the whole thing, and paste it into Excel. Then use Excel's text-to-columns and be clear that the delimiter is tab instead of comma.

CodePudding user response：

I think your best bet would be to use sep = '|' then when you open your CSV you should be able to select the option of using the | as a delimiter. Excel > Data Tab > From Text/CSV > Select the created file > select | as delimiter

CodePudding user response：

when you're saving it with \t (tab) as a separator, then shouldn't you read it the same way i.e. as tab separator?

Alternately, you can try to use "," as a separator and using escapechar attribute of to_csv when writing to csv