How to separate a CSV file when there are "" lines?
<p>When I am reading in a CSV file that looks like this:</p>

<pre><code>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
</code></pre>

<p>using pandas, as follows:</p>

<pre><code>duration_route1 =  pd.read_csv(file_name, sep = ',')
</code></pre>

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

<p><a href="http://i.stack.imgur.com/jO08M.png" rel="nofollow"><img src="http://i.stack.imgur.com/jO08M.png" alt="enter image description here"></a></p>

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

<p>You need <code>quoting=csv.QUOTE_NONE</code> because there are <code>quoting</code> in <code>file</code>:</p>

<pre><code>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 "
</code></pre>



<pre><code>#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
</code></pre>

python csv pandas