Dataframe loading with duplicated columns and usecols

[thenx]

I'm using pandas 0.17.1

import pandas as pd
  pd.__version__

Out:'0.17.1'

When column names are duplicated

cols = ['A', 'A', 'B']
with open('pandas.csv', 'w') as f:
  f.write('1,2,3')

we can still load dataframe

pd.read_csv('pandas.csv',
            header=None,
            names=cols,
           )

with explainable behaviour

Out:
     A    A    B
0    2    2    3

Then we might want to load some of the columns with python engine

pd.read_csv('pandas.csv',
            engine='python',
            header=None,
            names=cols,
            usecols=cols
           )

and get different but still explainable result

Out:
     A    B
0    1    3

But then we switch back to c-engine

pd.read_csv('pandas.csv',
            engine='c',
            header=None,
            names=cols,
            usecols=cols
           )

and get the following

Out:
     A    A    B
0    2    2    NaN

which is:
(a) different (which is not good in my opinion)
(b) looks like bug

[jreback]

we had an issue about this IIRC, but can't seem to locate it.

can you replace the top with some self-reproducing examples that demonstrate this? (use StringIO and in-line data)

(its actually an easy fix, just need some tests cases). The issue is that we pass a dict of the results to the DataFrame constructor.

[sxwang]

Maybe I'm missing something, but this doesn't appear to be an easy fix ...
The c parser pandas/parser.pyx first converts the usecols to a set, removing duplicates:

in cdef class TextReader, function __init__:

        # suboptimal
        if usecols is not None:
            self.has_usecols = 1
            self.usecols = set(usecols)

But then it seems that when it's reading the file, it stops after len(usecols) values are read. So in the example above, that 3rd column is never read:
In TextReader._convert_column_data:

            elif self.usecols and nused == len(self.usecols):
                # Once we've gathered all requested columns, stop. GH5766
                break

This occurs before the dict of the result is passed to the DataFrame constructor.
I'm wondering why usecols needs to be a set. Apart from that, any suggestions?

[jreback]

@sxwang yeh might be a bit more involved :)

You can try to change usecols to a list and see where that takes you

[sxwang]

Ok, I think usecols does not need to be a set, I changed it to a list, that seems to fix it for the c engine. I also updated the python engine to handle duplicated columns/usecols and now it should behave the same.