Koen Koen - 2 months ago 11
Python Question

How to separate a CSV file when there are "" lines?

When I am reading in a CSV file that looks like this:

To, ,New York ,Norfolk ,Charleston ,Savannah

Le Havre (Fri), ,15 ,18 ,22 ,24

Rotterdam (Sun) ,"",13 ,16 ,20 ,22

Hamburg (Thu) ,"",11 ,14 ,18 ,20

Southampton (Fri) , "" ,8 ,11 ,15 ,17


using pandas, as follows:

duration_route1 = pd.read_csv(file_name, sep = ',')


I get the following result (I use Sublime Text to run my Python code):

enter image description here

You see that when there is a
""
, it doesn't separate the string. Why does it not do this?

Answer

You need quoting=csv.QUOTE_NONE because there are quoting in file:

df = pd.read_csv('TAT_AX1_westbound_style3.csv', quoting=csv.QUOTE_NONE)
print (df)
                    To          New York   Norfolk   Charleston  Savannah 
0       Le Havre (Fri)                 15        18           22       24 
1    "Rotterdam (Sun)     """"         13        16           20      22 "
2      "Hamburg (Thu)     """"         11        14           18      20 "
3  "Southampton (Fri)    """"           8        11           15      17 "
#remove first column 
df = df.drop(df.columns[0], axis=1)
#remove all " values to empty string, convert to int
df = df.replace({'"':''}, regex=True).astype(int)
print (df)
                     New York   Norfolk   Charleston   Savannah 
To                                                              
Le Havre (Fri)              15        18           22         24
"Rotterdam (Sun)            13        16           20         22
"Hamburg (Thu)              11        14           18         20
"Southampton (Fri)           8        11           15         17     15       17
Comments