Properly transpose Pandas dataframe and access first column-CodePudding

I have a dataframe that I read from a csv file, looks like this:

original = pd.read_csv("fruit.csv")

print(original)
      fruit  apple  banana  pear
0    weight     50      45    48
1     price    100     150   200
2  calories     10      50    40

I would like to transpose it so that I can access the categories that are now in the 'fruit' row as their own colums, i.e. like this:

print(transposed['fruit'])

0  apple
1  banana
2  pear

Such that I can do things like print(transposed[transposed['fruit']=='apple']['weight'])

If I try to simply transpose the dataframe then I cannot access the first column (KeyError: 'fruit'). I tried all sort of manipulations of the dataframe, but either the index gets messed up or the first column name gets deleted (when I reset_index(), 'fruit' becomes 'index' for some reason).

CodePudding user response：

Use melt and pivot:

>>> df.melt('fruit').pivot('variable', 'fruit', 'value') \
      .rename_axis(index='fruit', columns=None).reset_index()

    fruit  calories  price  weight
0   apple        10    100      50
1  banana        50    150      45
2    pear        40    200      48

CodePudding user response：

Another way:

transposed = df.T.reset_index().set_axis(df.T.reset_index().iloc[0], axis=1).iloc[1:].rename_axis(None, axis=1)

Output:

>>> transposed
    fruit weight price calories
1   apple     50   100       10
2  banana     45   150       50
3    pear     48   200       40

>>> transposed[transposed['fruit']=='apple']['weight']
1    50
Name: weight, dtype: object

CodePudding user response：

IIUC, set fruit as the index; selection should be much easier:

temp =  df.set_index('fruit')
temp.loc['weight', 'apple']
50