0

I have a CSV file format like this:

filename  value  class
file1     55     mango
file1     62     orange
file1     74     apple
file2     75     mango
file2     42     orange
file3     89     apple
file4     54     orange
file4     35     apple

I want to transform this on the basis of its class values, like this:

filename   mango  apple  orange
file1      55     74     62
file2      75     0      42
file3      0      89     0
file4      0      54     35

Please, help me with this. I have tried the groupby method but that didn't work.

funie200
  • 3,688
  • 5
  • 21
  • 34
Ashwin Singh
  • 39
  • 5

2 Answers2

4

Try this:

df.pivot(index='file', columns='class', values='value').fillna(0).reset_index()

class   file    apple   mango   orange
0      file1    74.0    55.0    62.0
1      file2    0.0     75.0    42.0
2      file3    89.0    0.0     0.0
3      file4    35.0    0.0     54.0
Mateus Mattos
  • 115
  • 5
2

Another way groupby(), first() value

df.groupby(['filename', 'class'])['value'].first().\
unstack().reset_index().fillna(0).rename_axis(columns=None)

    filename  apple  mango  orange
0    file1   74.0   55.0    62.0
1    file2    0.0   75.0    42.0
2    file3   89.0    0.0     0.0
3    file4   35.0    0.0    54.0
wwnde
  • 26,119
  • 6
  • 18
  • 32