Python : Dataframe transformation tuples as columns

Question

I'm trying to transform a dataframe similar to the one below:

 index apple
 1      [(red,3),(green,2)]
 1      [(red,3)]
 1      [(yellow,9),(red,3)]
 1      [(green,2),(yellow,9)]
 1      [(green,2),(yellow,9), (pink,50)]
 2      [(yellow,14),(red,1)]
 2      [(green,5)]

into this:

 index  apple_red apple_green apple_yellow apple_pink
 1       3         2           9            50
 2       1         5           14           0

Note that tuples are unique per index in the original table Any idea how do to this ?

Thanks

SO is no code-writing service. Please post what you've tried so far. What problems did you encounter? — Mike Scotty
– Mike Scotty, Commented Jul 19, 2017 at 11:25

jezrael · Accepted Answer · 2017-07-19 11:40:17Z

1

You can use:

use DataFrame constructor with list comprehension
reshape by stack, reset_index and remove duplicates by drop_duplicates by columns created from tuples
last rehape by set_index and unstack
last add_prefix, rename_axis and reset_index

df1 = pd.DataFrame([dict(x) for x in df['apple']], index=df.index) \
        .stack() \
        .astype(int) \
        .reset_index(name='val') \
        .drop_duplicates(['level_1','val']) \
        .set_index(['index', 'level_1'])['val'] \
        .unstack(fill_value=0) \
        .add_prefix('apple_') \
        .rename_axis(None) \
        .rename_axis(None, axis=1) \
        .reset_index()
print (df1)
   index  apple_green  apple_pink  apple_red  apple_yellow
0      1            2          50          3             9
1      2            5           0          1            14

edited Jul 19, 2017 at 11:40

answered Jul 19, 2017 at 11:35

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jezrael Over a year ago

Glad can help, it was interesting problem!

Collectives™ on Stack Overflow

Python : Dataframe transformation tuples as columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related