你可以使用pd.wide_to_long。你只需要重命名你的列来匹配格式（A_Color -〉Color_A）

>>> (pd.wide_to_long(df.rename(columns=lambda x: '_'.join(x.split('_')[::-1])), 
                     stubnames=['Color', 'Size'], i='Category', j='Cat', 
                     sep='_', suffix='\w+')
       .query('Category == Cat').droplevel('Cat').reset_index())

  Category Color  Size
0        A   Red  50.0
1        B  Blue  60.0

详细信息：

# Rename columns
>>> df1 = df.rename(columns=lambda x: '_'.join(x.split('_')[::-1]))
  Category Color_A Color_B  Size_A  Size_B  # <- HERE
0        A     Red     NaN    50.0     NaN
1        B     NaN    Blue     NaN    60.0

# Reshape dataframe
>>> out = pd.wide_to_long(df1, stubnames=['Color', 'Size'], i='Category', j='Cat', sep='_', suffix='\w+')
             Color  Size
Category Cat            
A        A     Red  50.0  # Keep
B        A     NaN   NaN  # Drop
A        B     NaN   NaN  # Drop
B        B    Blue  60.0  # Keep

# Filter rows
>>> out = out.query('Category == Cat')
             Color  Size
Category Cat            
A        A     Red  50.0
B        B    Blue  60.0

# Get final dataframe
>>> out = out.droplevel('Cat').reset_index()
  Category Color  Size
0        A   Red  50.0
1        B  Blue  60.0

一个想法是通过使用_拆分列来获得第一个非缺失值：

df1 = (df.set_index('Category')
         .groupby(lambda x: x.split('_')[-1], axis=1)
         .first()
         .reset_index())
print (df1)
  Category Color  Size
0        A   Red  50.0
1        B  Blue  60.0

使用lookup的解决方案：

categories = ['Size','Color']

for c in categories:
    idx, cols = pd.factorize(df['Category'].add(f'_{c}'))
    df[c] = df.reindex(cols, axis=1).to_numpy()[np.arange(len(df)), idx]

您的解决方案应该通过转换为numpy数组来更改：

for b in bins:
    df.loc[df['Category'] == b, ['Color', 'Size']] = \
        df.loc[df['Category'] == b, [f'{b}_Color', f'{b}_Size']].to_numpy()