1

I have the following table

import pandas as pd

data = {'ID': [123, 123,124,124,125], 'Method': ['angular', 'square','angular','square','square'], 'Colour': ['red', 'blue','red','blue','blue'], 'Result': ['20', '30','10','5','13'] }

df = pd.DataFrame (data, columns = ['ID','Method','Colour','Result'])
df


ID  Method  Colour  Result
123 angular red       20
123 square  blue      30
124 angular red       10
124 square  blue       5
125 square  blue       13

So, I want to have transform this table in a generic way so that I can get. This table can contain more entries but I only show a snippet of it.

ID  Method_angular_red Method_square_blue 

123         20              30
124         10              5
125                         13

I know that we can use unstack and so on, but I'm not confidence with that since I am new in python. Thanks for any help.

Hello I have a second UseCase:

let's suppose I have now more columns like:

import pandas as pd

data = {'ID': [123, 123,124,124,125], 'Text': ['A', 'A','B','B','C'],'flag': [0, 0,1,1,2],'Method': ['angular', 'square','angular','square','square'], 'Colour': ['red', 'blue','red','blue','blue'], 'Result': ['20', '30','10','5','13'] }

df = pd.DataFrame (data, columns = ['ID','Text','flag','Method','Colour','Result'])
df



ID  Text flag   Method  Colour  Result
123   A    0     angular    red       20
123   A    0      square    blue      30
124   B    1     angular    red       10
124   B    1      square    blue       5
125   C    2      square    blue       13

I want to have a flexibel script to group or aggregate the first columns (it could be N columns) and pivot the others like

ID    Text flag  Method_angular_red Method_square_blue 

123     A    0          20              30
124     B    1          10              5
125     C    2                          13

I tried with following code but it throws me an error:

m = df.assign(Method=df['Method'].add('_'+df['Colour'])).drop('Colour',1)
out = m.pivot(*m).add_prefix('Method_').rename_axis(None,axis=1).reset_index()
out
CedSoft
  • 23
  • 3
  • try `m.set_index(['ID','Text','flag','Method'])['Result'].unstack().add_prefix('Method_').rename_axis(None,axis=1).reset_index()` for the second dataframe after the first line which defines m , also read this post on [how to pivot a dataframe](https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe) – anky Mar 06 '20 at 10:14
  • 1
    Thank you anky_91 for your help. It works fine – CedSoft Mar 09 '20 at 10:24

1 Answers1

2

You can try df.pivot after adding a the 2 columns

m = df.assign(Method=df['Method'].add('_'+df['Colour'])).drop('Colour',1)
out = m.pivot(*m).add_prefix('Method_').rename_axis(None,axis=1).reset_index()
#or out = (m.pivot_table('Result','ID','Method',aggfunc=sum).rename_axis(None,axis=1)
#          .add_prefix('Method_').reset_index())

    ID Method_angular_red Method_square_blue
0  123                 20                 30
1  124                 10                  5
2  125                NaN                 13
anky
  • 74,114
  • 11
  • 41
  • 70