CODEWITHSUNDEEP
pythonpandasstringdataframereplace
Ruchita P
Ruchita P

Reputation: 389

replace single quote to double quote python pandas dataframe

I want to replace single quote(') to double quote(") to make it proper json column value in python dataframe.

e.g. csv file looks like...

Unit Id Batch Id                               Items prod
A108    qa120  {'A': 123, 'B': 342, 'C': 454}   
P258    re015  {'A': 124, 'B': 234, 'C': 343}

I'm reading these values from csv to pandas dataframe. I tried several ways, but no luck.

df.replace("'",'"',inplace=True)
df.['<column_name>'].str.replace(r"[\',]",'"')
df = df['<column_name>'].str.replace(r"[\',]",'"')

Thanks for your help in advance.

Upvotes: 10

Views: 23564

Answers (4)

Gon&#231;alo Peres
Gon&#231;alo Peres

Reputation: 13582

Use str.replace.

If you want to update a column on a DataFrame, such as this

Example of DataFram

And let's say that you want to remove the double quotes from the first column.

Just do the following

df[0] = df[0].str.replace(r"[\"]", r"'")

Here is the final result

Output after running the code above

Upvotes: 2

Rakesh
Rakesh

Reputation: 82765

Looks like you need.

import pandas as pd
import json
import ast


df = pd.DataFrame({"Unit Id": ["A108", "P258"], "Batch Id": ["qa120", "re015"], "Items prod": ["{'A': 123, 'B': 342, 'C': 454}", "{'A': 124, 'B': 234, 'C': 343}"]})
df["NEW"] = df["Items prod"].apply(ast.literal_eval).apply(json.dumps)
print(df)

Output:

  Batch Id                      Items prod Unit Id  \
0    qa120  {'A': 123, 'B': 342, 'C': 454}    A108   
1    re015  {'A': 124, 'B': 234, 'C': 343}    P258   

                              NEW  
0  {"A": 123, "C": 454, "B": 342}  
1  {"A": 124, "C": 343, "B": 234}

Upvotes: 0

darthbhyrava
darthbhyrava

Reputation: 515

If the problem is converting the single quote to double quotes without the restraint of doing it after you read it into a dataframe - you could change the .csv file before you read it into a dataframe:

$ sed -i "s/'/\"/g" file_name.csv

If you have to replace them after you read them into a dataframe, try the solution mentioned in this post:

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

Upvotes: 14

jezrael
jezrael

Reputation: 862521

You can convert values to dictionaries like:

import ast

df['<column_name>'] = df['<column_name>'].apply(ast.literal_eval)

But if input data are json file (string), better is use json_normalize.

Upvotes: 0

Related Questions