BUG: "cannot reindex from duplicate axis" thrown using unique indexes, duplicated column names and a specific numpy array values

[igorluppi]

Code Sample

import pandas 
import numpy as np

a = np.array([[1,2],[3,4]]) 

# DO NOT WORKS
b = np.array([[0.5,6],[7,8]])  
# b = np.array([[.5,6],[7,8]])  # The same problem

# This one works fine:
# b = np.array([[5,6],[7,8]]) 

dfA = pandas.DataFrame(a)
# This works fine EVEN using .5, because the columns name is different
# dfA = pandas.DataFrame(a, columns=['a','b'])
dfB = pandas.DataFrame(b)

df_new = pandas.concat([dfA, dfB], axis = 1)

print(df_new[df_new > 5])

Problem description

It has a bug that combines numpy specific values and duplicated DataFrame column names when it's used a select operation, such as df[df > 5]. A exception is thrown saying "cannot reindex from duplicate axis", however It should not be, because:

• The DataFrame has no duplicated indexes ( df.index.is_unique is True)
• The DataFrame has duplicated column names, but should not be a problem when we apply the selection operation, such as df_new[df_new > 5]
• The DataFrame uses float or int numpy values, so it should not change the behavior of the code

However the values in the numpy array DO changes the behavior of the DataFrame selection, if the DataFrame has duplicated column names.

Expected Output

    0   1    0  1
0 NaN NaN  NaN  6
1 NaN NaN  7.0  8

Current Output

~/.local/lib/python3.6/site-packages/pandas/core/indexes/base.py in _can_reindex(self, indexer)
   3097         # trying to reindex on an axis with duplicates
   3098         if not self.is_unique and len(indexer):
-> 3099             raise ValueError("cannot reindex from a duplicate axis")
   3100 
   3101     def reindex(self, target, method=None, level=None, limit=None, tolerance=None):

ValueError: cannot reindex from a duplicate axis

Output of pd.show_versions()

INSTALLED VERSIONS

commit : None
python : 3.6.9.final.0
python-bits : 64
OS : Linux
OS-release : 5.3.0-28-generic
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : pt_BR.UTF-8

pandas : 1.0.1
numpy : 1.18.1
pytz : 2019.3
dateutil : 2.8.1
pip : 20.0.2
setuptools : 45.2.0
Cython : None
pytest : None
hypothesis : None
sphinx : 2.3.1
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 2.11.1
IPython : 7.12.0
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : 3.1.3
numexpr : 2.7.1
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
pytest : None
pyxlsb : None
s3fs : None
scipy : 1.4.1
sqlalchemy : None
tables : 3.6.1
tabulate : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None
numba : None

[igorluppi]

Moreover, doing this:

In [177]:  new_df = df.reset_index(drop=True) 
In [178]:  new_df[new_df > 10]

~/.local/lib/python3.6/site-packages/pandas/core/indexes/base.py in _can_reindex(self, indexer)
   3097         # trying to reindex on an axis with duplicates
   3098         if not self.is_unique and len(indexer):
-> 3099             raise ValueError("cannot reindex from a duplicate axis")
   3100 
   3101     def reindex(self, target, method=None, level=None, limit=None, tolerance=None):

ValueError: cannot reindex from a duplicate axis

So, it's 100% sure that we have no duplicates here, so what is going on?

[MarcoGorelli]

Thanks @igorluppi

I just tried

df = pd.DataFrame(np.random.randn(150001, 792))
df[df>10]                                                                                                                                                         

and got no error - could you give us some more details about your dataframe? Do you still get the error if you only consider its head, or if you only use (say) its first 5 columns?

[igorluppi]

I have many dataframes, and a put all of them in a single one:

let df_items be a list of dataframes.
I got this error using:
df_final = pandas.concat(df_items, axis = 1)

However I verified that
df_final = reduce(lambda x, y: pandas.merge(x, y, left_index=True, right_index=True, how='outer'), df_items)

Works fine, I got the same result DF and when I apply df_final[df_final>10] it works. But this method requires a long process to be done, concat is faster than it (at least 10 times faster).

Thanks for https://stackoverflow.com/questions/45885043/pandas-concat-cannot-reindex-from-a-duplicate-axis?rq=1 about this possible solution. But why the error happens?

[MarcoGorelli]

Applying df[df>10] I got "cannot reindex from duplicate axis",

I got this error using:
df_final = pandas.concat(df_items, axis = 1)

Sorry, I'm a bit confused, which command gave you the error - pd.concat or df[df>10]?

[igorluppi]

pd.concat gave me the df_final, this df_final got that error when I use df_final[df_final>10]
The interesting part is, when I use the reduce method and got a df_final2, this one works, in another words df_final2[df_final2>10] works fine.

Moreover,

In [14]: df_final.equals(df_final2)                                                                                                                                                                            
Out[14]: False

But I didnt find where the difference is

[igorluppi]

@MarcoGorelli I found the why the problem is happening but this implies in another problem regarding the exception I got. Give me a second

[igorluppi]

@MarcoGorelli "cannot reindex from duplicate axis" should be broken in two messages:
both "cannot reindex from duplicate index" and "cannot reindex from duplicate columns". I will explain why.

Why is that? Because all the messages and solutions I was looking for told me to took at the indexes, but in my case I found duplicated columns.

But why the second case worked? reduce(lambda x, y: pandas.merge(x, y, left_index=True, right_index=True, how='outer'), df_items)
In this case, when it finds a duplicated column, automatically it appended a string "_x" to the duplicated, it became "duplicated_column_x" It's not the case for concat, it keeps the duplicated column name "duplicated_column".

My sugestion

Please change the exception, to be specific that the problem belongs to the column (or index). Just saying duplicate axis was a little bit confused to find the solution

[MarcoGorelli]

Thanks @igorluppi

tbh I still can't reproduce the error:

df = pd.DataFrame([0, 1], columns=['a'])
new_df = pd.concat([df, df], axis=1)
new_df[new_df>0]  # works

could you try coming up with a minimal reproducible example?

[igorluppi]

Ok, I will create a simple example

[igorluppi]

@MarcoGorelli

import pandas 
import numpy as np

a = np.array([[1,2],[3,4]]) 

# DO NOT WORKS
b = np.array([[0.5,6],[7,8]]) 
# OR
# b = np.array([[.5,6],[7,8]])

# This one works fine:
# b = np.array([[5,6],[7,8]]) 

dfA = pandas.DataFrame(a)
# This works fine EVEN using .5, because the columns name is different
# dfA = pandas.DataFrame(a, columns=['a','b'])
dfB = pandas.DataFrame(b)

df_new = pandas.concat([dfA, dfB], axis = 1)

df_new[df_new>3]

~/.local/lib/python3.6/site-packages/pandas/core/indexes/base.py in _can_reindex(self, indexer)
   3097         # trying to reindex on an axis with duplicates
   3098         if not self.is_unique and len(indexer):
-> 3099             raise ValueError("cannot reindex from a duplicate axis")
   3100 
   3101     def reindex(self, target, method=None, level=None, limit=None, tolerance=None):

ValueError: cannot reindex from a duplicate axis

Basically, using .5 or 0.5 in numpy there breaks the dataframe operation. This might be a problem with pandas + numpy .

The interesting part is: Numpy float values just break the code if we have duplication on columns name.

[MarcoGorelli]

@igorluppi great, thanks! Could you edit this example into the original post?

[igorluppi]

@MarcoGorelli

For sure, it's done my friend!

[igorluppi]

@MarcoGorelli is it a bug ? Anything new ?

[MarcoGorelli]

cc @jorisvandenbossche