Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
200 views
in Technique[技术] by (71.8m points)

python - Transpose dataframe based on column list

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)
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...