Pandas: convert certain str column to list as other values

user_4150078 0
Viewed 23

I have pandas dataframe column that contains both list and str values. I am only trying to convert str values into a proper list format so that it matches other list form values. I found a fix around it but I am looking to see if there is better way to do it? Below are my questions:

  1. If there is a pandas's build functionality/capability to use instead of writing long regex, replace, ..etc?
  2. How to convert [nan] to [] without regex?

Here is my try:

Data File:

StudentName,CourseID
Alan,"['abc-12-0878', 'abc-12-45', 'abc-12-232342']"
Tim,"['abc-12-0878', 'abc-12-45']"
David,abc-12-1147
Martha,
Matt,"['abc-12-0878', 'abc-12-45']"
Abby,abc-12-1148

My code try:

import pandas as pd

df = pd.read_csv('sample_students.csv')
df


df['result'] = df['CourseID'].astype(str).apply(lambda x: x.strip('[]').replace("'","").split(',')) 
# Regex route.
# Pandas`s build in function available?
# gives `[nan]` instead of `[]`
# `to_list` and `tolist` didn't work.

Result I am looking for:

print(df[['CourseID','result']]) 

CourseID                                        result
['abc-12-0878', 'abc-12-45', 'abc-12-232342']   ['abc-12-0878', 'abc-12-45', 'abc-12-232342']
['abc-12-0878', 'abc-12-45']                    ['abc-12-0878', 'abc-12-45']
abc-12-1147                                     ['abc-12-1147']
NaN                                             []
['abc-12-0878', 'abc-12-45']                    ['abc-12-0878', 'abc-12-45']
abc-12-1148                                     [abc-12-1148]
python-3.6 pandas dataframe python-3.x
3 Answers

You may apply ast.literal_eval() for parsing a literal representation of a list.

import ast

def f(s):
    if pd.isna(s):     # case 1: nan
        return []
    elif s[0] == "[":  # case 2: string of list
        return ast.literal_eval(s)
    else:              # case 3: string
        return [s]

df["result"] = df["CourseID"].apply(f)

Or in an one-liner:

df["result"] = df["CourseID"].apply(lambda s: [] if pd.isna(s) else ast.literal_eval(s) if s[0] == "[" else [s])

Result:

print(df[["CourseID","result"]])
                                        CourseID                                   result
0  ['abc-12-0878', 'abc-12-45', 'abc-12-232342']  [abc-12-0878, abc-12-45, abc-12-232342]
1                   ['abc-12-0878', 'abc-12-45']                 [abc-12-0878, abc-12-45]
2                                    abc-12-1147                            [abc-12-1147]
3                                            NaN                                       []
4                   ['abc-12-0878', 'abc-12-45']                 [abc-12-0878, abc-12-45]
5                                    abc-12-1148                            [abc-12-1148]
user_32186930

If you prefer not having to import another library, you can do it this way:

def update_data(val):
    if pd.isna(val):
        return []
    if val[0] == '[':
        return val    
    return [val]
df['Result'] = df.apply(lambda row: update_data(row['CourseID']), axis= 1)
user_142490870

You may check only the type of the value

df['result'] = df['CourseID'].apply(lambda x: x.strip('[]').replace("'","").split(',') if type(x) == str else [])

>>> print(df[['CourseID','result']])
                                        CourseID                                     result
0  ['abc-12-0878', 'abc-12-45', 'abc-12-232342']  [abc-12-0878,  abc-12-45,  abc-12-232342]
1                   ['abc-12-0878', 'abc-12-45']                  [abc-12-0878,  abc-12-45]
2                                    abc-12-1147                              [abc-12-1147]
3                                            NaN                                         []
4                   ['abc-12-0878', 'abc-12-45']                  [abc-12-0878,  abc-12-45]
5                                    abc-12-1148                              [abc-12-1148]
user_59795430
Related