我有两个要合并的数据帧:
第一个:
In: array = [['RMP1','RMP1','RMP1','Coop','Coop','Coop','RMP2','RMP2','RMP2'],
['1. Varazdin','5.Vipap','11. Brno','1.Varazdin','5.Vipap','11. Brno','1.Varazdin','5.Vipap','11. Brno']]
tuples = list(zip(*array))
index = pd.MultiIndex.from_tuples(tuples, names=['project', 'sample'])
df1 = pd.DataFrame(np.random.rand(9*7).reshape(9,7),
index=index,
columns=['volume_filtered','filtration[date]','DNA_extract[µL]','DNA_isolation[Date]','IC','Sul1','qnrS'])
第二个:
df2:
count mean std min 25% 50% 75% max
project sample gene
IC 1 2 3 4 5 6 7 8
1. Varazdin Sul1 9 10 11 12 13 14 15 16
qnrS 17 18 19 20 21 22 23 24
IC
2. Amstetten Sul1 .
qnrS .
IC .
Coop 3.Cluj Sul1 .
. qnrS .
. .
. IC .
1. Varazdin Sul1 .
qnrS .
IC
2. Amstetten Sul1
qnrS
IC
RMP1 3.Cluj Sul1
qnrS
.
.
.
我加入他们是因为:
df3 = df1.join(df2.unstack(), on=['project','sample'])
这就得到了一个没有层次索引的数据帧(df3)。现在,我可以使用以下内容定义索引:
df3.set_index(['project','sample'], inplace=True)
而且效果很好。
我现在要做的是保留df1的行索引,但在列中添加一个多索引,以便df2中的值将存储在“IC”、“Sul1”和“qnrS”列下。我希望这些专栏以
'genes'
。
编辑:
df2词典:
{'25%': project sample gene
Coop 1. Varazdin IC 895.425000
Sul1 306300.000000
qnrS 842.400000
10. Sabac IC 607.250000
Sul1 172875.000000
qnrS 20135.000000
11. Brno IC 355.050000
Sul1 3202.750000
qnrS 3.261472
12. Zagreb IC 547.400000
Sul1 21332.500000
qnrS 0.005167
2. Amstetten IC 51.267500
Sul1 551.325000
qnrS NaN
3. Cluj IC 885.000000
Sul1 9035.800250
qnrS 6.225000
4. Augsburg IC 258.775000
Sul1 778.625000
qnrS 0.041505
5. Vipap IC 220.900000
Sul1 1196.000000
qnrS 0.370600
6. Budapest IC 389.875000
Sul1 4796.750000
qnrS 45.760000
7.Ljubljana IC 439.875000
Sul1 2398.000000
qnrS NaN
8. Bukarest IC 876.050000
Sul1 259150.000000
qnrS 699.925000
9. Zilina IC 314.875000
Sul1 1081.250000
qnrS NaN
RdG2 Communal WW IC 631.950000
Sul1 320950.000000
qnrS 70.592500
H1 IC 35.400000
Sul1 104250.000000
qnrS 4.773750
H4 IC 782.200000
Sul1 816500.000000
qnrS 37.090000
Pharm.-Inlet IC 0.835000
Sul1 72595.000000
qnrS 0.052330
Pharm.1- MBR IC 662.950000
Sul1 112.160000
qnrS NaN
Pharm.2-Ozone IC 625.450000
Sul1 1250.500000
qnrS 4.416000
Pharm.3-GAC IC 1089.500000
Sul1 1881.500000
qnrS NaN
Pharm.4-UV/Outlet IC 725.900000
Sul1 278.500000
qnrS NaN
WWTP Inlet 24h IC 431.000000
Sul1 180822.500000
qnrS 23.580000
WWTP Outlet 24h IC 437.450000
Sul1 411400.000000
qnrS 215.000000
Name: 25%, dtype: float64,
'50%': project sample gene
Coop 1. Varazdin IC 897.150000
Sul1 312900.000000
qnrS 842.400000
10. Sabac IC 612.600000
Sul1 176850.000000
qnrS 20610.000000
11. Brno IC 356.700000
Sul1 3262.500000
qnrS 6.484315
12. Zagreb IC 568.800000
Sul1 22215.000000
qnrS 0.005167
2. Amstetten IC 57.915000
Sul1 562.450000
qnrS NaN
3. Cluj IC 885.000000
Sul1 18070.533500
qnrS 6.225000
4. Augsburg IC 266.650000
Sul1 801.950000
qnrS 0.050550
5. Vipap IC 222.600000
Sul1 1203.000000
qnrS 0.370600
6. Budapest IC 402.150000
Sul1 4799.500000
qnrS 46.790000
7.Ljubljana IC 441.250000
Sul1 2482.000000
qnrS NaN
8. Bukarest IC 898.800000
Sul1 268500.000000
qnrS 771.350000
9. Zilina IC 321.550000
Sul1 1099.500000
qnrS NaN
RdG2 Communal WW IC 689.500000
Sul1 342700.000000
qnrS 77.735000
H1 IC 40.040000
Sul1 105600.000000
qnrS 5.170500
H4 IC 789.500000
Sul1 864700.000000
qnrS 42.190000
Pharm.-Inlet IC 1.399000
Sul1 73860.000000
qnrS 0.063520
Pharm.1- MBR IC 847.200000
Sul1 144.900000
qnrS NaN
Pharm.2-Ozone IC 689.200000
Sul1 1265.000000
qnrS 4.416000
Pharm.3-GAC IC 1097.000000
Sul1 1921.000000
qnrS NaN
Pharm.4-UV/Outlet IC 740.400000
Sul1 290.000000
qnrS NaN
WWTP Inlet 24h IC 463.700000
Sul1 360300.000000
qnrS 23.580000
WWTP Outlet 24h IC 495.600000
Sul1 435500.000000
qnrS 216.900000
Name: 50%, dtype: float64,
'75%': project sample gene
Coop 1. Varazdin IC 898.875000
Sul1 319500.000000
qnrS 842.400000
10. Sabac IC 617.950000
Sul1 180825.000000
qnrS 21085.000000
11. Brno IC 358.350000
Sul1 3322.250000
qnrS 9.707157
12. Zagreb IC 590.200000
Sul1 23097.500000
qnrS 0.005167
2. Amstetten IC 64.562500
Sul1 573.575000
qnrS NaN
3. Cluj IC 885.000000
Sul1 27105.266750
qnrS 6.225000
4. Augsburg IC 274.525000
Sul1 825.275000
qnrS 0.059595
5. Vipap IC 224.300000
Sul1 1210.000000
qnrS 0.370600
6. Budapest IC 414.425000
Sul1 4802.250000
qnrS 47.820000
7.Ljubljana IC 442.625000
Sul1 2566.000000
qnrS NaN
8. Bukarest IC 921.550000
Sul1 277850.000000
qnrS 842.775000
9. Zilina IC 328.225000
Sul1 1117.750000
qnrS NaN
RdG2 Communal WW IC 704.350000
Sul1 349850.000000
qnrS 84.877500
H1 IC 41.645000
Sul1 108950.000000
qnrS 5.567250
H4 IC 824.600000
Sul1 876450.000000
qnrS 46.760000
Pharm.-Inlet IC 1.963000
Sul1 75290.000000
qnrS 0.106010
Pharm.1- MBR IC 896.800000
Sul1 147.100000
qnrS NaN
Pharm.2-Ozone IC 719.700000
Sul1 238832.500000
qnrS 4.416000
Pharm.3-GAC IC 1126.500000
Sul1 1980.500000
qnrS NaN
Pharm.4-UV/Outlet IC 747.900000
Sul1 305.700000
qnrS NaN
WWTP Inlet 24h IC 618.450000
Sul1 404100.000000
qnrS 23.580000
WWTP Outlet 24h IC 503.300000
Sul1 440200.000000
qnrS 220.400000
Name: 75%, dtype: float64,
'count': project sample gene
Coop 1. Varazdin IC 2.0
Sul1 2.0
qnrS 1.0
10. Sabac IC 2.0
Sul1 2.0
qnrS 2.0
11. Brno IC 2.0
Sul1 2.0
qnrS 2.0
12. Zagreb IC 2.0
Sul1 2.0
qnrS 1.0
2. Amstetten IC 2.0
Sul1 2.0
qnrS 0.0
3. Cluj IC 1.0
Sul1 2.0
qnrS 1.0
4. Augsburg IC 2.0
Sul1 2.0
qnrS 2.0
5. Vipap IC 2.0
Sul1 2.0
qnrS 1.0
6. Budapest IC 2.0
Sul1 2.0
qnrS 2.0
7.Ljubljana IC 2.0
Sul1 2.0
qnrS 0.0
8. Bukarest IC 2.0
Sul1 2.0
qnrS 2.0
9. Zilina IC 2.0
Sul1 2.0
qnrS 0.0
RdG2 Communal WW IC 3.0
Sul1 3.0
qnrS 2.0
H1 IC 3.0
Sul1 3.0
qnrS 2.0
H4 IC 3.0
Sul1 3.0
qnrS 3.0
Pharm.-Inlet IC 2.0
Sul1 3.0
qnrS 3.0
Pharm.1- MBR IC 3.0
Sul1 3.0
qnrS 0.0
Pharm.2-Ozone IC 3.0
Sul1 3.0
qnrS 1.0
Pharm.3-GAC IC 3.0
Sul1 3.0
qnrS 0.0
Pharm.4-UV/Outlet IC 3.0
Sul1 3.0
qnrS 0.0
WWTP Inlet 24h IC 3.0
Sul1 3.0
qnrS 1.0
WWTP Outlet 24h IC 3.0
Sul1 3.0
qnrS 3.0
Name: count, dtype: float64,
'max': project sample gene
Coop 1. Varazdin IC 900.600000
Sul1 326100.000000
qnrS 842.400000
10. Sabac IC 623.300000
Sul1 184800.000000
qnrS 21560.000000
11. Brno IC 360.000000
Sul1 3382.000000
qnrS 12.930000
12. Zagreb IC 611.600000
Sul1 23980.000000
qnrS 0.005167
2. Amstetten IC 71.210000
Sul1 584.700000
qnrS NaN
3. Cluj IC 885.000000
Sul1 36140.000000
qnrS 6.225000
4. Augsburg IC 282.400000
Sul1 848.600000
qnrS 0.068640
5. Vipap IC 226.000000
Sul1 1217.000000
qnrS 0.370600
6. Budapest IC 426.700000
Sul1 4805.000000
qnrS 48.850000
7.Ljubljana IC 444.000000
Sul1 2650.000000
qnrS NaN
8. Bukarest IC 944.300000
Sul1 287200.000000
qnrS 914.200000
9. Zilina IC 334.900000
Sul1 1136.000000
qnrS NaN
RdG2 Communal WW IC 719.200000
Sul1 357000.000000
qnrS 92.020000
H1 IC 43.250000
Sul1 112300.000000
qnrS 5.964000
H4 IC 859.700000
Sul1 888200.000000
qnrS 51.330000
Pharm.-Inlet IC 2.527000
Sul1 76720.000000
qnrS 0.148500
Pharm.1- MBR IC 946.400000
Sul1 149.300000
qnrS NaN
Pharm.2-Ozone IC 750.200000
Sul1 476400.000000
qnrS 4.416000
Pharm.3-GAC IC 1156.000000
Sul1 2040.000000
qnrS NaN
Pharm.4-UV/Outlet IC 755.400000
Sul1 321.400000
qnrS NaN
WWTP Inlet 24h IC 773.200000
Sul1 447900.000000
qnrS 23.580000
WWTP Outlet 24h IC 511.000000
Sul1 444900.000000
qnrS 223.900000
Name: max, dtype: float64,
'mean': project sample gene
Coop 1. Varazdin IC 897.150000
Sul1 312900.000000
qnrS 842.400000
10. Sabac IC 612.600000
Sul1 176850.000000
qnrS 20610.000000
11. Brno IC 356.700000
Sul1 3262.500000
qnrS 6.484315
12. Zagreb IC 568.800000
Sul1 22215.000000
qnrS 0.005167
2. Amstetten IC 57.915000
Sul1 562.450000
qnrS NaN
3. Cluj IC 885.000000
Sul1 18070.533500
qnrS 6.225000
4. Augsburg IC 266.650000
Sul1 801.950000
qnrS 0.050550
5. Vipap IC 222.600000
Sul1 1203.000000
qnrS 0.370600
6. Budapest IC 402.150000
Sul1 4799.500000
qnrS 46.790000
7.Ljubljana IC 441.250000
Sul1 2482.000000
qnrS NaN
8. Bukarest IC 898.800000
Sul1 268500.000000
qnrS 771.350000
9. Zilina IC 321.550000
Sul1 1099.500000
qnrS NaN
RdG2 Communal WW IC 661.033333
Sul1 332966.666667
qnrS 77.735000
H1 IC 38.016667
Sul1 106933.333333
qnrS 5.170500
H4 IC 808.033333
Sul1 840400.000000
qnrS 41.836667
Pharm.-Inlet IC 1.399000
Sul1 73970.000000
qnrS 0.084387
Pharm.1- MBR IC 757.433333
Sul1 124.540000
qnrS NaN
Pharm.2-Ozone IC 667.033333
Sul1 159633.666667
qnrS 4.416000
Pharm.3-GAC IC 1111.666667
Sul1 1934.333333
qnrS NaN
Pharm.4-UV/Outlet IC 735.733333
Sul1 292.800000
qnrS NaN
WWTP Inlet 24h IC 545.066667
Sul1 269848.333333
qnrS 23.580000
WWTP Outlet 24h IC 461.966667
Sul1 422566.666667
qnrS 217.966667
Name: mean, dtype: float64,
'min': project sample gene
Coop 1. Varazdin IC 893.700000
Sul1 299700.000000
qnrS 842.400000
10. Sabac IC 601.900000
Sul1 168900.000000
qnrS 19660.000000
11. Brno IC 353.400000
Sul1 3143.000000
qnrS 0.038630
12. Zagreb IC 526.000000
Sul1 20450.000000
qnrS 0.005167
2. Amstetten IC 44.620000
Sul1 540.200000
qnrS NaN
3. Cluj IC 885.000000
Sul1 1.067000
qnrS 6.225000
4. Augsburg IC 250.900000
Sul1 755.300000
qnrS 0.032460
5. Vipap IC 219.200000
Sul1 1189.000000
qnrS 0.370600
6. Budapest IC 377.600000
Sul1 4794.000000
qnrS 44.730000
7.Ljubljana IC 438.500000
Sul1 2314.000000
qnrS NaN
8. Bukarest IC 853.300000
Sul1 249800.000000
qnrS 628.500000
9. Zilina IC 308.200000
Sul1 1063.000000
qnrS NaN
RdG2 Communal WW IC 574.400000
Sul1 299200.000000
qnrS 63.450000
H1 IC 30.760000
Sul1 102900.000000
qnrS 4.377000
H4 IC 774.900000
Sul1 768300.000000
qnrS 31.990000
Pharm.-Inlet IC 0.271000
Sul1 71330.000000
qnrS 0.041140
Pharm.1- MBR IC 478.700000
Sul1 79.420000
qnrS NaN
Pharm.2-Ozone IC 561.700000
Sul1 1236.000000
qnrS 4.416000
Pharm.3-GAC IC 1082.000000
Sul1 1842.000000
qnrS NaN
Pharm.4-UV/Outlet IC 711.400000
Sul1 267.000000
qnrS NaN
WWTP Inlet 24h IC 398.300000
Sul1 1345.000000
qnrS 23.580000
WWTP Outlet 24h IC 379.300000
Sul1 387300.000000
qnrS 213.100000
Name: min, dtype: float64,
'std': project sample gene
Coop 1. Varazdin IC 4.879037
Sul1 18667.619023
qnrS NaN
10. Sabac IC 15.132085
Sul1 11242.997821
qnrS 1343.502884
11. Brno IC 4.666905
Sul1 168.998521
qnrS 9.115575
12. Zagreb IC 60.528340
Sul1 2496.086938
qnrS NaN
Name: std, dtype: float64}
