title: Reading malformed 'csv' file with pandas
body: <p>I have a malformed "csv" file:</p>
  
  <pre><code>txt = """NAME;a;b;c
  ATTR1;1;2;3
  ATTR2;1;2;3;;;
  ATTR3;1;2;3;
  ATTR4;1;2;3"""
  </code></pre>
  
  <p>I there a way to use <code>pandas</code> <code>pd.read_*</code> toolbox to get the following <code>pd.DataFrame</code>:</p>
  
  <pre><code>|---+-------+---+---+---|
  |   | 0     | 1 | 2 | 3 |
  |---+-------+---+---+---|
  | 0 | NAME  | a | b | c |
  | 1 | ATTR1 | 1 | 2 | 3 |
  | 2 | ATTR2 | 1 | 2 | 3 |
  | 3 | ATTR3 | 1 | 2 | 3 |
  | 4 | ATTR4 | 1 | 2 | 3 |
  |---+-------+---+---+---|
  </code></pre>
  
  <p>?</p>
  
  <p>P.S. I know how to do it with <code>import csv</code> </p>
  
  <p>Thank you for ideas and BR,
  Lex</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>This was a toy example from real file (which I again had to modify) ...</p>
  
  <pre><code>SRC = 'https://dl.dropboxusercontent.com/u/40513206/test.csv'
  NA_VALUES = ['', '#N/A N/A', '#N/A Field Not Applicable', '#N/A Invalid Field',
           '#N/A Invalid Security', '#N/AN/A', '#N/A Limit', '#####', '#DIV/0!', 
           '#N/A', '#NAME?', '#NULL!', '#NUM!', '#REF!', '#VALUE!']
  CSV_ENCODING = 'WINDOWS-1252'
  S_ROWS = 6
  NR_ROWS = 60
  NR_COLS = 52 # correct nr. of columns, but not always known
  
  dat_m = pd.read_csv(SRC, sep = ';', header = None, index_col = None, skiprows = S_ROWS, 
                  nrows = NR_ROWS, encoding = CSV_ENCODING, na_values = NA_VALUES, names = range(NR_COLS))
  </code></pre>
  
  <p>Seems that if we use <code>names</code> parameter then <code>NR_COLS</code> must be <code>&gt;=</code> actual nr. of columns in first row, if not so, then <code>Index</code> or <code>MultiIndex</code> is formed (based on actual columns), for example if <code>NR_COLS = 50</code> then index has 2 levels, if <code>NR_COLS = 49</code> then 3 levels etc.</p>
  
  <p>All this is a result when I save <code>Excel</code> to <code>csv</code>, it seems to add <code>sep = ';'</code> to some rows and for some other reason I can not use <code>xls</code> (read) files directly.</p>
  
  <p>So I will use large <code>NR_COLS</code> value or continue with <code>csv</code> library.</p>
  
  <p>Thank you! </p>