Iteratively build multi-index dataframe in pandas

Question

I have n small dataframes which I combine into one multiindex dataframe.

d1 = DataFrame(np.array([
    ['a', 5, 9],
    ['b', 4, 61],
    ['c', 24, 9]]),
    columns=['name', 'attr11', 'attr22'])

d2 = DataFrame(np.array([
    ['a', 5, 19],
    ['b', 14, 16],
    ['c', 4, 9]]),
    columns=['name', 'attr21', 'attr22'])

d3 = DataFrame(np.array([
    ['a', 5, 19],
    ['b', 14, 16],
    ['d', 10, 14],
    ['c', 4, 9]]),
    columns=['name', 'attr21', 'attr22'])

How to combine these into one dataframe?

Result:

      name attr11 attr21  attr22
d1    a    5    NULL    9
      b    4    NULL    61
      c    24   NULL    9
d2    a    NULL   5    19
      b    NULL   14   16
      c    NULL   4    9
d3    a    NULL   5    19 
      b    NULL   14   16
      c    NULL   4    9
      d    NULL   10   14

Usually some combination of concat or merge or join, but you're going to have to let us know what the result should look like. — U2EF1
– U2EF1, Commented Feb 19, 2015 at 5:42

JAB · Accepted Answer · 2015-02-19 06:46:54Z

2

you can build the multiindex after the concatenation. You just need to add a column to each frame with the dataframe id:

frames =[d1,d2,d3]

Add a column to each frame with the frame id:

for x in enumerate(frames):
    x[1]['frame_id'] = 'd'+str(x[0]+1)

then concatenate the list of frames and set the index on the desired columns:

pd.concat(frames).set_index(['frame_id','name'])

edited Feb 19, 2015 at 6:46

answered Feb 19, 2015 at 6:21

JAB

12.9k7 gold badges48 silver badges51 bronze badges

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

4 Comments

user1140126 Over a year ago

thanks. Can a value (multi-level index) be attached? In my example, d1, d2, and d3 so that I can later know what was the value corresponding to d1

user1140126 Over a year ago

thanks. But I get an error in the enumerate section of the code. TypeError: list indices must be integers, not str

JAB Over a year ago

can you post the traceback? Are you calling enumerate on a list of dataframes?

user1140126 Over a year ago

Working fine now. Thanks. Error was because of typo.

Collectives™ on Stack Overflow

Iteratively build multi-index dataframe in pandas

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related