Объединение строк словаря с группировкой значений по ключу

Question

Есть опрос который проходят люди, на его базе нужно составить отчет. Входные данные получаемые из БД и заполнения списка словарей:

Запрос:

from itertools import groupby
from operator import itemgetter


        results_associate = [{'Question': str(), 'Answer': str(), 'Employee': list(), 'Comment': list()}]
    
           with app.app_context():
            with db.session.connection() as conn:
                for elem in conn.execute(query):
                    print(elem)
        
                    if elem[5] == 'Associate and Counsel Fall Review' and elem[0] not in results_associate:
                        results_associate.append(
                            {'Question': elem[0], 'Answer': elem[1], 'Employee': elem[3], 'Comment': elem[4]})
    
    results_associate.sort(key=itemgetter('Question', 'Answer'))
    for method, items in groupby(results_associate, key=itemgetter('Question', 'Answer')):
        print(method)
        for i in items:
            print('     ', i)

Выходные данные из БД в уже заполненном списке словарей через цикл for:

  ('2. RATING OF LAWYERING, MANAGEMENT AND LEADERSHIP SKILLS:', '6')
          {'Question': '2. RATING OF LAWYERING, MANAGEMENT AND LEADERSHIP SKILLS:', 'Answer': '6', 'Employee': 'Employee_1', 'Comment': None}
          {'Question': '2. RATING OF LAWYERING, MANAGEMENT AND LEADERSHIP SKILLS:', 'Answer': '6', 'Employee': 'Employee_2', 'Comment': None}
  ('Engagement', '6')
          {'Question': 'Engagement', 'Answer': '6', 'Employee': 'Employee_1', 'Comment': None}
          {'Question': 'Engagement', 'Answer': '6', 'Employee': 'Employee_2', 'Comment': None}
          {'Question': 'Engagement', 'Answer': '6', 'Employee': 'Employee_3', 'Comment': None}
  ('Engagement', '7(Exceptional)')
          {'Question': 'Engagement', 'Answer': '7(Exceptional)', 'Employee': 'Employee_1', 'Comment': None}
          {'Question': 'Engagement', 'Answer': '7(Exceptional)', 'Employee': 'Employee_2', 'Comment': None}
          {'Question': 'Engagement', 'Answer': '7(Exceptional)', 'Employee': 'Employee_3', 'Comment': None}
          {'Question': 'Engagement', 'Answer': '7(Exceptional)', 'Employee': 'Employee_4', 'Comment': None}

Заполненный список словарей:

[{'Question': '', 'Answer': '', 'Employee': [], 'Comment': []},
 {'Question': '1. Nature of your contact with the associate this review '
              'period:',
  'Answer': 'Close',
  'Employee': 'Employee_1',
  'Comment': None},
 {'Question': '1. Nature of your contact with the associate this review '
              'period:',
  'Answer': 'Limited',
  'Employee': 'Employee_2',
  'Comment': None},
 {'Question': '1. Nature of your contact with the associate this review '
              'period:',
  'Answer': 'Limited',
  'Employee': 'Employee_3',
  'Comment': None}
]

Employee - тот кто оценивает.

Подскажите, как в итоге объединить строки и сгруппировать значения по Вопросу и Ответу, чтобы получить следующий результат:

 ('2. RATING OF LAWYERING, MANAGEMENT AND LEADERSHIP SKILLS:', '6')
              {'Question': '2. RATING OF LAWYERING, MANAGEMENT AND LEADERSHIP SKILLS:', 'Answer': '6', 'Employee': ['Employee_1', 'Employee_2'], 'Comment': [None, None]}
('Engagement', '6')
              {'Question': 'Engagement', 'Answer': '6', 'Employee': ['Employee_1', 'Employee_2', 'Employee_3'], 'Comment': [None, None, None]}

('Engagement', '7(Exceptional)')
              {'Question': 'Engagement', 'Answer': '7(Exceptional)', 'Employee': ['Employee_1', 'Employee_2', 'Employee_3', 'Employee_4'], 'Comment': [None, None, None, None]}

Answer 1

Можно сделать группировку с помощью pandas:

import pandas as pd
d = [{'Question': '', 'Answer': '', 'Employee': [], 'Comment': []},
 {'Question': '1. Nature of your contact with the associate this review '
              'period:',
  'Answer': 'Close',
  'Employee': 'Employee_1',
  'Comment': None},
 {'Question': '1. Nature of your contact with the associate this review '
              'period:',
  'Answer': 'Limited',
  'Employee': 'Employee_2',
  'Comment': None},
 {'Question': '1. Nature of your contact with the associate this review '
              'period:',
  'Answer': 'Limited',
  'Employee': 'Employee_3',
  'Comment': None}
]
df = pd.DataFrame(d).iloc[1:,:]
res = df.groupby(["Question", "Answer"])[["Employee", "Comment"]].agg(list).to_dict('index')

res:

{('1. Nature of your contact with the associate this review period:', 'Close'): {'Employee': ['Employee_1'], 'Comment': [None]}, ('1. Nature of your contact with the associate this review period:', 'Limited'): {'Employee': ['Employee_2', 'Employee_3'], 'Comment': [None, None]}}

Answer 2

Нашел решение на чистом Python. Ответ от strawdog тоже рабочий.

    def merge_dicts(list_of_dicts):
        lookup = {}
        results = []
        for d in list_of_dicts:
            key = (d['Question'], d['Answer'])
            try:  # it's easier to ask forgiveness than permission
                lookup[key]['Employee'].append(d['Employee'])
                lookup[key]['Comment'].append(d['Comment'])
            except KeyError:
                val = {'Question': d['Question'],
                       'Answer': d['Answer'],
                       'Employee': [d['Employee']],  # note, extra [] around value to make it a list
                       'Comment': [d['Comment']]}
                lookup[key] = val
                results.append(val)
    
        return results

results_associate = merge_dicts(temp_results_associate)
results_associate.sort(key=itemgetter('Question', 'Answer'))
for method, items in groupby(results_associate, key=itemgetter('Question', 'Answer')):
    print(method)
    for i in items:
        print('     ', i)

Результат:

('1. Nature of your contact with the associate this review period:', 'Close')
      {'Question': '1. Nature of your contact with the associate this review period:', 'Answer': 'Close', 'Employee': ['Employee_1'], 'Comment': [None]}
('1. Nature of your contact with the associate this review period:', 'Limited')
      {'Question': '1. Nature of your contact with the associate this review period:', 'Answer': 'Limited', 'Employee': ['Employee_1', 'Employee_2', 'Employee_3'], 'Comment': [None, None, None]}

Надеюсь кому-то тоже поможет.

БЛОГ НА HUSL

Объединение строк словаря с группировкой значений по ключу

Ответы (2 шт):