在多个列中标识值发生变化的位置，按主题和实验分组，然后单击“确定”

Question

这是对以前帖子的更新。我在组内排序时遇到问题。感谢@IanThompson早些时候的帮助。你知道吗

使用以下代码，我尝试按受试者ID和实验进行分组，然后创建一个名为“color”的列，该列由与其关联的“Trial”排序“Trial”对应于其中一个颜色列（例如“color1”、“color10”）中的值更改为银色的行。你知道吗

我的问题是，我无法在受试者内进行排序，即按试验分组。我已经尝试了.sort\u值（by=['Trial']），但是这似乎不是在组中对它进行排序，这是我希望做的。你知道吗

我希望输出与@IanThompson在本文中所做的完全一样。你知道吗

下面是相关代码，后面是数据集。你知道吗

import pandas as pd
pd.set_option('display.max_rows', 100)
import numpy as np

df1 = pd.read_clipboard()

# melt it into shape
df2 = df1.melt(id_vars = [
    'ExperimentName',
    'Subject',
    'Trial'
], value_vars = [
    'color1',
    'color10',
    'color11',
    'color12',
    'color13',
    'color14',
    'color15',
    'color16',
    'color17',
    'color18',
    'color19',
    'color2',
    'color20',
    'color21',
    'color22',
    'color23',
    'color24',
    'color25',
    'color26',
    'color27',
    'color28',
    'color29',
    'color3',
    'color30',
    'color31',
    'color32',
    'color33',
    'color34',
    'color35',
    'color36',
    'color4',
    'color5',
    'color6',
    'color7',
    'color8',
    'color9',
], var_name = 'color')

# filter value to 'silver'
df2[df2.value == 'silver'].groupby([
    'ExperimentName',
    'Subject',
    'color'
]).agg({'Trial' : 'min'}).sort_values(by=['Trial'])

以下是数据集：

