I’m failing with accessing the values from multi-index/columns DataFrame by condition.
df = pd.DataFrame(np.arange(16).reshape(4,4),
index=[['a', 'a', 'b', 'b'], ["c", "d", "c", "d"]],
columns=[['cat1', 'cat1', 'cat2', 'cat2'], ['vals1', 'vals2', 'vals1', 'vals2']])
cat1 cat2
vals1 vals2 vals1 vals2
a c 0 1 2 3
d 4 5 6 7
b c 8 9 10 11
d 12 13 14 15
I know how to access values|subframes by their position e.g.
df.loc[("a", "c"), ("cat1", "vals1")]
df.loc[(slice(None), "d"), ("cat1", slice(None))]
BUT how to use the condition here
e.g. select the value in column ("cat1", "vals2") where ("cat2", "vals2")==7
>Solution :
Use tuples in DataFrame.loc:
a = df.loc[df[("cat2", "vals2")] == 7, ('cat1', 'vals1')]
print (a)
Last if need scalar from one element Series:
out = a.iat[0]
If possible no match:
out = next(iter(a), 'no match')
You can compare sliced rows, columns – output is DataFrame filled by boolean – so for boolean Series need test if any True per row by DataFrame.any or all Trues per rows by DataFrame.all:
m = df.loc[:, ("cat2", slice(None))]==7
a = df.loc[m.any(axis=1), ("cat1", "vals2")]
print (a)
a d 5
Name: (cat1, vals2), dtype: int32
m = df.loc[:, ("cat2", slice(None))]==7
df2 = df.loc[m.any(axis=1), ("cat1", slice(None))]
print (df2)
cat1
vals1 vals2
a d 4 5