这是an earlier question I had asked a little over a year ago年后的一点后续
我有一个"主"数据框，其中有许多material 的产品代码和名称以及它们的每月消耗量.类似于:
| product code | Name | Consumption A | Consumption B | Consumption C | Consumption D |
|---|---|---|---|---|---|
| 123 | AA | 100 | 120 | 130 | 140 |
| 456 | BB | 5 | 7 | 9 | 11 |
| 789 | CC | 12 | 5 | 33 | 89 |
| 134 | AD | 4 | 17 | 37 | 57 |
| 467 | BD | 1 | 3 | 5 | 7 |
| 179 | ED | 6 | 19 | 30 | 61 |
| 426 | FD | 8 | 5 | 2 | 13 |
我还有另一个表，其中列出了一些产品代码，这些代码是"替代"或替代产品，例如:
| Product Code | Alt Code |
|---|---|
| 123 | 134 |
| 123 | 179 |
| 123 | 426 |
| 456 | 467 |
(与之前的不同之处在于，现在，同一个"产品码"可以有多个不同的"Alt码"S)
我如何使用第二个数据帧来处理第一个数据帧，使其变为:
| product code | Name | Consumption A | Consumption B | Consumption C | Consumption D |
|---|---|---|---|---|---|
| 123 / 134 / 179 / 426 | AA / AD / ED / FD | 118 | 161 | 199 | 271 |
| 456 / 467 | BB / BD | 6 | 10 | 14 | 18 |
| 789 | CC | 12 | 5 | 33 | 89 |
其中，产品代码和名称已连接到同一单元格中，数量相加，并删除了替代方案的"重复项"？
与上次不同的是，现在有一些地方，一个"主"代码有多个备选代码，但它们将始终与主代码一起列出在第一列，备选代码在"alt code"列.理想情况下，我希望使用相同的代码将所有可选行合并为一个单独的行.
这是我一直在try 使用的代码，基于我上次得到的答案:
if alt_name != "":
altf = pd.read_excel(io=alt_name)
group = df['Material'].map(lambda x: altf.set_index('Material')['Alt Material'].get(x, x))
d = {c: 'sum' for c in df.columns}
out = (df
.astype({'Material': str})
.groupby([group], as_index=False)
.agg({**d, **{'Material': ' / '.join, 'Description': ' / '.join}})
)
df = out
ALT_NAME是包含列的EXCEL表格的名称，另存为"material "和"替代material ".
当我try 使用具有多个备选方案的表来运行它时，我得到以下错误:
Traceback (most recent call last):
File "/Users/[Location]/function_code_v2.py", line 285, in <module>
trendcharts(file_name, zone, combinezones, month, numMonths, percent_wanted, trendgen, alt_name, save_directory)
File "/Users/[Location]/function_code_v2.py", line 106, in trendcharts
.agg({**d, **{'Material': ' / '.join, 'Description': ' / '.join}})
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/[Location]/.venv/lib/python3.12/site-packages/pandas/core/groupby/generic.py", line 1445, in aggregate
result = op.agg()
^^^^^^^^
File "/Users/[Location]/.venv/lib/python3.12/site-packages/pandas/core/apply.py", line 175, in agg
return self.agg_dict_like()
^^^^^^^^^^^^^^^^^^^^
File "/Users/[Location]/.venv/lib/python3.12/site-packages/pandas/core/apply.py", line 406, in agg_dict_like
return self.agg_or_apply_dict_like(op_name="agg")
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/[Location]/.venv/lib/python3.12/site-packages/pandas/core/apply.py", line 1388, in agg_or_apply_dict_like
result_index, result_data = self.compute_dict_like(
^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/[Location]/.venv/lib/python3.12/site-packages/pandas/core/apply.py", line 480, in compute_dict_like
getattr(obj._gotitem(key, ndim=1), op_name)(how, **kwargs)
File "/Users/[Location]/.venv/lib/python3.12/site-packages/pandas/core/groupby/generic.py", line 275, in aggregate
if self.ngroups == 0:
^^^^^^^^^^^^
File "/Users/[Location]/.venv/lib/python3.12/site-packages/pandas/core/groupby/groupby.py", line 825, in ngroups
return self.grouper.ngroups
^^^^^^^^^^^^^^^^^^^^
File "properties.pyx", line 36, in pandas._libs.properties.CachedProperty.__get__
File "/Users/[Location]/.venv/lib/python3.12/site-packages/pandas/core/groupby/ops.py", line 758, in ngroups
return len(self.result_index)
^^^^^^^^^^^^^^^^^
File "properties.pyx", line 36, in pandas._libs.properties.CachedProperty.__get__
File "/Users/[Location]/.venv/lib/python3.12/site-packages/pandas/core/groupby/ops.py", line 769, in result_index
return self.groupings[0].result_index.rename(self.names[0])
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "properties.pyx", line 36, in pandas._libs.properties.CachedProperty.__get__
File "/Users/[Location]/.venv/lib/python3.12/site-packages/pandas/core/groupby/grouper.py", line 718, in result_index
return self.group_index
^^^^^^^^^^^^^^^^
File "properties.pyx", line 36, in pandas._libs.properties.CachedProperty.__get__
File "/Users/[Location]/.venv/lib/python3.12/site-packages/pandas/core/groupby/grouper.py", line 722, in group_index
codes, uniques = self._codes_and_uniques
^^^^^^^^^^^^^^^^^^^^^^^
File "properties.pyx", line 36, in pandas._libs.properties.CachedProperty.__get__
File "/Users/[Location]/.venv/lib/python3.12/site-packages/pandas/core/groupby/grouper.py", line 801, in _codes_and_uniques
codes, uniques = algorithms.factorize( # type: ignore[assignment]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/[Location]/.venv/lib/python3.12/site-packages/pandas/core/algorithms.py", line 795, in factorize
codes, uniques = factorize_array(
^^^^^^^^^^^^^^^^
File "/Users/[Location]/.venv/lib/python3.12/site-packages/pandas/core/algorithms.py", line 595, in factorize_array
uniques, codes = table.factorize(
^^^^^^^^^^^^^^^^
File "pandas/_libs/hashtable_class_helper.pxi", line 7280, in pandas._libs.hashtable.PyObjectHashTable.factorize
File "pandas/_libs/hashtable_class_helper.pxi", line 7194, in pandas._libs.hashtable.PyObjectHashTable._unique
TypeError: unhashable type: 'Series'