问在Python中基于交集合并两个表

EN

from collections import defaultdict
A = [
    ('A', 5, 3),
    ('B', 6, 1),
    ('D', 10, 2),
    ('C', 2, 4),
]

B = [
    ('A', 5, 5),
    ('D', 2, 1),
    ('B', 5, 4),
]

# we need to calculate (value, quantity) for each label:
a = {key: [weight * quantity, quantity] for key, weight, quantity in A}
b = {key: [weight * quantity, quantity] for key, weight, quantity in B}

# defaultdict is a dictionary like structure, but able to create 
# a new item if key is not found, in our case a new [0, 0] list:
merged = defaultdict(lambda: list((0, 0)))

# let's sum quantities and 
for key, pair in a.items() + b.items():
    # add both value and quantity respectively
    quantity, value = map(sum, zip(merged[key], pair))
    merged[key] = [quantity, value]

# now let's calculate means
for key, (quantity, value) in merged.items():
    mean = quantity / float(value)
    merged[key] = [mean, value]


for item in merged.items():
    print item

import pandas as pd

# first let's create dataframes
colnames = 'label weight quantity'.split()
A = pd.DataFrame.from_records([
        ('A', 5, 3),
        ('B', 6, 1),
        ('D', 10, 2),
        ('C', 2, 4),
    ], columns=colnames)

B = pd.DataFrame.from_records([
        ('A', 5, 2),
        ('D', 2, 1),
        ('B', 5, 4),
    ], columns=colnames)

# we can just concatenate those DataFrames and do calculation:
df = pd.concat([A, B])
df['value'] = df.weight * df.quantity

# sum each group with the same label
df = df.groupby('label').sum()
del df['weight']  # it got messed up anyway and we don't need it

# and calculate means:
df['mean'] = df.value / df.quantity

print df
print(df[['mean', 'quantity']])
#            mean  quantity
# label
# A      5.000000         5
# B      5.200000         5
# C      2.000000         4
# D      7.333333         3

In [1]: import pandas as pd
In [2]: import numpy as np
In [3]: df1 = pd.DataFrame({'AAA':np.array([5,3]),'BBB':np.array([6,1]),
 .....: 'DDD':np.array([10,2]),'CCC':np.array([2,4])})
In [4]: df2 = pd.DataFrame({'AAA':np.array([5,2]),'DDD':np.array([2,1]),
 .....: 'BBB':np.array([5,4])})
In [5]: df = pd.concat([df1,df2])
In [6]: df.transpose()
      0  1   0   1
AAA   5  3   5   2
BBB   6  1   5   4 
CCC   2  4 NaN NaN
DDD  10  2   2   1
In [7]: vals = np.nan_to_num(df.values)
In [8]: _mean = (vals[0,:]*vals[1,:]+vals[2,:]*vals[3,:])/(vals[1,:]+vals[3,:])
In [9]: _sum = (vals[1,:]+vals[3,:])
In [10]: result = pd.DataFrame(columns = df.columns,data = [_mean,_sum], index=['mean','sum'])
In [11]: result.transpose()
         mean  sum
AAA  5.000000    5
BBB  5.200000    5
CCC  2.000000    4
DDD  7.333333    3