CODEWITHSUNDEEP
pythonpandas
Satwik
Satwik

Reputation: 1321

Select one group and transform the remaining group to columns in pandas

I've a dataframe that looks like

import pandas as pd

from pandas.compat import StringIO

origin = pd.read_table(StringIO('''label    type    value
x   a   1
x   b   2
y   a   4
y   b   5
z   a   7
z   c   9'''))

origin
Out[5]:
  label type  value
0     x    a      1
1     x    b      2
2     y    a      4
3     y    b      5
4     z    a      7
5     z    c      9

I want to transform it to something like

  label type  value   y_value   z_value
0     x    a      1         4         7
1     x    b      2         5       NaN

Here the y_value and z_value are decided based on type.

Upvotes: 1

Views: 37

Answers (2)

Tbaki
Tbaki

Reputation: 1003

You can use pivot_table :

origin_temp = origin.pivot(index='type',columns='label',values='value')

ouput :

type    x   y     z
a    1.0    4.0  7.0
b    2.0    5.0  NaN
c    NaN    NaN  9.0

Filter what interrest you :

origin_temp = origin_temp.drop('c').reset_index()
origin_temp = origin_temp.drop('x',axis=1)
origin_temp = origin_temp[['y','z']]
origin_temp.columns = [ i + '_value' for i in origin_temp]

output

    y_value z_value
0   4.0     7.0
1   5.0     NaN

Then filter the value you want to keep 

origin_temp_2 = origin[origin['label'] == 'x' ]

output 

    label   type    value
0   x       a       1
1   x       b       2

And finally concat both :

origine_final = pd.concat([origin_temp, origin_temp_2],axis=1)

output 

    y_value z_value label   type    value
0   4.0     7.0     x       a       1
1   5.0     NaN     x       b       2

Upvotes: 0

jezrael
jezrael

Reputation: 862471

You can use boolean indexing for filtering first - in df2 also remove rows which are not in df1['type'] with isin, then pivot, add_suffix and last join:

a = 'x'
df1 = df[df['label'] == a]
df2 = df[(df['label'] != a) & (df['type'].isin(df1['type']))]
df3 = df2.pivot(index='type', columns='label', values='value').add_suffix('_value')
print (df3)
label  y_value  z_value
type                   
a          4.0      7.0
b          5.0      NaN

df3 = df1.join(df3, on='type')
print (df3)
  label type  value  y_value  z_value
0     x    a      1      4.0      7.0
1     x    b      2      5.0      NaN

Upvotes: 1

Related Questions