sep=';' not shaping dataframe in Python

Question

I am importing a file that is semicolon delimited. my code:

df = pd.read_csv('bank-full.csv', sep = ';')
print(df.shape)

When I use this in Jupyter Notebooks and Spyder I get a shape output of (45211, 1). When I print my dataframe the data looks like this at this point:

<bound method NDFrame.head of       age;"job";"marital";"education";"default";"balance";"housing";"loan";"contact";"day";"month";"duration";"campaign";"pdays";"previous";"poutcome";"y"
0      58;"management";"married";"tertiary";"no";2143...

I can get the correct shape by using

 df = pd.read_csv('bank-full.csv', sep = '[;]')
print(df.shape)

or

 df = pd.read_csv('bank-full.csv', sep = '\;')
print(df.shape)

However when I do this the data seems to get pulled in as though each row is a string. The first and last column get added preceding and ending double quotations respectively, and when I attempt to strip them nothing is working to remove them so either way I am stuck with many of my columns called objects and unable to force them into integers when needed. My data comes out like this:

    "age           ""job""   ""marital""  ""education"" ""default""  \
0   "58    ""management""   ""married""   ""tertiary""      ""no""

with final column:

        ""y"""  
0       ""no"""

I have reached out to those in my class and had them send me their .csv file, restarted from scratch, tried a different UI, and even copy/pasted their line of code to read and shape the data and get nothing. I have used every resource except asking this here and am out of ideas.

Answer 1

CSVs are usually separated by commas, but sometimes the cells are separated by a different character(s). So, since I don't have access to your exact dataset, I will give you advice that should help you overall.

First, look at the CSV and assess what character(s) are separating each value, then use that as the value in "sep" during your pd.read_csv() call.

Then, whatever columns you want to convert to numeric, you can use pd.to_numeric() to convert the data type. This may present problems if any of the values in the column cannot be converted to numeric, and you will then need to do additional data cleaning.

Below is an example of how to do this to a particular column that I am calling "col":

import pandas as pd

df = pd.read_csv('bank-full.csv', sep = '[;]')
df[col] = pd.to_numeric(df[col])

Let me know if you have further questions, or better yet, share the data with me if you can't get this to work for you.