Pandas column of features to multiple columns

Question

I have a CSV file that has 9 columns, the last one is a column of a list of features as following:

  First Name  Last Name                         Email Grad Date                Major List  Appointments Count  Advising Time  Labels Count                                   Labels Name List
0     Adrien      Yanez           ayaneza@twitter.com       NaN  Psychology: Neuroscience                  12            325            18  acad_stat=gr, class_code=sr, sess=fa, re_id=11...
1    Aindrea     Braams             abraams3v@nps.gov       NaN  Psychology: Neuroscience                   4            120            17  cx_id=600852, re_id=1114446, primary_departmen...
2      Alida  Swinburne  aswinburne52@cyberchimps.com  5/1/2022                Psychology                   1             30            14  re_id=1124407, primary_department=psychology, ...

The column Labels Name List is the target one, the objective is to create new columns each has a name of one of the tags or features inside the list, and the value is the value after the '=' sign after the tag name in the list, or takes the value of 1 if there is no '=' sign.

For example, if the tag list for row 1 is:
adv1=syed ahmad, cx_id=616758, re_id=1112539, slate_id=, class_session=spring, class_yr=2018, advd=joseph atkins, adv2=, not labeled seniors 2022

The output will look like this:

         adv1   cx_id    re_id  slate_id class_session  class_yr           advd  adv2  not labeled seniors 2022
0  syed admad  616758  1112539         1        spring      2018  joseph atkins     1                         1

so how can I do that in pandas, please?
Note: The total columns will be a fixed number of course for all rows, which means if a row doesn't have the tag name it will take the value of none or 0 in this tag column

AndrzejO · Answer

You could create a function which will receive a labels_name_list, split it by , and then by = to get the key-value pairs, and returns them as a dict. Something like:

def fun(label_names_list):
    key_val_strings = label_names_list.split(',')
    key_val = map(lambda z: (z[0], z[1]), [x.split('=') for x in key_val_strings])
    return dict(key_val)

Then you need to apply it on the labels_name_list column. This will create a new dataframe:

new_df = df[1].apply(lambda s: pd.Series(fun(s)))

For example

a = [['A', 'Z=cat,b=a1, c=a2'], ['B', 'Z=dog,c=b1,  d=b2']]
new_df = df[1].apply(lambda s: pd.Series(fun(s)))

The new_df:

    Z    b   c    c   d
0   cat a1  a2  NaN NaN
1   dog NaN NaN b1  b2

This is not a full solution, for example it doesn't remove whitespace, so you have here two columns " c" and "c" because of the space before c

Pandas column of features to multiple columns

1 Answers