如何将两种化学品的透视表合并为符合实验需求的单一透视表？

我正在为实验制备两种化学品的浓度组合，希望通过透视表清晰展示不同浓度组合下各自的添加体积，以此筛选出最优组合。目前已分别生成两种化学品的透视表/DataFrame，但无法合并成符合预期的透视表。

尝试代码 #

import numpy as np
import pandas as pd


array_CinA = np.array([0,125,250,500,1000])
array_Aceto = np.array([0,100,200,400,800])
vol_cina = [0, 5, 10, 20, 40]
vol_as = [0, 2, 4,8,16]

array = np.array(np.meshgrid(array_CinA,array_Aceto)).T.reshape(-1,2)

df = pd.DataFrame({"CinnamonicAcid":array_CinA,
                   "Acetosyringone":array_Aceto,
                   "VolCinA": vol_cina,
                   "VolAS": vol_as})

pivtab = df.pivot_table(index="CinnamonicAcid", columns="Acetosyringone", values=["VolCinA", "VolAS"])
#pivtab.to_excel
print(pivtab)

当前输出 #

VolAS                      VolCinA                       
Acetosyringone   0    100  200  400   800     0    100   200   400   800
CinnamonicAcid                                                          
0                0.0  NaN  NaN  NaN   NaN     0.0  NaN   NaN   NaN   NaN
125              NaN  2.0  NaN  NaN   NaN     NaN  5.0   NaN   NaN   NaN
250              NaN  NaN  4.0  NaN   NaN     NaN  NaN  10.0   NaN   NaN
500              NaN  NaN  NaN  8.0   NaN     NaN  NaN   NaN  20.0   NaN
1000             NaN  NaN  NaN  NaN  16.0     NaN  NaN   NaN   NaN  40.0

期望输出 #

期望得到无NaN值的完整透视表，每个肉桂酸与乙酰丁香酮的浓度组合都对应各自的添加体积，结构为行是肉桂酸浓度，列是乙酰丁香酮浓度，每个浓度组合下同时展示两种化学品的添加体积。

问题出在DataFrame的构造阶段：直接用array_CinA和array_Aceto作为列值，仅生成了5行一对一的浓度匹配，而非所有25种组合。虽然用meshgrid生成了全组合，但未用于构造DataFrame。

修正代码 #

import numpy as np
import pandas as pd

array_CinA = np.array([0,125,250,500,1000])
array_Aceto = np.array([0,100,200,400,800])
vol_cina = [0, 5, 10, 20, 40]
vol_as = [0, 2, 4,8,16]

# 生成所有浓度组合网格
cina_grid, aceto_grid = np.meshgrid(array_CinA, array_Aceto)

# 生成对应体积的网格：每个浓度对应固定体积，扩展为全组合匹配
vol_cina_grid = np.tile(vol_cina, (len(array_Aceto), 1))
vol_as_grid = np.tile(vol_as, (len(array_CinA), 1)).T

# 构造包含所有25种组合的DataFrame
df = pd.DataFrame({
    "CinnamonicAcid": cina_grid.flatten(),
    "Acetosyringone": aceto_grid.flatten(),
    "VolCinA": vol_cina_grid.flatten(),
    "VolAS": vol_as_grid.flatten()
})

# 生成透视表并调整列层级，让每个乙酰丁香酮浓度下同时显示两种体积
pivtab = df.pivot(index="CinnamonicAcid", columns="Acetosyringone", values=["VolCinA", "VolAS"])
pivtab = pivtab.swaplevel(axis=1).sort_index(axis=1)

print(pivtab)

修正后输出 #

Acetosyringone       0              100            200            400            800           
               VolAS VolCinA VolAS VolCinA VolAS VolCinA VolAS VolCinA VolAS VolCinA
CinnamonicAcid                                                                      
0                  0       0     0       0     0       0     0       0     0       0
125                2       5     2       5     2       5     2       5     2       5
250                4      10     4      10     4      10     4      10     4      10
500                8      20     8      20     8      20     8      20     8      20
1000              16      40    16      40    16      40    16      40    16      40

可选：合并体积为单元格字符串 #

如果需要每个单元格直接显示两种体积的组合文本，可添加以下代码：

# 合并每个浓度组合下的体积为字符串
df["Volume"] = df.apply(lambda row: f"VolCinA: {row['VolCinA']}, VolAS: {row['VolAS']}", axis=1)
pivtab_combined = df.pivot(index="CinnamonicAcid", columns="Acetosyringone", values="Volume")
print(pivtab_combined)

合并后输出

Acetosyringone                0                          100                        200                        400                        800
CinnamonicAcid                                                                                                                              
0                VolCinA: 0, VolAS: 0  VolCinA: 0, VolAS: 0  VolCinA: 0, VolAS: 0  VolCinA: 0, VolAS: 0  VolCinA: 0, VolAS: 0
125              VolCinA: 5, VolAS: 2  VolCinA: 5, VolAS: 2  VolCinA: 5, VolAS: 2  VolCinA: 5, VolAS: 2  VolCinA: 5, VolAS: 2
250             VolCinA: 10, VolAS: 4 VolCinA: 10, VolAS: 4 VolCinA: 10, VolAS: 4 VolCinA: 10, VolAS: 4 VolCinA: 10, VolAS: 4
500             VolCinA: 20, VolAS: 8 VolCinA: 20, VolAS: 8 VolCinA: 20, VolAS: 8 VolCinA: 20, VolAS: 8 VolCinA: 20, VolAS: 8
1000           VolCinA: 40, VolAS: 16 VolCinA: 40, VolAS: 16 VolCinA: 40, VolAS: 16 VolCinA: 40, VolAS: 16 VolCinA: 40, VolAS: 16

内容的提问来源于stack exchange，提问作者Teigsta