[Python] How do I merge the columns from the data and matrix data frames at the same time do...

KeyError
Traceback (most recent call last)
<ipython-input-11-6f060795a7ae> in <cell line: 0>()
2
3 # Merge data and matrix
----> 4 merged_df = data.merge(matrix, left_on='_main_table_record_id', right_on='_id', how='left')
5
6 # Aggregate _object_id by _id

4 frames
/usr/local/lib/python3.11/dist-packages/pandas/core/generic.py in _get_label_or_level_values(self, key, axis)
1909 values = self.axes[axis].get_level_values(key)._values
1910 else:
-> 1911 raise KeyError(key)
1912
1913 # Check for duplicates

KeyError: '_main_table_record_id'


The python script below would still return a dataframe where the _id and the _main_table_record_id have duplicates due to having mmore than one _object_id

# Merge data and matrix
merged_df = data.merge(matrix, left_on='_main_table_record_id', right_on='_id', how='left')

# Aggregate _object_id by _id
final_df = merged_df.groupby('_id', as_index=False).agg({
'_main_table_record_id': 'first', # Keep first occurrence
'_custom_field_tag': 'first', # Keep first occurrence
'_object_id': lambda x: ', '.join(map(str, x.dropna().unique())), # Concatenate unique values
'_user_name': 'first', # Keep first occurrence
'_started_at': 'first' # Keep first occurrence
})

# Display the first few rows
print(final_df.head())

# Close connection
dbConnection.close() here

Continue reading...