ids = [item['id'] for item in data]
col = [item['colors'] for item in data]
ids = np.repeat(ids, list(map(len, col)))
col = [a for item in col for a in item]
df = DataFrame({'ids': ids, 'colors': col})
df
df.groupby(['id', 'colors']).size().unstack().fillna(0)
How to transform list of dictionaries with jagged arrays to a DataFrame