CODEWITHSUNDEEP
python-3.xpandasmulti-index
Antti Ellonen
Antti Ellonen

Reputation: 189

Python pandas multiindex select values

I have a pandas multiindex with mostly numerical values, but some None, NaN, or "-" in the data as well. Something like this:

                0         1         2         3
bar one -0.096648 -0.080298  0.859359 -0.030288
    two       NaN -0.431791  1.923893 -1.544845
    thr -0.358526  1.416211  1.589617  0.284130
baz one  0.639951 -0.008833         -  0.042315
    two  0.705281      None -1.108522  0.471676

Now I need to identify for each level 0 index which row has the smallest numerical value in column 0 and extract value for column 3 for that row. (ignoring NaN, None and -)

For example, for 'bar' I compare -0.096648, NaN, -0.358526 and the smallest of these is -0.358526 so I want the value 0.284130 (from the column 3)

I'm sure this is simple, but I'm not very familiar with these multi-index tables and just got lost and frustrated.

Upvotes: 1

Views: 212

Answers (1)

jezrael
jezrael

Reputation: 862406

Use DataFrameGroupBy.idxmin for indices, but first need some preprocessing and then select by DataFrame.iloc:

#get name for level of MultiIndex and create unique index
df1 = df.rename_axis(('a','b')).reset_index()
#if values non numeric in column 0 convert to NaNs
df1[0] = pd.to_numeric(df1[0], errors='coerce')
#get index of minimal values of column 0 per column a
s = df1.groupby('a')[0].idxmin()
print (s)
a
bar    2
baz    3
Name: 0, dtype: int64

#select by positions index and column 3
df = df.iloc[s, 3].to_frame()
print (df)
                3
bar thr  0.284130
baz one  0.042315

Upvotes: 2

Related Questions