CODEWITHSUNDEEP
pythonpandas
1 1
1 1

Reputation: 57

Transform data frame in pandas

Assume I have data frame:

x1  x2  value_x1    value_x2
a   b   1           2       
a   c   3           4
b   c   5           6

Can I get the following data frame?

a   b   c
1   2   0
3   0   4
0   5   6

I tried pivot_table and melt, but result is not suitable.

Thank you.

Upvotes: 1

Views: 60

Answers (1)

jezrael
jezrael

Reputation: 862581

Use wide_to_long:

df = pd.wide_to_long(df.reset_index(), stubnames=['x', 'value_x'], i='index', j='a')

print (df)
         x  value_x
index a            
0     1  a        1
1     1  a        3
2     1  b        5
0     2  b        2
1     2  c        4
2     2  c        6

And then unstack with some data cleaning:

df = (df.reset_index(level=1, drop=True)
        .set_index('x', append=True)['value_x']
        .unstack(fill_value=0)
        .rename_axis(None)
        .rename_axis(None, axis=1))
print (df)
   a  b  c
0  1  2  0
1  3  0  4
2  0  5  6

Upvotes: 1

Related Questions