How to replace single quotes in a json like column in pandas-CodePudding

I have a column in dataframe with values like the ones below:

{'id': 22188, 'value': 'trunk'}
{'id': 22170, 'value': 'motor'}

I want to replace the single quotes to double quotes to use as json field. I am trying:

df['column'] = df['column'].replace({'\'': '"'}, regex=True)

But nothing changes.

How can I do this?

Expected result:

{"id": 22188, "value": "trunk"}
{"id": 22170, "value": "motor"}

CodePudding user response：

No need to special escape the characters, just use the opposite family. And no need to use Regex neither And you have to use str accessor to do string replacements

df['column'] = df['column'].str.replace("'",'"', regex=False)

Works fine with string fields

>>> pd.Series(["{'id': 22188, 'value': 'trunk'}","{'id': 22170, 'value': 'motor'}"])
0    {'id': 22188, 'value': 'trunk'}
1    {'id': 22170, 'value': 'motor'}
dtype: object
>>> pd.Series(["{'id': 22188, 'value': 'trunk'}","{'id': 22170, 'value': 'motor'}"]).str.replace("'",'"')
0    {"id": 22188, "value": "trunk"}
1    {"id": 22170, "value": "motor"}
dtype: object
>>>

Fails with dictionaries => Convert it first with astype(str)

>>> pd.Series([{'id': 22188, 'value': 'trunk'},{'id': 22170, 'value': 'motor'}])
0    {'id': 22188, 'value': 'trunk'}
1    {'id': 22170, 'value': 'motor'}
dtype: object
>>> pd.Series([{'id': 22188, 'value': 'trunk'},{'id': 22170, 'value': 'motor'}]).str.replace("'",'"')
0   NaN
1   NaN
dtype: float64
>>> pd.Series([{'id': 22188, 'value': 'trunk'},{'id': 22170, 'value': 'motor'}]).astype(str).str.replace("'",'"')
0    {"id": 22188, "value": "trunk"}
1    {"id": 22170, "value": "motor"}
dtype: object