Pandas合并不等长DataFrame：基于idx匹配多语言选项

关联规则说明 #

• 待关联的两个数据集：
  • foreign：存储选项的英文文本、对应乌尔都语翻译
  • native：存储全量选项的德语文本，数据量远大于foreign
• 关联基准：完全保留native的全量行与原有行顺序
• 匹配逻辑：通过公共字段idx关联，匹配成功的行从foreign中取对应option_eng、option_urdu值填充；未匹配的行两列默认填充空值

原始数据样例 #

# foreign 表
idx                    option_eng                    option_urdu
1.1.01-001   This is english of option1             urdu translation of option1
1.1.01-001   This is english of option2             urdu translation of option2
1.1.01-001   This is english of option3             urdu translation of option3 
1.1.01-002   This is english of option1             urdu translation of option1
1.1.01-002   This is english of option2             urdu translation of option2
1.1.01-002   This is english of option3              Urdu translation of option3
1.1.01-003   This is english of option1              urdu translation of option1
1.1.01-003   This is english of option2              urdu translation of option2
1.1.01-004   This is english of option1              urdu translation of option1
1.1.01-004   This is english of option2               urdu translation of option2

# native 表
  idx    sort_code  iscorrect   count                option_de
1.1.01-001  1          1          1              German tranlation option3
1.1.01-001  2          1          2              German tranlation option2
1.1.01-001  3          0          3              German tranlation option1
1.1.01-002  1          1          4              German tranlation option3
1.1.01-002  2          1          5             German tranlation option2
1.1.01-002  3          1          6             German tranlation option1
1.1.01-003  1          1          7             German tranlation option3
1.1.01-003  2          1          8             German tranlation option2
1.1.01-003  3          0          9             German tranlation option1
1.1.01-012  1          1          10             German tranlation option3
1.1.01-012  2          1          11             German tranlation option2
1.1.01-012  3          1          12             German tranlation option1
1.1.01-101  1          1          13             German tranlation option3
1.1.01-101  2          1          14             German tranlation option2

输出效果要求 #

idx               option_de                       option_eng           option_urdu
1.1.01-001  German tranlation option3   This is english of option3  This is urdu of option3
1.1.01-001  German tranlation option2   This is english of option2  This is urdu of option2
1.1.01-001  German tranlation option1   This is english of option1  This is urdu of option1
1.1.01-002  German tranlation option3   This is english of option3  This is urdu of option3
1.1.01-002  German tranlation option2   This is english of option2  This is urdu of option2
1.1.01-002  German tranlation option1   This is english of option1  This is urdu of option1
1.1.01-003  German tranlation option3   This is english of option3  This is urdu of option3
1.1.01-003  German tranlation option2   This is english of option2  This is urdu of option2
1.1.01-003  German tranlation option1   This is english of option1  This is urdu of option1
1.1.01-012  German tranlation option3     na                                  na
1.1.01-012  German tranlation option2     na                                  na
1.1.01-012  German tranlation option1     na                                  na
1.1.01-101  German tranlation option3     na                                  na
1.1.01-101  German tranlation option2     na                                  na

核心匹配难点 #

• 同一idx分组下，native的选项排列为反向顺序（option3 → option2 → option1）
• 同一idx分组下，foreign的选项排列为正向顺序（option1 → option2 → option3）
• 仅靠idx单字段关联，会出现行错位、数据膨胀的问题，无法精准匹配对应选项

已尝试的失败方案 #

• 用native.merge(foreign,how='left',left_on='idx',right_on='idx')单字段左连接，结果产生大量idx重复的冗余行，数据量异常膨胀
• 用pd.combine_first方法合并，会打乱native原有的行顺序，不符合输出要求
• 尝试通过native['key']=native.groupby(["code"]).cumcount()新增分组计数列作为关联键，仍无法解决顺序相反的匹配错位问题

可复现代码 #

import pandas as pd