ExperimentName  Subject Trial   Background  color1  color10 color11 color12 color13 color14 color15 color16 color17 color18 color19 color2  color20 color21 color22 color23 color24 color25 color26 color27 color28 color29 color3  color30 color31 color32 color33 color34 color35 color36 color4  color5  color6  color7  color8  color9  ColorChange RespSlide.RT    RespSlide.RTTime
FacFac  91  1   bgA.jpg silver  teal    teal    teal    teal    teal    teal    teal    teal    teal    blue    red blue    blue    blue    blue    blue    blue    blue    blue    purp    purp    red purp    purp    purp    purp    purp    purp    purp    red red red red red red 0   949 33133
FacFac  91  2   bgA.jpg silver  teal    teal    teal    teal    teal    teal    teal    teal    teal    blue    silver  blue    blue    blue    blue    blue    blue    blue    blue    purp    purp    red purp    purp    purp    purp    purp    purp    purp    red red red red red red 0   1019    45197
FacFac  91  3   bgA.jpg silver  teal    teal    teal    teal    teal    teal    teal    teal    teal    blue    silver  blue    blue    blue    blue    blue    blue    blue    blue    purp    purp    silver  purp    purp    purp    purp    purp    purp    purp    red red red red red red 0   5596    57453
FacFac  91  4   bgA.jpg silver  teal    teal    teal    teal    teal    teal    teal    teal    teal    blue    silver  blue    blue    blue    blue    blue    blue    blue    blue    purp    purp    silver  purp    purp    purp    purp    purp    purp    purp    silver  red red red red red 0   1506    67157
FacFac  91  5   bgA.jpg silver  teal    teal    teal    teal    teal    teal    teal    teal    teal    blue    silver  blue    blue    blue    blue    blue    blue    blue    blue    purp    purp    silver  purp    purp    purp    purp    purp    purp    purp    silver  silver  red red red red 0   1061    74908
FacFac  91  6   bgA.jpg silver  teal    teal    teal    teal    teal    teal    teal    teal    teal    blue    silver  blue    blue    blue    blue    blue    blue    blue    blue    purp    purp    silver  purp    purp    purp    purp    purp    purp    purp    silver  silver  silver  red red red 0   2350    87092
FacFac  91  7   bgA.jpg silver  teal    teal    teal    teal    teal    teal    teal    teal    teal    blue    silver  blue    blue    blue    blue    blue    blue    blue    blue    purp    purp    silver  purp    purp    purp    purp    purp    purp    purp    silver  silver  silver  silver  red red 0   1067    95404
FacFac  91  8   bgA.jpg silver  teal    teal    teal    teal    teal    teal    teal    teal    teal    blue    silver  blue    blue    blue    blue    blue    blue    blue    blue    purp    purp    silver  purp    purp    purp    purp    purp    purp    purp    silver  silver  silver  silver  silver  red 0   1341    103924
FacFac  91  9   bgA.jpg silver  teal    teal    teal    teal    teal    teal    teal    teal    teal    blue    silver  blue    blue    blue    blue    blue    blue    blue    blue    purp    purp    silver  purp    purp    purp    purp    purp    purp    purp    silver  silver  silver  silver  silver  silver  0   1188    112484
FacFac  91  30  bgC.jpg silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  purp    silver  purp    purp    purp    purp    purp    purp    purp    silver  silver  silver  silver  silver  silver  2   548 308799
FacFac  91  31  bgC.jpg silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  purp    purp    purp    purp    purp    purp    purp    silver  silver  silver  silver  silver  silver  2   547 317927
FacFac  91  32  bgC.jpg silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  purp    purp    purp    purp    purp    purp    silver  silver  silver  silver  silver  silver  2   521 324831
FacFac  91  33  bgC.jpg silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  purp    purp    purp    purp    purp    silver  silver  silver  silver  silver  silver  2   502 330759
FacNo   91  1   bgA.jpg silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk 0   982 93293
FacNo   91  2   bgA.jpg silver  silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk 0   1215    102005
FacNo   91  3   bgA.jpg silver  silver  blk blk blk blk blk blk blk blk silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk 0   632 110301
FacNo   91  37  bgC.jpg silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  blk silver  silver  silver  silver  silver  silver  2   1385    393502
FacNo   91  38  bgC.jpg silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  2   1141    400438
FacNo   91  39  bgC.jpg silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  2   0   0
RandFac 92  14  bgB.jpg silver  silver  silver  silver  silver  silver  purp    red purp    blue    blue    silver  red purp    teal    purp    red red purp    teal    red teal    silver  teal    teal    purp    teal    teal    blue    blue    silver  silver  silver  silver  silver  silver  0   1176    203938
RandFac 92  15  bgB.jpg silver  silver  silver  silver  silver  silver  silver  red purp    blue    blue    silver  red purp    teal    purp    red red purp    teal    red teal    silver  teal    teal    purp    teal    teal    blue    blue    silver  silver  silver  silver  silver  silver  0   1204    229338
RandFac 92  16  bgB.jpg silver  silver  silver  silver  silver  silver  silver  silver  purp    blue    blue    silver  red purp    teal    purp    red red purp    teal    red teal    silver  teal    teal    purp    teal    teal    blue    blue    silver  silver  silver  silver  silver  silver  0   1095    241689
RandFac 92  36  bgA.jpg silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  0   1516    696759
RandFac 92  37  bgA.jpg silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  0   0   0
RandNo  92  1   bgC.jpg blk blk blk blk blk blk blk blk blk blk blk silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk 0   1340    25909
RandNo  92  2   bgC.jpg silver  blk blk blk blk blk blk blk blk blk blk silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk 0   1600    46693
RandNo  92  3   bgC.jpg silver  blk blk blk blk blk blk blk blk blk blk silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk silver  blk blk blk blk blk 0   982 53821
RandNo  92  4   bgC.jpg silver  blk blk blk blk blk blk blk blk blk blk silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk silver  silver  blk blk blk blk 0   1020    68052
RandNo  92  5   bgC.jpg silver  blk blk blk blk blk blk blk blk blk blk silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk silver  silver  blk silver  blk blk 0   1703    101420
RandNo  92  6   bgC.jpg silver  blk blk blk blk blk blk blk blk blk blk silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk silver  silver  silver  silver  blk blk 0   746 108675
RandNo  92  7   bgC.jpg silver  blk blk blk blk blk blk blk blk blk blk silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk silver  silver  silver  silver  blk silver  0   1341    118699
RandNo  92  8   bgC.jpg silver  blk blk blk blk blk blk blk blk blk blk silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   1194    126731
RandNo  92  9   bgC.jpg silver  silver  blk blk blk blk blk blk blk blk blk silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   766 144995
RandNo  92  10  bgC.jpg silver  silver  silver  blk blk blk blk blk blk blk blk silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   986 154610
RandNo  92  11  bgC.jpg silver  silver  silver  blk silver  blk blk blk blk blk blk silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   838 172970
RandNo  92  12  bgC.jpg silver  silver  silver  silver  silver  blk blk blk blk blk blk silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   1871    181266
RandNo  92  13  bgC.jpg silver  silver  silver  silver  silver  silver  blk blk blk blk blk silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   1711    191418
RandNo  92  14  bgC.jpg silver  silver  silver  silver  silver  silver  silver  blk blk blk blk silver  blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   1087    214865
RandNo  92  15  bgC.jpg silver  silver  silver  silver  silver  silver  silver  blk blk blk blk silver  blk blk blk blk blk blk blk blk blk blk silver  blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   1502    237153
RandNo  92  16  bgC.jpg silver  silver  silver  silver  silver  silver  silver  blk blk blk blk silver  blk blk blk blk blk blk blk blk blk silver  silver  blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   993 260616
RandNo  92  17  bgC.jpg silver  silver  silver  silver  silver  silver  silver  blk silver  blk blk silver  blk blk blk blk blk blk blk blk blk silver  silver  blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   1245    276944
RandNo  92  18  bgC.jpg silver  silver  silver  silver  silver  silver  silver  silver  silver  blk blk silver  blk blk blk blk blk blk blk blk blk silver  silver  blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   1190    285768
RandNo  92  19  bgC.jpg silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  blk silver  blk blk blk blk blk blk blk blk blk silver  silver  blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   1087    297759
RandNo  92  20  bgC.jpg silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  blk silver  silver  blk blk blk blk blk blk blk blk silver  silver  blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   798 319959
RandNo  92  21  bgC.jpg silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  blk blk blk blk blk blk blk blk silver  silver  blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   837 329310
RandNo  92  22  bgB.jpg silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  blk blk blk blk blk blk blk silver  silver  blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   850 348614
RandNo  92  23  bgB.jpg silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  blk blk blk blk blk blk silver  silver  blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   987 360046
RandNo  92  24  bgB.jpg silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  silver  blk blk blk blk blk silver  silver  blk blk blk blk blk blk blk silver  silver  silver  silver  silver  silver  0   626 388621

