problems with reindexing dataframes: Reindexing only valid with uniquely valued Index objects

dataframe, pandas, reindex

Solution

You could groupby the index and take the first entry (see docs):

df.groupby(level=0).first()

Example:

In [1]: df = pd.DataFrame([[1], [2]], index=[1, 1])

In [2]: df
Out[2]: 
   0
1  1
1  2

In [3]: df.groupby(level=0).first()
Out[3]: 
   0
1  1

Problem

I am having a real strange behaviour when trying to reindex a dataframe in pandas. My version of Pandas is 0.10.0 and I use Python 2.7. Basically, when I load a dataframe: ``` eurusd = pd.DataFrame.load('EUR_USD_30Min.df').drop_duplicates().dropna() eurusd <class 'pandas.core.frame.DataFrame'> DatetimeIndex: 119710 entries, 2003-02-02 17:30:00 to 2012-12-28 17:00:00 Data columns: open 119710 non-null values high 119710 non-null values low 119710 non-null values close 119710 non-null values dtypes: float64(4) ``` and then I try to reindex inside a larger date range: ``` newindex = pd.DateRange(datetime.datetime(2002,1,1), datetime.datetime(2012,12,31), offset=pd.datetools.Minute(30)) newindex <class 'pandas.tseries.index.DatetimeIndex'> [2002-01-01 00:00:00, ..., 2012-12-31 00:00:00] Length: 192817, Freq: 30T, Timezone: None ``` I get strange behaviour when trying to reindex the dataframe. If I reindex one larger part of the dataset I get this error: ``` eurusd[29558:29560].reindex(index=newindex) Exception: Reindexing only valid with uniquely valued Index objects ``` But, if I do the same for two subsets of the data above, I don't get the error: Here's the first subset, with no problems, ``` eurusd[29558:29559].reindex(index=newindex) <class 'pandas.core.frame.DataFrame'> DatetimeIndex: 192817 entries, 2002-01-01 00:00:00 to 2012-12-31 00:00:00 Freq: 30T Data columns: open 1 non-null values high 1 non-null values low 1 non-null values close 1 non-null values dtypes: float64(4) ``` and here's the second subset, still no problems, ``` eurusd[29559:29560].reindex(index=newindex) <class 'pandas.core.frame.DataFrame'> DatetimeIndex: 192817 entries, 2002-01-01 00:00:00 to 2012-12-31 00:00:00 Freq: 30T Data columns: open 1 non-null values high 1 non-null values low 1 non-null values close 1 non-null values dtypes: float64(4) ``` I am really going crazy about this, and cannot understand the reasons of this. It seems like the dataframe is 'clean' from duplicates, and duplicated indexes.... I can provide the pickle file for the dataframe if you want.

Original source