12

So I imported and merged 4 csv's into one dataframe called data. However, upon inspecting the dataframe's index with:

index_series = pd.Series(data.index.values)
index_series.value_counts()

I see that multiple index entries have 4 counts. I want to completely reindex the data dataframe so each row now has a unique index value. I tried:

data.reindex(np.arange(len(data)))

which gave the error "ValueError: cannot reindex from a duplicate axis." A google search leads me to think this error is because the there are up to 4 rows that share a same index value. Any idea how I can do this reindexing without dropping any rows? I don't particularly care about the order of the rows either as I can always sort it.

UPDATE: So in the end I did find a way to reindex like I wanted.

data['index'] = np.arange(len(data))
data = data.set_index('index')

As I understand it, I just added a new column called 'index' to my data frame, and then set that column as my index. As for my csv's, they were the four csv's under "download loan data" on this page of Lending Club loan stats.

Justin H
  • 163
  • 1
  • 1
  • 7
  • 3
    Can you show us your DataFrame? Or better yet, how to build an example DataFrame? – chrisaycock Jun 22 '15 at 18:16
  • In addition to @chrisaycock's comment, could you even provide some sample data from your csv-files hence your problem could result from improper csv-reading. – albert Jun 22 '15 at 18:28

1 Answers1

18

It's pretty easy to replicate your error with this sample data:

In [92]: data = pd.DataFrame( [33,55,88,22], columns=['x'], index=[0,0,1,2] )

In [93]: data.index.is_unique
Out[93]: False

In [94:] data.reindex(np.arange(len(data)))  # same error message

The problem is because reindex requires unique index values. In this case, you don't want to preserve the old index values, you merely want new index values that are unique. The easiest way to do that is:

In [95]: data.reset_index(drop=True)
Out[72]: 
    x
0  33
1  55
2  88
3  22

Note that you can leave off drop=True if you want to retain the old index values.

JohnE
  • 29,156
  • 8
  • 79
  • 109
  • 2
    If you do not want to have the old indices in a new column, you can call `data.reset_index(drop=True)`. – Tim Oct 04 '17 at 15:29
  • @Tim Thanks! That looks a bit cleaner so I edited to reflect your suggestion. – JohnE Oct 04 '17 at 16:23