旧职位： 以下是目前为止的相关代码：

data = pd.read_excel("Data.xlsx")
data.filter(like='color').isin(['silver'])
boolSilver = data.filter(like='color').isin(['silver']).astype(int)

Answer 1

不知道这是不是你想要的，是不是最好的方法。你知道吗

我首先将数据从宽格式转换为长格式，然后利用.shift()找到前一阶段的颜色（滞后值），并检查是否有任何颜色变为银色。你知道吗

import pandas as pd

# Read Data
sample_data = pd.read_csv('...\\sample_data.txt', sep='\t')
colnames = sample_data.columns

# Convert Data From Wide to Long Format
df_long = pd.wide_to_long(sample_data, stubnames='color', i=colnames[0:3], j='order')
df_long = df_long.reset_index()
df_long.order = pd.to_numeric(df_long.order)  # Sorting of str and int variables are different
df_long.sort_values(by=['ExperimentName', 'Subject', 'Trial', 'order'], inplace=True)
df_long['l_ExperimentName'] = df_long.ExperimentName.shift()
df_long['l_Subject'] = df_long.Subject.shift()
df_long['l_Trial'] = df_long.Trial.shift()
df_long['l_color'] = df_long.color.shift()

# Find if there is any colour change to silver within the same trial
df_long['i_silver'] = df_long.color == 'silver'
df_long['i_lag_not_silver'] = df_long.l_color != 'silver'
df_long['i_change_to_silver'] = (df_long.i_silver & 
                                df_long.i_lag_not_silver & 
                                (df_long.ExperimentName == df_long.l_ExperimentName) & 
                                (df_long.Subject == df_long.l_Subject) & 
                                (df_long.Trial == df_long.l_Trial))
df_change_to_silver = df_long.loc[df_long.i_change_to_silver == True, ['ExperimentName', 'Subject', 'order', 'Trial', 'l_color', 'color']]

输出：

# Print the changes
print(df_change_to_silver)

    ExperimentName  Subject  Trial  order l_color   color
206         FacFac       92      3     15    teal  silver
215         FacFac       92      4     15    teal  silver
224         FacFac       92      5     15    teal  silver
57          NumFac       91      2     10     red  silver
66          NumFac       91      3     10     red  silver
75          NumFac       91      4     10     red  silver
84          NumFac       91      5     10     red  silver
226         NumFac       92      1      2    pink  silver
102          NumNo       91      2     10   black  silver
111          NumNo       91      3     10   black  silver
120          NumNo       91      4     10   black  silver
129          NumNo       91      5     10   black  silver
147        RandFac       91      2     10    blue  silver
156        RandFac       91      3     10    blue  silver
165        RandFac       91      4     10    blue  silver
174        RandFac       91      5     10    blue  silver