Pandas dataframe - transform column values into individual columns

Question

I have something like this:

   XY UV  BC   Val
0  y  u    c    11
1  y  u    b    22
2  y  v    c    33
3  y  v    b    44
4  x  u    c    111
5  x  u    b    222
6  x  v    c    333
7  x  v    b    444

I'd like to get

   XY  UV  B_Val  C_Val
0  y   u   22      11
1  y   v   44      33
2  x   u   222    111
3  x   v   444     333

In general, the BC columns above can contain a number of different items, so I need a solution that works in the general case, not only for 2 different values.

I tried writing some code that splits the dataframe, than re-joins the separate parts, but it started looking too complicated, and it wasn't going anywhere.

Paul H · Accepted Answer · 2015-11-09 16:52:15Z

2

This where I like to use multi-level indexes and stack/unstack.

So here, I'd do:

from io import StringIO
import pandas

datacsv = StringIO("""\
XY UV  BC   Val
y  u    c    11
y  u    b    22
y  v    c    33
y  v    b    44
x  u    c    111
x  u    b    222
x  v    c    333
x  v    b    444
""")
df = pandas.read_csv(datacsv, sep='\s+')
df.set_index(['XY', 'UV', 'BC']).unstack(level='BC')

Which gives us:

       Val     
BC       b    c
XY UV          
x  u   222  111
   v   444  333
y  u    22   11
   v    44   33

So we have MultiIndexes on both the rows and columns. Assuming you don't want that, I would just do:

xtab = (df.set_index(['XY', 'UV', 'BC'])
          .unstack(level='BC')['Val']
          .reset_index())

And that'll give you:

BC XY UV    b    c
0   x  u  222  111
1   x  v  444  333
2   y  u   22   11
3   y  v   44   33

answered Nov 9, 2015 at 16:52

Paul H

68.7k23 gold badges165 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Baron Yugovich Over a year ago

How do I rename the column index from 'BC' to just 'index'?

Paul H Over a year ago

That's actually the name of the columns index level. Try xtab.columns.names = [] or maybe it's xtab.columns.index.names = []

Baron Yugovich Over a year ago

I got ValueError: Length of new names must be 1, got 0

Paul H Over a year ago

Try filling the list with None or an empty string.

EdChum · Accepted Answer · 2015-11-09 16:48:59Z

2

IIUC you want to pivot:

In [110]:
df.pivot(index='XY',columns='BC', values='Val')

Out[110]:
BC   b   c
XY        
x   10  20
y   33  44

EDIT

pivot doesn't support multi-index df's which was one method I was considering, what you could do is add a new column which is a composite of the 2 columns and use this as the index to pivot on:

In [120]:
df['composite'] = df['XY']+df['UV']
df

Out[120]:
  XY UV BC  Val composite
0  y  u  c   11        yu
1  y  u  b   22        yu
2  y  v  c   33        yv
3  y  v  b   44        yv
4  x  u  c  111        xu
5  x  u  b  222        xu
6  x  v  c  333        xv
7  x  v  b  444        xv

In [121]:
df.pivot(index='composite', columns='BC', values='Val')

Out[121]:
BC           b    c
composite          
xu         222  111
xv         444  333
yu          22   11
yv          44   33

edited Nov 9, 2015 at 16:48

answered Nov 9, 2015 at 16:28

EdChum

397k204 gold badges836 silver badges583 bronze badges

3 Comments

Baron Yugovich Over a year ago

How does the index= clause look like if I have more than 1 column analogous to XY? I'm sorry for adding a question like this.

EdChum Over a year ago

I really don't understand what you're asking, as I've stated before unless you post a question with representative data and desired output then it becomes difficult to answer speculative questions

Baron Yugovich Over a year ago

Please take a look at my improved example. Thanks

A Laurent · Accepted Answer · 2015-11-09 16:49:01Z

1

You can also use multi Index and unstack like this:

df=df.set_index(['XY','UV','BC'])
df=df.unstack('BC')

answered Nov 9, 2015 at 16:49

A Laurent

1742 silver badges8 bronze badges

Collectives™ on Stack Overflow

Pandas dataframe - transform column values into individual columns

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related