python - Transpose dataframe based on column list

Question

I have a dataframe in the following structure:

cNames  | cValues   |  number  
[a,b,c] | [1,2,3]   |  10      
[a,b,d] | [55,66,77]|  20

I would like to transpose - create columns from the names in cNames.
But I can't manage to achieve this with transpose because I want a column for each value in the list.
The needed output:

a   | b   | c   | d   |  number
1   | 2   | 3   | NaN | 10
55  | 66  | NaN | 77  | 20

How can I achieve this result?
Thanks!

The code to create the DF:

d = {'cNames': [['a','b','c'], ['a','b','d']], 'cValues': [[1,2,3], 
[55,66,77]], 'number': [10,20]}
df = pd.DataFrame(data=d)

Answer 1

You can concatenate explode() and then pivot the table back to desired output!

df = df.explode('cNames').explode('cValues')
df['cValues'] = pd.to_numeric(df['cValues'])
print(df.pivot_table(columns='cNames',index='number',values='cValues'))

Output:

cNames     a     b    c     d
number                       
10       2.0   2.0  2.0   NaN
20      66.0  66.0  NaN  66.0

Pitifully, the output of explode is of type object therefore, we must transform it first to pd.to_numeric() before pivoting. Otherwise there will no be numeric values to aggregate.