How to add Dictionary items in pandas column-CodePudding

I have a dataset in this form

Name     Designation
Jack     [{'dxm':0, 'sal':1, 'pix':0}]
Joe      [{'dxm':0, 'sal':0, 'pix':1}]

I want to make this dataset in this form:

Name     Designation                           dixim     Salary   pixel
Jack     [{'dxm':0, 'sal':1, 'pix':0}]          0         1        0
Joe      [{'dxm':0, 'sal':0, 'pix':1}]          0         0        1

that means the dxm value of dictionary will come to dixim column and so on for sal and pix.

Can someone please suggest me on how to achieve this ??

CodePudding user response：

If by chance your column Designation is a string:

dxm = []
sal = []
pix = []

for i in df["Designation"]:
    chars = str(i).replace("'", '"')
    res = json.loads(chars)
    
    dxm.append(res[0]['dxm'])
    sal.append(res[0]['sal'])
    pix.append(res[0]['pix'])

Then we create the dataframe:

df['dxm'] = dxm
df['sal'] = sal
df['pix'] = pix

The Output:

Name                          Designation   dxm sal pix
0   Jack    [{'dxm':0, 'sal':1, 'pix':0}]   0   1   0
1   Joe     [{'dxm':0, 'sal':0, 'pix':1}]   0   0   1

CodePudding user response：

Use json_normalize with select first value of list:

df = df.join(pd.json_normalize(df['Designation'].str[0]))
print (df)
   Name                       Designation  dxm  sal  pix
0  Jack  [{'dxm': 0, 'sal': 1, 'pix': 0}]    0    1    0
1   Joe  [{'dxm': 0, 'sal': 0, 'pix': 1}]    0    0    1