How to split dataframe made from a json/dict entries into multiple columns for traversal?

user_16255944 0
Viewed 108

I have data that I have pulled from GitHub API for a research project I am involved in. The goal is to pull the issues from a repo so we can streamline our efforts to categorize issues by their # of comments, date of creation, etc. So the idea would be to have a csv or json created that has each issue # and it's respective attributes as one column.

The problem I'm running into is that each issue is separated into rows, but all the information is in one column rather than multiple. Here is a sample of the code:

def run_query(repo_name, repo_owner, sample_amount = 50, state = "CLOSED"):
    request = requests.post('https://api.github.com/graphql', json={'query': gen_query(repo_name, repo_owner, sample_amount, state)}, headers=headers)
    if request.status_code == 200:
        return request.json()
    else:
        raise Exception("Query failed to run by returning code of {}. {}".format(request.status_code, gen_query(repo_name, repo_owner)))

def gen_query(repo_name, repo_owner, sample_amount = 50, state = "CLOSED"):
    query = """
    query {{
        repository(owner: "{}", name: "{}") {{
            issues(last:{}, states:{}) {{
              edges {{
                node {{
                  number
                  url
                  title
                  createdAt
                  comments {{
                    totalCount
                  }}
                  labels(first: 10) {{
                    edges {{
                      node {{
                        name
                      }}
                    }}
                  }}
                }}
              }}
            }}
        }}
    }}""".format(repo_owner, repo_name, sample_amount, state)
    return query

query_res = run_query("yolov5", "ultralytics", sample_amount=3)
query_proc = query_res['data']['repository']['issues']['edges']

which returns this:

[{'node': {'comments': {'totalCount': 1},
           'createdAt': '2021-06-16T05:00:55Z',
           'labels': {'edges': [{'node': {'name': 'question'}}]},
           'number': 3628,
           'title': 'Image resize function in detect.py',
           'url': 'https://github.com/ultralytics/yolov5/issues/3628'}},
 {'node': {'comments': {'totalCount': 4},
           'createdAt': '2021-06-16T10:22:08Z',
           'labels': {'edges': [{'node': {'name': 'question'}}]},
           'number': 3636,
           'title': "What's the difference between 'ema' and 'model' in "
                    'checkpoints',
           'url': 'https://github.com/ultralytics/yolov5/issues/3636'}},
 {'node': {'comments': {'totalCount': 2},
           'createdAt': '2021-06-16T14:52:02Z',
           'labels': {'edges': [{'node': {'name': 'TODO'}},
                                {'node': {'name': 'bug'}}]},
           'number': 3644,
           'title': 'Training crash on first validation: Error in processing '
                    'validate dataset path',
           'url': 'https://github.com/ultralytics/yolov5/issues/3644'}}]

Img of DataFrame of the Dict to show issue better

So it's separated the issues into entries but the actual data from each issue is just one giant text, rather than individual attributes. Ideally I would want it to resemble this dict:

dictionary = {0 : {'number': 3628, 'url': 'https://github.com/ultralytics/yolov5/issues/3628', 'title': 'Image resize function in detect.py', 'labels': 'question', 'created at': '2021-06-16T05:00:55Z', 'comments': 1},
              1 : {'number': 3636, 'url': 'https://github.com/ultralytics/yolov5/issues/3636', 'title': "What's the difference between 'ema' and 'model' in checkpoints", 'labels': 'question', 'created at': '2021-06-16T10:22:08Z', 'comments': 4},
              2 : {'number': 3644, 'url': 'https://github.com/ultralytics/yolov5/issues/3644', 'title': "Training crash on first validation: Error in processing validate dataset path", 'labels': "TODO, question", 'created at': '2021-06-16T14:52:02Z', 'comments': 2}}

Ideal/Properly Formatted DataFrame Example

If you want to look further into it, here is the gist just incase! Any help would be super appreciated!

pandas dataframe dictionary python json
2 Answers

You may iterate on each row to apply your desired output

  • take all values from node key
  • overwrite comments with the totalCount
  • overwrite labels with inner values from labels.edges
v = [{'node': {'comments': {'totalCount': 1}, 'createdAt': '2021-06-16T05:00:55Z',
               'labels': {'edges': [{'node': {'name': 'question'}}]},
               'number': 3628, 'title': 'Image....py', 'url': 'https://.../3628'}},
     {'node': {'comments': {'totalCount': 4}, 'createdAt': '2021-06-16T10:22:08Z',
               'labels': {'edges': [{'node': {'name': 'question'}}]},
               'number': 3636, 'title': "What's ...checkpoints", 'url': 'https://.../3636'}},
     {'node': {'comments': {'totalCount': 2}, 'createdAt': '2021-06-16T14:52:02Z',
               'labels': {'edges': [{'node': {'name': 'TODO'}},
                                    {'node': {'name': 'bug'}}]},
               'number': 3644, 'title': 'Training ... path', 'url': 'https://.../3644'}}]
result = [
    {
        **value['node'],
        'comments': value['node']['comments']['totalCount'],
        'labels': ", ".join(x['node']['name'] for x in value['node']['labels']['edges'])
    }
    for value in v
]

import pandas as pd

df = pd.DataFrame(result)
print(df)
print(df.to_markdown())
comments createdAt labels number title url
0 1 2021-06-16T05:00:55Z question 3628 Image....py https://.../3628
1 4 2021-06-16T10:22:08Z question 3636 What's ...checkpoints https://.../3636
2 2 2021-06-16T14:52:02Z TODO, bug 3644 Training ... path https://.../3644
user_72126860

IIUC, you can try:

df1 = df['node'].apply(pd.Series)
df1['comments'] = df1['comments'].str['totalCount']
df1['labels'].str['edges'].explode().str['node'].str['name'].fillna(
    '').groupby(level=0).agg(', '.join)
user_154380330
Related