Pandas MultiIndex Vector Setting

Question

I have a DataFrame with multiindex like this:

             0         1         2
 a 0  0.928295  0.828225 -0.612509
   1  1.103340 -0.540640 -0.344500
   2 -1.760918 -1.426488 -0.647610
   3 -0.782976  0.359211  1.601602
   4  0.334406 -0.508752 -0.611212
 b 2  0.717163  0.902514  1.027191
   3  0.296955  1.543040 -1.429113
   4 -0.651468  0.665114  0.949849
 c 0  0.195620 -0.240177  0.745310
   1  1.244997 -0.817949  0.130422
   2  0.288510  1.123550  0.211385
   3 -1.060227  1.739789  2.186224
   4 -0.109178 -1.645732  0.022480
 d 3  0.021789  0.747183  0.614485
   4 -1.074870  0.407974 -0.961013

What I want : array([1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 1, 0])

Now I want to generate a zero vector which have the sample length of this DataFrame and only have ones on the first elements of level[1] index. For example, here the df have a shape of (15, 3). Therefore I want to get a vector with length of 15 and should have 1 at(a, 0), (b, 2), (c, 0), （d, 3) and 0 at other points. How could I generator an vector like that? (If possible don't loop over get each sub vector and then use np.concatenate()) Thanks a lot!

BENY · Accepted Answer · 2018-08-09T02:42:54.793

2

IIUC duplicated

(~df.index.get_level_values(0).duplicated()).astype(int)
Out[726]: array([1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 1, 0])

Or using groupby and head

df.loc[df.groupby(level=0).head(1).index,'New']=1
df.New.fillna(0).values
Out[721]: array([1., 0., 0., 0., 0., 1., 0., 0., 1., 0., 0., 0., 0., 1., 0.])

edited Aug 09 '18 at 02:42

answered Aug 09 '18 at 02:35

BENY

317,841
20
164
234

The `duplicated` method is nice! – sacuL Aug 09 '18 at 02:47
Thanks for your answer and I have just found a simpler solution. Thank you all the same. – Kid Aug 09 '18 at 02:48
@Kid hi , you can vote for the answers and accept one of the answers you like – BENY Aug 09 '18 at 02:51

score 1 · Answer 2 · answered Aug 09 '18 at 02:40

Get the labels of your first multiindex, turn them into a series, then find where they are not equal to the adjacent ones

labels = pd.Series(df.index.labels[0])

v = labels.ne(labels.shift()).astype(int).values

>>> v
array([1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 1, 0])

score 0 · Answer 3 · answered Aug 09 '18 at 02:51

pd.Index(df.labels[0])
Int64Index([0, 0, 0, 0, 0, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3], dtype='int64')
res = pd.Index(df.labels[0]).duplicated(keep='first')
array([False,  True,  True,  True,  True, False,  True,  True, False,
       True,  True,  True,  True, False,  True])

Mulitindex has an attribute labels to indicate postion. Which has the same meaning of the requirement.

Pandas MultiIndex Vector Setting

3 Answers3