pandas groupby聚合来自另一个datafram

Question

在使用多个数据帧时，我在使用groupby和aggregation时遇到问题。我试图从两个不同的数据帧计算num_maint_over_$90。在

cars_dict = {"ABC123": ["Ford", "Compact_Car"], "XYZ148": ["Chevy", "Truck"], "ASX133": ["Ford", "Truck"], "ADS111": ["Porsche", "Sports_Car"], "SSC119": ["Toyota", "Compact_Car"]}
cars = pd.DataFrame.from_dict(cars_dict, orient = 'index')
cars.columns = ["Manufacturer", "Type"]
cars.index.rename("License_Plate", inplace = True)

maintenance_dict = {"License_Plate": ["ABC123", "ABC123", "ABC123", "XYZ148", "ASX133", "ASX133", "ADS111", "ADS111", "SSC119"], "Cost": [60, 100, 200, 150, 40, 199, 33, 99, 0]}
maintenance_records = pd.DataFrame.from_dict(maintenance_dict)
maintenance_records.index.rename("order_num", inplace = True)

*汽车：

^{pr2}$

*维护记录：

           Cost License_Plate
order_num
0            60        ABC123
1           100        ABC123
2           200        ABC123
3           150        XYZ148
4            40        ASX133
5           199        ASX133
6            33        ADS111
7            99        ADS111
8             0        SSC119

*所需数据框：

Type         num_maint_over_$90
Compact_Car  2
Sports_Car   1
Truck        2

我试过使用groupby、apply()、和{}。在

Answer 1

merged = pd.merge(maintenance_records, cars, how='left',
                  left_on='License_Plate', right_index=True)
merged.query('Cost > 90')['Type'].value_counts()

Answer 2

下面是简单的for循环解决方案：

car_types = {}

for index, row in cars.iterrows():
    car_type = row["Type"]

    if car_type not in car_types:
        car_types[car_type] = 0

for index, row in maintenance_records.iterrows():

    if row["Cost"] > 90:
        car_license = row["License_Plate"]
        car_type = cars.loc[car_license,"Type"]
        car_types[car_type] += 1

df = pd.DataFrame.from_dict(car_types, orient = "index")
df.index.rename("Type", inplace = True)
df.columns = ["num_maint_over_$90"]

*df:

^{pr2}$