时间戳

首页 > 解决方案 > 熊猫有两个数据框，想要每组之间的平均分

python - 熊猫有两个数据框，想要每组之间的平均分

问题描述

我有一个这样的数据框：

dataA = [["A1", "t1", 5], ["A1", "t2", 8], ["A1", "t3", 7],
    ["A1","t4", 4], ["A1", "t5", 2], ["A1", "t6", 2],
    ["A2", "t1", 15], ["A2", "t2", 6], ["A2", "t3", 1], 
    ["A2", "t4", 11], ["A2", "t5", 12], ["A2", "t6", 7], 
    ["A3", "t1", 12], ["A3", "t2", 8], ["A3", "t3", 3], 
    ["A3", "t4", 7], ["A3", "t5", 15], ["A3", "t6", 14]]

dataB = [["B1", "t1", 2], ["B1", "t2", 9], ["B1", "t3", 17],
    ["B1","t4", 14], ["B1", "t5", 32], ["B1", "t6", 3],
    ["B2", "t1", 44], ["B2", "t2", 36], ["B2", "t3", 51], 
    ["B2", "t4", 81], ["B2", "t5", 82]]

data1 = pd.DataFrame(data = dataA, columns=["An", "colA", "Val"])

data2 = pd.DataFrame(data = dataB, columns=["Bm", "colA", "Val"])

如何得到这个结果：

 GroupA | GroupB| result | 
 ---------------------------
| A1    | B1    | val_11 | 
 --------------------------
| A1    | B2    | val_12 | 
 --------------------------
| A2    | B1    | val_21 | 
 --------------------------
| A2    | B2    | val_22 | 
 --------------------------
| A3    | B1    | val_31 |
 --------------------------
| A3    | B2    | val_32 |

...........................

| An    | Bm    | val_nm  |

val_nm的计算方式如下：val_11等于A1的列值除以B1的列值的列均值，注意A1列除以B1列，对应的个数除以结果，如果它大于1，取倒数，然后求结果的平均值 所以无论是A1除以B1还是B1除以A1，结果值必须相同。

为了计算val，可能需要定义一个函数，val大于0，不会被0除

我以 val_11 为例

A1[5,8,7,4,2,2] B1[2,9,17,14,32,3]

val_11 =avg (A1/B1) =avg( 5/2 取 2/5 + 8/9 +7/17 + 4/15 +2/32 +2/3)

= 0.4525

所以无论A1/B1还是B1/A1，结果都是一样的

请帮我计算结果

标签： pythonpandasdataframe

解决方案

直接定义您要计算的内容

  • 首先形状数据框，数据是键/值对，使用创建表pivot()
  • merge()在合成列foo上的两个表之间做笛卡尔积
  • 完成您指定的计算
  • 过滤列以获得所需的输出
def meanofdiv(dfa):
    a = dfa.loc[:,[c for c in dfa.columns if "_A" in c]].values 
    b = dfa.loc[:,[c for c in dfa.columns if "_B" in c]].values
    return np.where((a/b)>1, b/a, a/b).mean(axis=1)

# pivot key/val pair data to tables
# caretesian product of tables
# simple calculation of columns from A and a column from B
dfr = pd.merge(
    data1.pivot(index="An", columns="colA", values="Val").reset_index().assign(foo=1),
    data2.pivot(index="Bm", columns="colA", values="Val").reset_index().assign(foo=1),
    on="foo",
    suffixes=("_A","_B")
).assign(resname=lambda dfa: dfa["An"]+dfa["Bm"],
        res=meanofdiv)

dfr.loc[:,["An","Bm","res"]]
一个 BM 资源
0 A1 B1 0.452589
1 A1 B2 0.202259
2 A2 B1 0.408018
3 A2 B2 0.206316
4 A3 B1 0.40251
5 A3 B2 0.172901

参差不齐的数据集

  • 这涉及长度不同的 A 和 B 集合，并在最后 B 观察时停止计算
  • 改为逐行apply(axis=1)
  • 通过查看B中的NaN将数组修改为相同大小
def meanofdiv(dfa):
    dfa = dfa.to_frame().T
    a = dfa.loc[:,[c for c in dfa.columns if "_A" in c]].astype(float).values[0] 
    b = dfa.loc[:,[c for c in dfa.columns if "_B" in c]].astype(float).values[0]
    a = a[~np.isnan(b)]
    b = b[~np.isnan(b)]
    return np.where((a/b)>1, b/a, a/b).mean()

# pivot key/val pair data to tables
# caretesian product of tables
# simple calculation of columns from A and a column from B
dfr = pd.merge(
    data1.pivot(index="An", columns="colA", values="Val").reset_index().assign(foo=1),
    data2.pivot(index="Bm", columns="colA", values="Val").reset_index().assign(foo=1),
    on="foo",
    suffixes=("_A","_B")
).assign(resname=lambda dfa: dfa["An"]+dfa["Bm"],
        res=lambda dfa: dfa.apply(meanofdiv, axis=1))

推荐阅读