0

I am having troubles understanding how pandas multiindex work. Specifically:

  1. how to merge two dataframes of different index level (by row)
  2. how can change index level for a dataframe

Using an example from a previous question: 

d1 = pd.DataFrame( {'StudentID':    ["x1", "x10", "x2","x3", "x4", "x5", "x6",   "x7", "x8", "x9"],
 'StudentGender' : ['F', 'M', 'F', 'M', 'F', 'M', 'F', 'M', 'M', 'M'],
 'ExamenYear': ['2007','2007','2007','2008','2008','2008','2008','2009','2009','2009'],
 'Exam': ['algebra', 'stats', 'bio', 'algebra', 'algebra', 'stats', 'stats', 'algebra', 'bio', 'bio'],
 'Participated': ['no','yes','yes','yes','no','yes','yes','yes','yes','yes'],
 'Passed': ['no','yes','yes','yes','no','yes','yes','yes','no','yes']},
 columns = ['StudentID', 'StudentGender', 'ExamenYear', 'Exam', 'Participated', 'Passed'])

I compute two datasets 

def ZahlOccurence_0(x):
     return pd.Series({'All': len(x['StudentID']),
                   'Part': sum(x['Participated'] == 'yes'),
                   'Pass' :  sum(x['Passed'] == 'yes')})
t1 = d1.groupby(['ExamenYear', 'Exam']).apply(ZahlOccurence_0)   
t2 = d1.groupby('ExamenYear').apply(ZahlOccurence_0)

How can I merge t1 and t2 by rows ? 

print t1
                    All  Part  Pass
ExamenYear Exam                    
2007       algebra    1     0     0
           bio        1     1     1
           stats      1     1     1
2008       algebra    2     1     1
           stats      2     2     2
2009       algebra    1     1     1
           bio        2     2     1

print t2 

            All  Part  Pass
ExamenYear                 
2007          3     2     2
2008          4     3     3
2009          3     3     2

I tried the following 

t2 = t2.set_index([t2.index, np.array(['tot']* 3)], append = False)

BUT 

 pd.concat(t1,t2)

produces an error

ValueError: Cannot call bool() on DataFrame.

What I am doing wrong ?

Thanks in advance

Community
  • 1
  • 1
user1043144
  • 2,680
  • 5
  • 29
  • 45
  • 2
    If you type `help(pd.concat)` you'll see that it takes a collection of objects as its first argument. IOW, it should be `pd.concat([t1, t2])`. I'm not sure what output you're hoping for, though: is it something like `pd.concat([t1, t2]).sort()`? – DSM Mar 26 '13 at 15:57
  • Maybe this is what you are looking for? - t2['Exam'] = 'tot' ; pd.concat([t1.reset_index(),t2.reset_index()]) – user1827356 Mar 26 '13 at 15:59
  • Silly me ! THANKS !!!!!! DSM: I just forgot to put objects in list :-( @user1827356: that's what I was looking for and did it in a complicated way. Can you post your comment as a reply so that I can accept it and close the question ? – user1043144 Mar 26 '13 at 16:19

1 Answers1

3

As @DSM pointed it out the DataFrame objects need to be in a list

pd.concat([t1, t2])

I do have to perform calculations similar to yours. This is my preferred method

t2['Exam'] = 'tot'
            All  Part  Pass Exam
ExamenYear
2007          3     2     2  tot
2008          4     3     3  tot
2009          3     3     2  tot

pd.concat([t1.reset_index(),t2.reset_index()], ignore_index=True)

   All     Exam ExamenYear  Part  Pass
0    1  algebra       2007     0     0
1    1      bio       2007     1     1
2    1    stats       2007     1     1
3    2  algebra       2008     1     1
4    2    stats       2008     2     2
5    1  algebra       2009     1     1
6    2      bio       2009     2     1
7    3      tot       2007     2     2
8    4      tot       2008     3     3
9    3      tot       2009     3     2
user1827356
  • 6,764
  • 2
  • 21
  • 30