CODEWITHSUNDEEP
pythonjsonpandasdataframe
NAB0815
NAB0815

Reputation: 471

flattening a json column to multiple columns in a pandas dataframe

I have a pandas dataframe which looks like the following format

   email          name    permissions
  [email protected]   abc    {'system_role': {'title': 'uadmin', 'mask': 64.0}, 'account_roles': [{'role': {'title': 'user', 'mask': 4}}], 'labs_access': True}

I want explode permissions ignoring system_role and labs_access and having only title column from account_roles dict as rest all data is not useful.

expected data frame

   email          name    permissions_role_tile 
  [email protected]   abc           'user'

I tried the following code to flatten the dataframe and drop the columns but its throwing an error TypeError: 'float' object is not iterable. Also, other posts but none of them are working.

df1 = (pd.concat({i: json_normalize(x) for i, x in df.pop('permissions').items()})
         .reset_index(level=1, drop=True)
         .join(df)
         .reset_index(drop=True))

Really appreciate if I can get some help.

Upvotes: 0

Views: 325

Answers (1)

sammywemmy
sammywemmy

Reputation: 28644

You can index into the json column with the str method : 

data = {"email":"[email protected]", "name":"abc","permissions":[{'system_role': {'title': 'uadmin', 'mask': 64.0}, 'account_roles': [{'role': {'title': 'user', 'mask': 4}}], 'labs_access': True}]}
df = pd.DataFrame(data)

df['permissions_role_title'] = df.permissions.str['account_roles'].str[0].str['role'].str['title']

       email       name          permissions             permissions_role_title
0   [email protected]   abc {'system_role': {'title': 'uadmin', 'mask': 64...   user

Upvotes: 1

Related Questions