Pandas unable to reset index because name exist

Question

I have a multi-level pandas dataframe which im trying to level. I use reset_index but its giving me error that the name already exist.

I dont want to use reset_index(drop=True) because i want to keep one of the column names still.

i want as my new dataframe:

country,listing_neighborhood,count

right now,

df.columns only gives count.

my code:

df.columns = ['count']
df.reset_index() -> gives error that `ValueError: cannot insert country, already exists`

I also tried:

df.columns.droplevel(0) -> gives error that 'Index' object has no attribute 'droplevel'

Answer 1

You need remove first duplicated level:

df = pd.DataFrame({
        'A':list('abcdef'),
         'B':[4,5,4,5,5,4],
         'C':[7,8,9,4,2,3],
         'F':list('aaabbb')
})

df = (df.set_index(['A','F','C'])
        .rename_axis(['country','country','listing_neighborhood'])
        .rename(columns={'B':'count'}))

print (df)
                                      count
country country listing_neighborhood       
a       a       7                         4
b       a       8                         5
c       a       9                         4
d       b       4                         5
e       b       2                         5
f       b       3                         4

df = df.reset_index(level=0, drop=True).reset_index()
print (df)
  country  listing_neighborhood  count
0       a                     7      4
1       a                     8      5
2       a                     9      4
3       b                     4      5
4       b                     2      5
5       b                     3      4

Or:

df = df.droplevel(0).reset_index()

Answer 2

You can change the existing name so that it would not be duplicated anymore:

df.reset_index(name="new_name")

Hope this help