Spliting a list of string as columns names and setting true or false if exists in the string

user_17039578 0
Viewed 16

I have this dataframe:

id            class                     text
 1       ["oil","water"]                text1
 2           ["oil"]                    text2
 3     ["sun","water","earth"]          text3

and I have a list of all possible class using this code:

import ast
df.class.map(ast.literal_eval).explode().value_counts()


oil
water
sun
earth

I want to create a new dataframe with all classes as column names and set 1 if the column name corresponds to the class column:

id            class                 text       oil    water   sun   earth
  1       ["oil","water"]           text1       1       1      0       0
  2           ["oil"]               text2       1       0      0       0
  3     ["sun","water","earth"]     text3       0       1      1       1

I tried:

f = df.explode('class').pivot(columns='class', index='text', values='text').notnull().astype(int)

But the columns name is not splited correctly

jupyter-notebook python-3.x
1 Answers
 (df.merge(df.assign(value=1).explode('class').
    pivot_table('value', 'id', 'class', aggfunc = sum,fill_value = 0).
    reset_index()))

   id                class   text  earth  oil  sun  water
0   1         [oil, water]  text1      0    1    0      1
1   2                [oil]  text2      0    1    0      0
2   3  [sun, water, earth]  text3      1    0    1      1
user_83802720
Related