We want to be able to load the following csv file which quotation marks and additional commas:
eg.
"","",""BSF" code - Intermittant, see notes",""
should result in
0 1 2 3
0 , , "BSF" code - Intermittant, see notes,
Let's fist make our data:
with open("file.csv", 'w') as f:
f.write("\"0\",\"1\",\"2\",\"3\"\n\"\",\"\",\"\"BSF\" code - Intermittant, see notes\",\"\"")
!cat file.csv
"0","1","2","3" "","",""BSF" code - Intermittant, see notes",""
import pandas as pd
data = pd.read_csv("file.csv",sep=r'(?<="),(?=")',quotechar='"', engine = 'python')
print("Before stripping:\n",data)
data = data.applymap(lambda s:s[1:-1])
print("\nAfter stripping:\n",data)
Before stripping: "0" "1" "2" "3" 0 "" "" ""BSF" code - Intermittant, see notes" "" After stripping: "0" "1" "2" "3" 0 "BSF" code - Intermittant, see notes
This seems to work pretty well.
Now I think the answer lies in considering ","
to be the delimiter