Python : group by columns with columns values that are grouped by occurs only once and retain all other columns

Here is a possible solution:

from pandas import json_normalize

df = json_normalize(
    dict_,
    record_path=['nested_dicts'],
    meta = ['key1', 'key2', 'key3', 'key4', 'key5']
)
df = df.set_index(['key1', 'key2', 'key3', 'key4', 'key5'])

Here I use a json_normalize function to flatten the dictionary and then I set the index to key1-key5.

Here's a pandas-only solution:

import pandas as pd

# Convert dict to dataframe
# https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.from_dict.html
df = pd.DataFrame.from_dict(dict_)

# Explode the nested_dicts column: each item/dict in list is transformed into a new row in the dataframe
# https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.explode.html
df2 = df.explode('nested_dicts', ignore_index=True)

# Assign the nested_dicts dictionaries to a new column for each key 
# while dropping the nested_dicts column from the dataframe
# https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.assign.html
df3 = df2.drop('nested_dicts', 1).assign(**pd.DataFrame(df2.nested_dicts.values.tolist()))

#  Set index
df4 = df3.set_index(df3.columns.tolist())

df4

Based on Yury answere with a bit of automated recognition for meta and record_path.

# take the first item to detect meta, record_path
meta, record_path = [], []
for key_j, value_j in dict_[0].items():
    if not isinstance(value_j, list):
        meta.append(key_j)
    else:
        record_path.append(key_j)

# adopted Yury's solution
df = pandas.json_normalize(
    dict_,
    record_path=record_path[:1],
    meta = meta,
)

df.set_index(meta, inplace=True)

Maybe I didn't understand the problem right, but why would one need big clunky libraries for this when 33 lines of python code can do the whole thing?

import pprint

def flatten_and_merge(dict_list, key_list):
    collect_dict = {}
    for dict_list_entry in dict_list:
        result_dict = {}
        
        # first flatten everything into result_dict
        for (key,value) in dict_list_entry.items():
            if isinstance(value,list) and len(value)>0 and isinstance(value[0],dict):
                for subdict in value:
                    for (subkey,subvalue) in subdict.items():
                        target = result_dict.get(subkey)
                        if not target:
                            target = []
                            result_dict[subkey] = target
                        target.append(subvalue)
            elif key not in key_list:
                result_dict[key] = [ value ] # make all other fields into collection-lists
            else:
                result_dict[key] = value # transfer key values unchanged
                
        # finally merge the resulting dictionary into the one with the same key values
        dict_key = '\u0000'.join(result_dict.get(key) for key in key_list)
        target = collect_dict.get(dict_key)
        if not target:
            collect_dict[dict_key] = result_dict
        else:
            for (key,value) in result_dict.items():
                if key not in key_list:
                    if key in target:
                        target.get(key).extend(value)
                    else:
                        target[key] = value
    return collect_dict.values()

if __name__ == '__main__':
    dict_list = [
        {'key1': 'value1',
         'key2': 'value2',
         'some_value_to_keep': 'kept_value1',
         'some_list_to_keep': [ 'list_value_1', 'list_value_2' ],
         'nested_dicts': [
            {'nested_key1': 'nested_value_1',
             'nested_key2': 'nested_value_2',
            },
            {'nested_key1': 'nested_value_3',
             'nested_key2': 'nested_value_4',
            }]
        },
        {'key1': 'value1',
         'key2': 'value2',
         'some_value_to_keep': 'kept_value2',
         'some_list_to_keep': [ 'list_value_3', 'list_value_4' ],
         'some_other_list_to_keep': [ 'list_value_5', 'list_value_6' ],
         'nested_dicts': [
            {'nested_key1': 'nested_value_5',
             'nested_key2': 'nested_value_6',
            },
            {'nested_key1': 'nested_value_7',
             'nested_key2': 'nested_value_8',
            }]
        },
        {'key1': 'value3',
         'key2': 'value4',
         'some_other_value_to_keep': 'kept_value3',
         'yet_another_list_to_keep': [ 'list_value_7', 'list_value_8' ],
         'nested_dicts': [
            {'nested_key1': 'nested_value_9',
             'nested_key2': 'nested_value_10',
            },
            {'nested_key1': 'nested_value_11',
             'nested_key2': 'nested_value_12',
            }]
        }
    ]
    print('original data:\n')
    pprint.PrettyPrinter().pprint(dict_list)
    
    print('\n\nflattened and merged data\n')
    flattened = flatten_and_merge(dict_list,['key1','key2'])
    pprint.PrettyPrinter().pprint(flattened)

produces the following output:

original data:

[{'key1': 'value1',
  'key2': 'value2',
  'nested_dicts': [{'nested_key1': 'nested_value_1',
                    'nested_key2': 'nested_value_2'},
                   {'nested_key1': 'nested_value_3',
                    'nested_key2': 'nested_value_4'}],
  'some_list_to_keep': ['list_value_1', 'list_value_2'],
  'some_value_to_keep': 'kept_value1'},
 {'key1': 'value1',
  'key2': 'value2',
  'nested_dicts': [{'nested_key1': 'nested_value_5',
                    'nested_key2': 'nested_value_6'},
                   {'nested_key1': 'nested_value_7',
                    'nested_key2': 'nested_value_8'}],
  'some_list_to_keep': ['list_value_3', 'list_value_4'],
  'some_other_list_to_keep': ['list_value_5', 'list_value_6'],
  'some_value_to_keep': 'kept_value2'},
 {'key1': 'value3',
  'key2': 'value4',
  'nested_dicts': [{'nested_key1': 'nested_value_9',
                    'nested_key2': 'nested_value_10'},
                   {'nested_key1': 'nested_value_11',
                    'nested_key2': 'nested_value_12'}],
  'some_other_value_to_keep': 'kept_value3',
  'yet_another_list_to_keep': ['list_value_7', 'list_value_8']}]


flattened and merged data

[{'key1': 'value1',
  'key2': 'value2',
  'nested_key1': ['nested_value_1',
                  'nested_value_3',
                  'nested_value_5',
                  'nested_value_7'],
  'nested_key2': ['nested_value_2',
                  'nested_value_4',
                  'nested_value_6',
                  'nested_value_8'],
  'some_list_to_keep': [['list_value_1', 'list_value_2'],
                        ['list_value_3', 'list_value_4']],
  'some_other_list_to_keep': [['list_value_5', 'list_value_6']],
  'some_value_to_keep': ['kept_value1', 'kept_value2']},
 {'key1': 'value3',
  'key2': 'value4',
  'nested_key1': ['nested_value_9', 'nested_value_11'],
  'nested_key2': ['nested_value_10', 'nested_value_12'],
  'some_other_value_to_keep': ['kept_value3'],
  'yet_another_list_to_keep': [['list_value_7', 'list_value_8']]}]