Reading csv like file to pandas

Question

I am trying to read an Excel file into pandas, but I get the message format and extension of the file don't match.

When I try to use read_excel, I get an error message, I am therefore using read_csv.

This is where the issue is; my 'Excel like' file has empty cells on some rows, and it creates a weird df, where some field are shifted:

My code is below:

2010 = pd.read_csv(r'{0}\\file.xls'.format(path_temp),sep = 
r'\t*',encoding='iso-8859-2')

In the output, column Outcome appears in 6th (date 4) column of the data frame from row 8. Would you know of a workaround? I need to load this file automatically every 15mins, meaning I d like to avoid a manual open and save as with excel

Answer 1

Your separator is a regex. sep=r'\t*' matches any number of consecutive tabs, and so what should be blank cells get treated as a single delimiter. Try sep='\t' instead.