Foreigndic = {'idx': {0: '1.1.01-001', 1: '1.1.01-001', 2: '1.1.01-001', 3: '1.1.01-002', 4: '1.1.01-002', 5: '1.1.01-002', 6: '1.1.01-003', 7: '1.1.01-003', 8: '1.1.01-004', 9: '1.1.01-004'}, 'option_eng': {0: ' Stopping as a precaution at every crossroads', 1: ' Not insisting on your rights', 2: " Allowing for other people's mistakes", 3: ' Inattentiveness', 4: ' Driving too close behind the vehicle in front', 5: ' Unexpectedly heavy braking', 6: '  respond as soon as possible to probable changes in the road traffic situation', 7: '  attempt to identify the intentions of other road users, as soon as possible', 8: ' - of its braking characteristics', 9: ' - of its manoeuvrability'}, 'option_urdu': {0: 'احتیاط کے طور پر ہر دوراہے پر رکنا', 1: 'اپنے حقوق پر اصرار نہیں کرنا', 2: 'دوسرے لوگوں کی غلطیوں کی اجازت دینا', 3: 'غفلت', 4: 'اگلی گاڑی سے مناسب فاصلہ نہ رکھنا', 5: 'غیر متوقع طور پر اگلی گاڑی کا اچانک بریک لگا دینا', 6: 'سڑک پر موجود ٹریفک کی صورتحال میں ممکنہ تبدیلیوں کا جلد سے جلد جواب دینا', 7: 'جتنی جلدی ممکن ہو سڑک کے دوسرے صارفین کے ارادوں کی نشاندہی کرنے کی کوشش کریں', 8: ' اس کی بریک خصوصیات', 9: 'اس کے سسٹم کی جانکاری'}}
Nativedict = {'code': {0: 1, 1: 2, 2: 3, 3: 1, 4: 2, 5: 3, 6: 1, 7: 2, 8: 3, 9: 1, 10: 2, 11: 3, 12: 1, 13: 2, 14: 3}, 'ans_id': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5, 5: 6, 6: 7, 7: 8, 8: 9, 9: 10, 10: 11, 11: 12, 12: 13, 13: 14, 14: 15}, 'iscorrect': {0: 1, 1: 1, 2: 0, 3: 1, 4: 1, 5: 1, 6: 1, 7: 1, 8: 0, 9: 1, 10: 1, 11: 1, 12: 1, 13: 1, 14: 1}, 'ans_id_txt': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5, 5: 6, 6: 7, 7: 8, 8: 9, 9: 10, 10: 11, 11: 12, 12: 13, 13: 14, 14: 15}, 'text': {0: 'Nicht auf dem eigenen Recht bestehen', 1: 'Mit Fehlern anderer rechnen', 2: 'Vorsorglich an jeder Kreuzung anhalten', 3: 'Unerwartet starkes Bremsen', 4: 'Unaufmerksamkeit', 5: 'Zu dichtes Auffahren', 6: 'Ich reagiere möglichst frühzeitig auf wahrscheinliche Veränderungen der Verkehrssituation', 7: 'Ich versuche, die Absichten anderer Verkehrsteilnehmer möglichst frühzeitig zu erkennen', 8: 'Ich beschränke die Verkehrsbeobachtung möglichst auf das direkt vor mir fahrende Fahrzeug', 9: 'Ich weiß möglicherweise zu wenig über die Besonderheiten in der Anordnung und der Funktion der Bedienelemente', 10: 'Ich weiß möglicherweise zu wenig über die Besonderheiten im Bremsverhalten', 11: 'Ich weiß möglicherweise zu wenig über die Besonderheiten im Lenkverhalten', 12: 'Der Vorausfahrende bremst unerwartet', 13: 'Der Vorausfahrende betätigt den Blinker vor dem Abbiegen zu spät', 14: 'Der Vorausfahrende hält unerwartet an, um nach dem Weg zu fragen'}, 'ques_id': {0: 1, 1: 1, 2: 1, 3: 2, 4: 2, 5: 2, 6: 3, 7: 3, 8: 3, 9: 4, 10: 4, 11: 4, 12: 5, 13: 5, 14: 5}, 'idx': {0: '1.1.01-001', 1: '1.1.01-001', 2: '1.1.01-001', 3: '1.1.01-002', 4: '1.1.01-002', 5: '1.1.01-002', 6: '1.1.01-003', 7: '1.1.01-003', 8: '1.1.01-003', 9: '1.1.01-004', 10: '1.1.01-004', 11: '1.1.01-004', 12: '1.1.01-101', 13: '1.1.01-101', 14: '1.1.01-101'}}

foreign = pd.DataFrame(Foreigndic)
native = pd.DataFrame(Nativedict)

正确实现步骤 #

核心思路是针对两表同idx下顺序相反的特点，构造可精准匹配的复合关联键：

1. 对foreign表，按idx分组，生成分组内的正向行号作为临时关联列：
   foreign['seq'] = foreign.groupby('idx').cumcount()
2. 对native表，按idx分组，生成分组内的反向行号作为临时关联列：
   native['seq'] = native.groupby('idx').cumcount(ascending=False)
3. 以native为基准，用idx和seq双字段做左连接：
   result = native.merge(foreign[['idx', 'seq', 'option_eng', 'option_urdu']], how='left', on=['idx', 'seq'])
4. 删除临时关联列seq，最终结果完全保留native的原有行顺序，未匹配行自动填充空值

注意：临时关联列seq仅用于匹配，合并完成后删除即可，不会改变native原有列的顺序与结构

内容的提问来源于stack exchange，提问作者Faisal