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>From your sample you have provided, it is clear that the problem is with the data set and pandas is working correctly.</p>

<p>Only the first row is separated correctly, the second row is all in one column; as a single string (pay attention to the <code>"</code>). If I replace the <code>,</code> with <code>|</code>, your problem becomes a bit clearer:</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>Now you have to manually split the second row in order to create the correct data set.</p>

<pre><code>&gt;&gt;&gt; with open('sample2.txt') as f:
...    headers = next(f).split(',')
...    rows = [i.split(',') for i in f]
...
&gt;&gt;&gt; rows = [list(map(str.strip, list(map(lambda x: x.replace('"', ''), i)))) for i in rows]
&gt;&gt;&gt; pd.DataFrame(rows, columns=headers)
                  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>

python csv pandas