您可以使用：

# dic = {np.int64: 'NUM', object: 'STR'}
(df.set_index('FIELD_A')
   .pipe(lambda d: d.set_axis(pd.MultiIndex.from_arrays(
          [d.columns, d.dtypes],
         # or for custom NAMES
         #[d.columns, d.dtypes.map(dic)],
                              names=['FIELD_NAME', None]),
                              axis=1)
        )
   .stack(0).add_prefix('FIELD_').add_suffix('_VALUE')
   .reset_index()
)

• 注意：如果你真的想要STR/NUM，map那些来自dtype的字符串（参见代码中的注解）。*

输出：

FIELD_A FIELD_NAME  FIELD_int64_VALUE FIELD_object_VALUE
0   123123    FIELD_B                8.0                NaN
1   123123    FIELD_C                NaN                  a
2   123123    FIELD_D            23423.0                NaN
3   123124    FIELD_B                7.0                NaN
4   123124    FIELD_C                NaN                  c
5   123124    FIELD_D             6464.0                NaN
6   123144    FIELD_B               99.0                NaN
7   123144    FIELD_C                NaN                  x
8   123144    FIELD_D              234.0                NaN

你也可以试试这个：

(df.melt('FIELD_A')
 .pipe(lambda d: d[['FIELD_A', 'value']].join(d.variable.str.extract('\w+_(?P<FIELD_NAME>\w+)')))
 .pipe(lambda g: g[['FIELD_A', 'FIELD_NAME']].join(g.value.astype(str).str.extract('(?P<FIELD_STR_VALUE>\D+)|(?P<FIELD_NUM_VALUE>\d+)')))
 .sort_values('FIELD_A'))
   FIELD_A FIELD_NAME FIELD_STR_VALUE FIELD_NUM_VALUE
0   123123          B             NaN               8
3   123123          C               a             NaN
6   123123          D             NaN           23423
1   123124          B             NaN               7
4   123124          C               c             NaN
7   123124          D             NaN            6464
2   123144          B             NaN              99
5   123144          C               x             NaN
8   123144          D             NaN             234

df.melt(id_vars='FIELD_A', var_name='FIELD_NAME', value_name='FIELD_VALUE').sort_values(by='FIELD_A').reset_index(drop=True)

产出

FIELD_A FIELD_NAME  FIELD_VALUE
0   123123  FIELD_B     8
1   123123  FIELD_C     a
2   123123  FIELD_D     23423
3   123124  FIELD_B     7
4   123124  FIELD_C     c
5   123124  FIELD_D     6464
6   123144  FIELD_B     99
7   123144  FIELD_C     x
8   123144  FIELD_D     234

我希望这有助于实现您想要的输出！
我们可以进一步延伸：

(df
.melt(
    id_vars='FIELD_A', 
    var_name='FIELD_NAME', 
    value_name='FIELD_VALUE')
.assign(
    FIELD_NAME = lambda df: df.FIELD_NAME.str[-1], 
    FIELD_NUM_VALUE = lambda df: df.FIELD_VALUE.where(df.FIELD_VALUE.map(type) == int),
     FIELD_STR_VALUE = lambda df: np.where(df.FIELD_NUM_VALUE.isna(), df.FIELD_VALUE, np.nan))
    .drop(columns='FIELD_VALUE')
    .sort_values(by='FIELD_A', ignore_index = True)
)
   FIELD_A FIELD_NAME FIELD_NUM_VALUE FIELD_STR_VALUE
0   123123          B               8             NaN
1   123123          C             NaN               a
2   123123          D           23423             NaN
3   123124          B               7             NaN
4   123124          C             NaN               c
5   123124          D            6464             NaN
6   123144          B              99             NaN
7   123144          C             NaN               x
8   123144          D             234             NaN

使用pd.Series.str.extract的另一个选项：

temp = df.melt(id_vars='FIELD_A', var_name='FIELD_NAME', value_name='FIELD_VALUE')
temp = temp.assign(FIELD_NAME = lambda df: df.FIELD_NAME.str[-1])
regex = r"(?P<FIELD_STR_VALUE>\D+)|(?P<FIELD_NUM_VALUE>\d+)"
extract = temp.FIELD_VALUE.astype(str).str.extract(regex)
temp.drop(columns='FIELD_VALUE').assign(**extract)
   FIELD_A FIELD_NAME FIELD_STR_VALUE FIELD_NUM_VALUE
0   123123          B             NaN               8
1   123124          B             NaN               7
2   123144          B             NaN              99
3   123123          C               a             NaN
4   123124          C               c             NaN
5   123144          C               x             NaN
6   123123          D             NaN           23423
7   123124          D             NaN            6464
8   123144          D             NaN             234