Pandas dataframe.read_csv ,quotechar doesnot work

user_11492343 0
Viewed 78

I am not getting the output as expected.

I am trying to convert CSV to dataframe, But it is not working:

sales=pd.read_csv('Downloads/item.csv',sep=',',delimeter='"',error_bad_lines=False,quotechar='"')

This is my CSV file sample:

"account_number,name,item_code,category,quantity,unit price,net_price,date "

"093356,Waters-Walker,AS-93055,Shirt,5,82.68,413.40,2013-11-17 20:41:11"

"659366,Waelchi-Fahey,AS-93055,Shirt,18,99.64,1793.52,2014-01-03 08:14:27"

"563905,""Kerluke, Reilly and Bechtelar"",AS-93055,Shirt,17,52.82,897.94,2013-12-04 02:07:05"

"995267,Cole-Eichmann,GS-86623,Shoes,18,15.28,275.04,2014-04-09 16:15:03"

"524021,Hegmann and Sons,LL-46261,Shoes,7,78.78,551.46,2014-06-18 19:25:10"

"929400,""Senger, Upton and Breitenberg"",LW-86841,Shoes,17,38.19,649.23,2014-02-10 05:55:56"

Please take a look at the bold characters in the CSV files they are enclosed with ""

pandas dataframe python-3.x
1 Answers

Here is my proposal:

df = pd.read_csv('file.csv')
col_name = 'account_number,name,item_code,category,quantity,unit price,net_price,date'
z = pd.concat([df[col_name].str.split(r'(,(?=\S)|:)', expand=True)], axis=1)
z['date'] = z[14]+z[15]+z[16]+z[17]+z[18]
z = z.drop(columns=[1,3,5,7,9,11,13, 14,15,16,17,18])
z.columns = col_name.split(',')

Crucial is this regex r'(,(?=\S)|:)' - comma but not followed by space but I don't know why it also split on :. If you can fix it then you don't have manually concat date.

Output:

 account_number  ...                 date
0         093356  ...  2013-11-17 20:41:11
1         659366  ...  2014-01-03 08:14:27
2         563905  ...  2013-12-04 02:07:05
3         995267  ...  2014-04-09 16:15:03
4         524021  ...  2014-06-18 19:25:10
5         929400  ...  2014-02-10 05:55:56
user_73403170
Related