How to extract value from Data dictionary like values in CSV file in Python Panda data frame

Question

I have Header in CSV file with the data dictionary format like {"Id":"endDate","timeZone":"Z"} in column A , {"Id":"status"} in column B, {"Id":"ipAddress"} in column C in Panda Data Frame. How Can I show only Values in respective column in CSV file?

I tried to replace this code but seems not working.

df1.columns = df1.columns.str.replace(r'{"Id":"$', '')

I want to show only Values like in Column A > endDate, B > status, and C > ipAddress

enter image description here

Expected output enter image description here

question from:https://stackoverflow.com/questions/65713018/how-to-extract-value-from-data-dictionary-like-values-in-csv-file-in-python-pand

Answer 1

You've not actually provided the first row of your CSV which is the column names. I've reconstructed (also chose pipe separated). json.loads() to convert a string into a dict then extract key value you want as column name in a dict comprehension

import json
df1 = pd.read_csv(io.StringIO("""{"Id":"endDate","timeZone":"Z"}|{"Id":"status"}|{"Id":"ipAddress"}"""), sep="|")
df1 = df1.rename(columns={c:json.loads(c)["Id"] for c in df1.columns})
df1.columns

output

Index(['endDate', 'status', 'ipAddress'], dtype='object')

enhanced

• do not rename columns that do not have Id key
• deal with columns that are not a dict

import json
df1 = pd.read_csv(io.StringIO("""{"Id":"endDate","timeZone":"Z"}|{"Id":"status"}|{"Id":"ipAddress"}|{"NoId":"skip"}|notJSON"""), sep="|")
def decode(text):
    try:
        return json.loads(str(text))
    except ValueError:
        return {"Id":text}
df1 = df1.rename(columns={c:decode(c)["Id"] for c in df1.columns if "Id" in decode(c).keys()})
df1.columns