unable to normalize the json data fully

Question

I'm trying to normalize the below json data, but the data of "permissions" array is not getting normalized. I have used the "normalize" method of the pandas but not getting the desired output. I want all the data like ("permissions.id", "permissions.self", "permissions.holder.type" and so on) inside the permissions array for every object(there will be multiple objects in that) Can any one help me out for this?

 {
        "permissionSchemes": [
            {
                "expand": "permissions,user,group,projectRole,field,all",
                "id": 10002,
                "self": "https://api.atlassian.com/ex/jira/bacd1a93-d349-44c5-b786-b79cc80f7d31/rest/api/3/permissionscheme/10002",
                "name": "CDP: Simplified Permission Scheme",
                "scope": {
                    "type": "PROJECT",
                    "project": {
                        "id": "10002"
                    }
                },
                "permissions": [
                    {
                        "id": 11079,
                        "self": "https://api.atlassian.com/ex/jira/bacd1a93-d349-44c5-b786-b79cc80f7d31/rest/api/3/permissionscheme/10002/permission/11079",
                        "holder": {
                            "type": "projectRole",
                            "parameter": "10106",
                            "expand": "projectRole"
                        },
                        "permission": "VIEW_ISSUES"
                    },
                    {
                        "id": 11078,
                        "self": "https://api.atlassian.com/ex/jira/bacd1a93-d349-44c5-b786-b79cc80f7d31/rest/api/3/permissionscheme/10002/permission/11078",
                        "holder": {
                            "type": "projectRole",
                            "parameter": "10106",
                            "expand": "projectRole"
                        },
                        "permission": "VIEW_PROJECTS"
                    },
               
            ]
        }
    ]
}

score 0 · Answer 1 · answered Apr 07 '22 at 14:59

0

You can try with pandas.json_normalize()

df = pd.json_normalize(data['permissionSchemes'], record_path=['permissions'], meta=['expand', 'id', 'self', 'name', ['scope', 'type'], ['scope', 'project', 'id']], record_prefix='permissions.')

   permissions.id  \
0           11079
1           11078

                                                                                                            permissions.self  \
0  https://api.atlassian.com/ex/jira/bacd1a93-d349-44c5-b786-b79cc80f7d31/rest/api/3/permissionscheme/10002/permission/11079
1  https://api.atlassian.com/ex/jira/bacd1a93-d349-44c5-b786-b79cc80f7d31/rest/api/3/permissionscheme/10002/permission/11078

  permissions.permission permissions.holder.type permissions.holder.parameter  \
0            VIEW_ISSUES             projectRole                        10106
1          VIEW_PROJECTS             projectRole                        10106

  permissions.holder.expand                                        expand  \
0               projectRole  permissions,user,group,projectRole,field,all
1               projectRole  permissions,user,group,projectRole,field,all

      id  \
0  10002
1  10002

                                                                                                       self  \
0  https://api.atlassian.com/ex/jira/bacd1a93-d349-44c5-b786-b79cc80f7d31/rest/api/3/permissionscheme/10002
1  https://api.atlassian.com/ex/jira/bacd1a93-d349-44c5-b786-b79cc80f7d31/rest/api/3/permissionscheme/10002

                                name scope.type scope.project.id
0  CDP: Simplified Permission Scheme    PROJECT            10002
1  CDP: Simplified Permission Scheme    PROJECT            10002

answered Apr 07 '22 at 14:59

Ynjxsjmh

28,441
6
34
52

thank you, but the columns I want are(sha_id,description,expand,id,name,permissions,permissions_holder,permissions_holder_expand,permissions_holder_parameter,permissions_holder_type,permissions_id, permissions_permission,permissions_self,self,scope).I have used "df_results = pd.json_normalize(data, record_path='permissionSchemes'])" but by using this I getting only ('expand','id','self','name','permissions','scope_type','scope_project_id','description','permissions_id','sha_id')columns,not getting the inner columns,any way to get all the columns as I mentioned in the starting of this comment. – Kunaal Apr 08 '22 at 05:29
@Kunaal There is no `sha_id` in your example json. – Ynjxsjmh Apr 08 '22 at 05:35
you can ignore that one – Kunaal Apr 08 '22 at 05:39
@Kunaal If you want to get other inner column, you need to specify `meta` argument, see my answer. – Ynjxsjmh Apr 08 '22 at 05:41
no those are not inner columns, those are outer columns, I have used "df_results = pd.json_normalize(data, record_path=['permissionSchemes'])" with this I got ('expand', 'id', 'self', 'name', 'permissions', 'scope_type','scope_project_id', 'description', 'permissions_id', 'sha_id') these columns but not the columns of "permissions" which is inside "permissionSchemes" – Kunaal Apr 08 '22 at 06:10
I want (description,expand,id,name,permissions,permissions_holder,permissions_holder_expand,permissions_holder_parameter,permissions_holder_type,permissions_id,permissions_permission,permissions_self,self,scope) all these columns in a single dataframe, but I'm getting only some of them and if I use the solution that you have given, some of the columns are still missing – Kunaal Apr 08 '22 at 06:13
is there any columns at one time? or I have to get all of them separately and then combine them – Kunaal Apr 08 '22 at 06:19
@Kunaal I think you can `df_results.explode('permissions')` then `.apply(lambda col: pd.Series(col['permissions']), axis=1)` and concat the result to exploded df. – Ynjxsjmh Apr 08 '22 at 06:54
@Kunaal Or you can check https://stackoverflow.com/questions/45418334/using-pandas-json-normalize-on-nested-json-with-arrays – Ynjxsjmh Apr 08 '22 at 07:00
that issue is resolved, I have used two dataframes and the merged them, it worked, but I'm facing a new issue, I'm not getting the "permission.holder" column, can you help me out on this? – Kunaal Apr 08 '22 at 07:04
I have seen that question but I thought that would be unnecessary complicated, not sure – Kunaal Apr 08 '22 at 07:07
@Kunaal If you two dataframes, you can use `add_prefix` to add prefix to dataframe columns. – Ynjxsjmh Apr 08 '22 at 07:50
No need to add any prefix, I'm getting the column names as I want, you can check in the previous comments – Kunaal Apr 08 '22 at 09:24

unable to normalize the json data fully

1 Answers1