python - 基于第二个索引在 pandas DataFrame 中保留行标签的子集

Question

给定一个具有包含三个级别(实验、试验、插槽)的分层索引的 DataFrame 和一个具有包含两个级别(实验、试验)的分层索引的第二个 DataFrame，我如何删除第一个 DataFrame 中的所有行( experiment, trial) 不包含在第二个数据框中？

示例数据:

from io import StringIO
import pandas as pd

df1_data = StringIO(u',experiment,trial,slot,token\n0,btn144a10_p_RDT,0,0,4.0\n1,btn144a10_p_RDT,0,1,14.0\n2,btn144a10_p_RDT,1,0,12.0\n3,btn144a10_p_RDT,1,1,14.0\n4,btn145a07_p_RDT,0,0,6.0\n5,btn145a07_p_RDT,0,1,19.0\n6,btn145a07_p_RDT,1,0,17.0\n7,btn145a07_p_RDT,1,1,13.0\n8,chn004b06_p_RDT,0,0,6.0\n9,chn004b06_p_RDT,0,1,8.0\n10,chn004b06_p_RDT,1,0,2.0\n11,chn004b06_p_RDT,1,1,5.0\n12,chn008a06_p_RDT,0,0,12.0\n13,chn008a06_p_RDT,0,1,14.0\n14,chn008a06_p_RDT,1,0,6.0\n15,chn008a06_p_RDT,1,1,4.0\n16,chn008b06_p_RDT,0,0,3.0\n17,chn008b06_p_RDT,0,1,13.0\n18,chn008b06_p_RDT,1,0,12.0\n19,chn008b06_p_RDT,1,1,19.0\n20,chn008c04_p_RDT,0,0,17.0\n21,chn008c04_p_RDT,0,1,2.0\n22,chn008c04_p_RDT,1,0,1.0\n23,chn008c04_p_RDT,1,1,6.0\n')
df1 = pd.DataFrame.from_csv(df1_data).set_index(['experiment', 'trial', 'slot'])

df2_data = StringIO(u',experiment,trial,target\n0,btn145a07_p_RDT,1,13\n1,chn004b06_p_RDT,1,9\n2,chn008a06_p_RDT,0,15\n3,chn008a06_p_RDT,1,15\n4,chn008b06_p_RDT,1,1\n5,chn008c04_p_RDT,1,12\n')
df2 = pd.DataFrame.from_csv(df2_data).set_index(['experiment', 'trial'])

第一个数据框看起来像:

                            token
experiment      trial slot       
btn144a10_p_RDT 0     0         4
                      1        14
                1     0        12
                      1        14
btn145a07_p_RDT 0     0         6
                      1        19
                1     0        17
                      1        13
chn004b06_p_RDT 0     0         6
                      1         8
                1     0         2
                      1         5
chn008a06_p_RDT 0     0        12
                      1        14
                1     0         6
                      1         4
chn008b06_p_RDT 0     0         3
                      1        13
                1     0        12
                      1        19
chn008c04_p_RDT 0     0        17
                      1         2
                1     0         1
                      1         6

第二个数据框看起来像:

                       target
experiment      trial        
btn145a07_p_RDT 1          13
chn004b06_p_RDT 1           9
chn008a06_p_RDT 0          15
                1          15
chn008b06_p_RDT 1           1
chn008c04_p_RDT 1          12

我想要的结果:

                            token
experiment      trial slot       
btn145a07_p_RDT 1     0        17
                      1        13
chn004b06_p_RDT 1     0         2
                      1         5
chn008a06_p_RDT 0     0        12
                      1        14
                1     0         6
                      1         4
chn008b06_p_RDT 1     0        12
                      1        19
chn008c04_p_RDT 1     0         1
                      1         6

Answer 1

一种方法是使用 merge

merged = pd.merge(
    df2.reset_index(),
    df1.reset_index(),
    left_on=['experiment', 'trial'],
    right_on=['experiment', 'trial'],
    how='left')

你只需要重新索引 merged 到你喜欢的任何东西(我无法从问题中准确判断)。