20

After using transpose on a dataframe there is always an extra row as a remainder from the initial dataframe's index for example:

import pandas as pd

df = pd.DataFrame({'fruit':['apple','banana'],'number':[3,5]})
df
    fruit  number
0   apple       3
1  banana       5
df.transpose()
        0       1
fruit   apple  banana
number      3       5

Even when i have no index:

df.reset_index(drop = True, inplace = True)
df
    fruit  number
0   apple       3
1  banana       5

df.transpose()
        0       1
fruit   apple  banana
number      3       5

The problem is that when I save the dataframe to a csv file by:

df.to_csv(f)

this extra row stays at the top and I have to remove it manually every time.

Also this doesn't work:

 df.to_csv(f, index = None)

because the old index is no longer considered an index (just another row...).

It also happened when I transposed the other way around and I got an extra column which i could not remove.

Any tips?

Scott Boston
  • 147,308
  • 15
  • 139
  • 187
Helena K
  • 469
  • 2
  • 6
  • 15
  • 2
    `print df.T.to_csv(header=None)` or `print df.to_csv(index=None)` both work for me. – piRSquared Jul 01 '16 at 15:24
  • Oh thanks, I hadn't realised this was actually a header. The solution which worked in my case is `df.T.to_csv(f, header=None) ` assuming that df is the original dataframe (I want the transposed). Also, I don't get why you put `print`? – Helena K Jul 01 '16 at 15:32
  • 1
    Dataframes always have to have indexes and headers in Pandas, so if you don't supply one it just gives you integers. When you transpose it you just swap the index and header dimensions. – Jeff Jul 01 '16 at 15:35

3 Answers3

18

I had the same problem, I solved it by reseting index before doing the transpose. I mean df.set_index('fruit').transpose():

import pandas as pd

df = pd.DataFrame({'fruit':['apple','banana'],'number':[3,5]})
df
    fruit   number
0   apple   3
1   banana  5

And df.set_index('fruit').transpose() gives:

fruit   apple   banana
number  3       5
user1742571
  • 181
  • 1
  • 3
12

Instead of removing the extra index, why don't try setting the new index that you want and then use slicing ?

step 1: Set the new index you want:
df.columns = df.iloc[0]
step 2: Create a new dataframe removing extra row.
df_new = df[1:]

galaxyan
  • 5,944
  • 2
  • 19
  • 43
Radhika Nair
  • 141
  • 10
0

If you do not want one of your columns to stay an index (especially if you are using a larger dataset) and want to have a numerical index, you can use this method:

#Create df
df = pd.DataFrame({'fruit':['apple','banana'],'number':[3,5]})
#Set the first column as the index + transpose it
df = df.set_index(df.columns[0]).transpose()
#Rename the index with the name of your first column + reset index
df = df.rename_axis("fruit").reset_index()
#Remove the index name
df = df.rename_axis(None, axis = 1)

#Result
    fruit  apple  banana
0  number      3       5
jessieolough
  • 9
  • 4