Pandas: Clean up String column containing Single Quotes and Brackets using Regex?

user_5709203 0
Viewed 275

I want to clean the following Pandas dataframe column, but in a single and efficient statement than the way I am trying to achieve it in the code below.

Input:

                  string
0  ['string', '#string']
1            ['#string']
2                     []

Output:

            string
0  string, #string
1          #string
2              NaN

Code:

import pandas as pd
import numpy as np

d = {"string": ["['string', '#string']", "['#string']", "[]"]}
df = pd.DataFrame(d)

df['string'] = df['string'].astype(str).str.strip('[]')
df['string'] = df['string'].replace("\'", "", regex=True)
df['string'] = df['string'].replace(r'^\s*$', np.nan, regex=True)

print(df)
pandas dataframe python regex
1 Answers

You can use

df['string'] = df['string'].astype(str).str.replace(r"^[][\s]*$|(^\[+|\]+$|')", lambda m: '' if m.group(1) else np.nan)

Details:

  • ^[][\s]*$ - matches a string that only consists of zero or more [, ] or whitespace chars
  • | - or
  • (^\[+|\]+$|') - captures into Group 1 one or more [ chars at the start of string, or one or more ] chars at the end of string or any ' char.

If Group 1 matches, the replacement is an empty string (the match is removed), else, the replacement is np.nan.

user_38329700
Related