Creating new complex index with pandas Dataframe columns

Question

I'm trying to concatenate the columns 'A' and 'C' in a Dataframe like the following to use it as a new Index:

     A  |  B  |  C  |  ...
---------------------------
 0   5  | djn |  0  |  ...
 1   5  | vlv |  1  |  ...
 2   5  | bla |  2  |  ...
 3   5  | ses |  3  |  ...
 4   5  | dug |  4  |  ...

The desired result would be a Dataframe which is similar to the following result:

         A  |  B  |  C  |  ...
-------------------------------
 05000   5  | djn |  0  |  ...
 05001   5  | vlv |  1  |  ...
 05002   5  | bla |  2  |  ...
 05003   5  | ses |  3  |  ...
 05004   5  | dug |  4  |  ...

I've searched my eyes off, does someone know how to manipulate a dataframe to get such result?

cphlewis · Accepted Answer · 2015-03-26 04:48:09Z

2

#dummying up a dataframe
cf['A'] = 5*[5]
cf['C'] = range(5)
cf['B'] = list('qwert')
#putting together two columns into a new one -- EDITED so string formatting is OK
cf['D'] = map(lambda x: str(x).zfill(5), 1000*cf.A + cf.C)
# use it as the index
cf.index = cf.D
# we don't need it as a column
cf.drop('D', axis=1, inplace=True)
print(cf.to_csv())

D,A,C,B
05000,5,0,q
05001,5,1,w
05002,5,2,e
05003,5,3,r
05004,5,4,t

That said, I suspect you'd be safer with multi-indexing (what if the values in B go above 999....), or sorting or grouping on multi-columns.

edited Mar 26, 2015 at 4:48

answered Mar 13, 2015 at 6:04

cphlewis

16.3k4 gold badges52 silver badges58 bronze badges

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

11 Comments

CMPSoares Over a year ago

In my case I can figure out the maximum before-hand but multi-indexing could be an option. BTW. How can I do this with 0-padding and turn it in to a string format?

cphlewis Over a year ago

Look up string formats and add that to the line that creates cf['D'].

cphlewis Over a year ago

... okay, I added string formatting. Have to say it gives me the heebie-jeebies to do this in the data -- I'd keep that as a pretty-for-humans option in reports, or such, and multi-index the data.

CMPSoares Over a year ago

It's a pretty for human option! ;-) Thank you very much! I have another one I'll use for the real jobs! ;-)

CMPSoares Over a year ago

Hey @cphlewis, I used your solution but had to turn it in to a for loop because when transformed into a string it would save the whole concated index in each entry. See: stackoverflow.com/questions/29110566/… Do you know why?

|

Collectives™ on Stack Overflow

Creating new complex index with pandas Dataframe columns

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related