How to preserve header as a column and have an index after transposing dataframe in pandas?-CodePudding

I have a data frame with headers X,Y,Z which looks like this -

   0 X Y Z
   0 P P P
   1 P P P
   2 P P P

I have a 0 in the header because I changed the header row from the default unnamed row in my excel file. My header before was Unnamed: 1 Unnamed:2...Unnamed: 34 I changed the header row like this -

    header_row = df_1.iloc[0]
    df_1 = df_1[1:]
    df_1.columns = header_row

When I transpose it, it looks like -

   0 0 1 2
   X P P P
   Y P P P
   Z P P P

Instead of the headers (X,Y.Z) becoming index, how can I add it as a new column in the data frame so that it looks like this ?

    0 1 2 3
  0 X P P P
  1 Y P P P
  2 Z P P P

I have tried df.T.reset_index(). It gives me the error - ValueError: cannot insert 0, already exists.

CodePudding user response：

Instead your solution try first transpose and then remove Unnamed index values - set to default values:

df_1 = pd.read_csv(file)
print (df_1)
  Unnamed 1 Unnamed 4  Unnamed 7
0         X         Y          Z
1         P         P          P
2         P         P          P
3         P         P          P


df_1 = df_1.T.reset_index(drop=True)
print (df_1)
   0  1  2  3
0  X  P  P  P
1  Y  P  P  P
2  Z  P  P  P