CODEWITHSUNDEEP
pandasmulti-index
Eugene Redekop
Eugene Redekop

Reputation: 81

selecting data from pandas panel with MultiIndex

I have a DataFrame with MultiIndex, for example:

In [1]: arrays = [['one','one','one','two','two','two'],[1,2,3,1,2,3]]
In [2]: df = DataFrame(randn(6,2),index=MultiIndex.from_tuples(zip(*arrays)),columns=['A','B'])
In [3]: df
Out [3]:
          A         B
one 1 -2.028736 -0.466668
    2 -1.877478  0.179211
    3  0.886038  0.679528
two 1  1.101735  0.169177
    2  0.756676 -1.043739
    3  1.189944  1.342415

Now I want to compute the means of elements 2 and 3 (index level 1) for each row (index level 0) and each column. So I need a DataFrame which would look like 

                                 A                            B
one 1 mean(df['A'].ix['one'][1:3])  mean(df['B'].ix['one'][1:3])
two 1 mean(df['A'].ix['two'][1:3])  mean(df['B'].ix['two'][1:3])

How do I do that without using loops over rows (index level 0) of the original data frame? What if I want to do the same for a Panel? There must be a simple solution with groupby, but I'm still learning it and can't think of an answer.

Upvotes: 4

Views: 3043

Answers (3)

joris
joris

Reputation: 139182

I know this is an old question, but for reference who searches and finds this page, the easier solution I think is the level keyword in mean:

In [4]: arrays = [['one','one','one','two','two','two'],[1,2,3,1,2,3]]

In [5]: df = pd.DataFrame(np.random.randn(6,2),index=pd.MultiIndex.from_tuples(z
ip(*arrays)),columns=['A','B'])

In [6]: df
Out[6]:
              A         B
one 1 -0.472890  2.297778
    2 -2.002773 -0.114489
    3 -1.337794 -1.464213
two 1  1.964838 -0.623666
    2  0.838388  0.229361
    3  1.735198  0.170260

In [7]: df.mean(level=0)
Out[7]:
            A         B
one -1.271152  0.239692
two  1.512808 -0.074682

In this case it means that level 0 is kept over axis 0 (the rows, default value for mean)

Upvotes: 2

ely
ely

Reputation: 77454

Do the following:

# Specify the indices you want to work with.
idxs = [("one", elem) for elem in [2,3]] + [("two", elem) for elem in [2,3]]

# Compute grouped mean over only those indices.
df.ix[idxs].mean(level=0)

Upvotes: 0

Matti John
Matti John

Reputation: 20497

You can use the xs function to select on levels.

Starting with:

              A         B
one 1 -2.712137 -0.131805
    2 -0.390227 -1.333230
    3  0.047128  0.438284
two 1  0.055254 -1.434262
    2  2.392265 -1.474072
    3 -1.058256 -0.572943

You can then create a new dataframe using:

DataFrame({'one':df.xs('one',level=0)[1:3].apply(np.mean), 'two':df.xs('two',level=0)[1:3].apply(np.mean)}).transpose()

which gives the result:

            A         B
one -0.171549 -0.447473
two  0.667005 -1.023508

To do the same without specifying the items in the level, you can use groupby:

grouped = df.groupby(level=0)
d = {}

for g in grouped:
    d[g[0]] = g[1][1:3].apply(np.mean)

DataFrame(d).transpose()

I'm not sure about panels - it's not as well documented, but something similar should be possible

Upvotes: 2

Related Questions