Question posted in Json
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Appending multiple values of a json into a Single Pandas column using python

SrikanthB
June 19, 2023
176 views
0 votes
2 Answers

I have trouble in getting values from a Json and storing in a Dataframe.
My Json looks like

{
  "issues": [
    {
      "expand": "operations",
      "id": "1",
      "fields": {
        "customfield_100": [
          {
            "self": "https://url1001",
            "value": "Mobile",
            "id": "1001",
            "disabled": "false"
          }
        ]
      }
    },
    {
      "expand": "operations",
      "id": "2",
      "fields": {
        "customfield_100": [
          {
            "self": "https://url1002",
            "value": "Desktop",
            "id": "1002",
            "disabled": false
          },
          {
            "self": "https://url1001",
            "value": "Mobile",
            "id": "1001",
            "disabled": false
          }
        ]
      }
    },
    {
      "expand": "operations",
      "id": "3",
      "fields": {
        "customfield_100": [
          {
            "self": "https://url1003",
            "value": "ios",
            "id": "1002",
            "disabled": false
          }
        ]
      }
    },
    {
      "expand": "operations",
      "id": "4",
      "fields": {
        "customfield_100": [
          {
            "self": "https://url1002",
            "value": "Desktop",
            "id": "1002",
            "disabled": false
          },
          {
            "self": "https://url1001",
            "value": "Mobile",
            "id": "1001",
            "disabled": false
          },
          {
            "self": "https://url1003",
            "value": "ios",
            "id": "1003",
            "disabled": false
          }
        ]
      }
    }
  ]
}

Below is my part of the code

        df2=pd.dataframe()
        d=pd.json_normalize(json.loads(df1['customfield_100'].to_json(orient='record')))
        filtered_component=[]
        for index in range(len(issues.id)):
             if((pd.json_normalize(df1['customfield_100'][index])).size>0):
                 filtered_component.append(d[0][index]['value']
             else:
                 filtered_component.append('No Component')
          df2['Component']=filterd_component

When i list df2[‘Component’], i am getting the below output

'Mobile'
'Desktop'
'ios'
'Desktop'

I would like my output to be like(when i list df2[components]) i.e if more than single value for customfield_100 then i would like those to be separated by ;. I am not sure how the loop/code should be written for it

'Mobile'
'Desktop';'Mobile'
'ios'
'Desktop';'Mobile';'ios'

Answers

If data contains your parsed Json data then you can do:

all_data = []
for i in data['issues']:
    for k, v in i['fields'].items():
        for vv in v:
            all_data.append({'main_id': i['id'], 'field_id': k, **vv})

df = pd.DataFrame(all_data)
print(df)

This prints:

  main_id         field_id             self    value    id disabled
0       1  customfield_100  https://url1001   Mobile  1001    false
1       2  customfield_100  https://url1002  Desktop  1002    False
2       2  customfield_100  https://url1001   Mobile  1001    False
3       3  customfield_100  https://url1003      ios  1002    False
4       4  customfield_100  https://url1002  Desktop  1002    False
5       4  customfield_100  https://url1001   Mobile  1001    False
6       4  customfield_100  https://url1003      ios  1003    False

Then you can group by main_id, e.g.:

df = df.groupby('main_id')['value'].agg(';'.join)
print(df)

This prints:

main_id
1                Mobile
2        Desktop;Mobile
3                   ios
4    Desktop;Mobile;ios
Name: value, dtype: object

Another possible solution:

df = pd.json_normalize(
    data, 
    record_path=['issues', 'fields', 'customfield_100'], 
    meta=[['issues', 'id']])

df.groupby('issues.id')['value'].agg(';'.join)

where

data = {
  "issues": [
    {
      "expand": "operations",
      "id": "1",
      "fields": {
        "customfield_100": [
          {
            "self": "https://url1001",
            "value": "Mobile",
            "id": "1001",
            "disabled": False
          }
        ]
      }
    },
    {
      "expand": "operations",
      "id": "2",
      "fields": {
        "customfield_100": [
          {
            "self": "https://url1002",
            "value": "Desktop",
            "id": "1002",
            "disabled": False
          },
          {
            "self": "https://url1001",
            "value": "Mobile",
            "id": "1001",
            "disabled": False
          }
        ]
      }
    },
    {
      "expand": "operations",
      "id": "3",
      "fields": {
        "customfield_100": [
          {
            "self": "https://url1003",
            "value": "ios",
            "id": "1002",
            "disabled": False
          }
        ]
      }
    },
    {
      "expand": "operations",
      "id": "4",
      "fields": {
        "customfield_100": [
          {
            "self": "https://url1002",
            "value": "Desktop",
            "id": "1002",
            "disabled": False
          },
          {
            "self": "https://url1001",
            "value": "Mobile",
            "id": "1001",
            "disabled": False
          },
          {
            "self": "https://url1003",
            "value": "ios",
            "id": "1003",
            "disabled": False
          }
        ]
      }
    }
  ]
}

Output:

issues.id
1                Mobile
2        Desktop;Mobile
3                   ios
4    Desktop;Mobile;ios
Name: value, dtype: object

Please signup or login to give your own answer.

Click here to cancel reply.