Selecting nested dictionaries and turning them to a DataFrame in Python

user_15747512 0
Viewed 62

Selecting nested dictionaries and turning them to a DataFrame in Python

From the nested 'biblio' data below, is there a way of sorting this into a data frame with each key as a column? For example, where 'classifications_cpc' is a column header with the codes as the subsequent values?

 {
  "publication_reference": {
    "jurisdiction": "US",
    "doc_number": "10236491",
    "kind": "B2",
    "date": "2019-03-19"
  },
  "application_reference": {
    "jurisdiction": "US",
    "doc_number": "201615053025",
    "kind": "A",
    "date": "2016-02-25"
  },
  "priority_claims": {
    "claims": [
      {
        "jurisdiction": "JP",
        "doc_number": "2015062114",
        "kind": "A",
        "date": "2015-03-25",
        "sequence": 1
      }
    ]
  },
  "invention_title": [
    {
      "text": "Lithium ion secondary battery",
      "lang": "en"
    }
  ],
  "parties": {
    "applicants": [
      {
        "residence": "JP",
        "extracted_name": {
          "value": "AUTOMOTIVE ENERGY SUPPLY CORP"
        }
      }
    ],
    "inventors": [
      {
        "residence": "JP",
        "sequence": 1,
        "extracted_name": {
          "value": "SAKAGUCHI SHINICHIRO"
        }
      },
      {
        "residence": "JP",
        "sequence": 2,
        "extracted_name": {
          "value": "KIMURA AIKA"
        }
      },
      {
        "residence": "JP",
        "sequence": 3,
        "extracted_name": {
          "value": "MIZUTA MASATOMO"
        }
      }
    ],
    "agents": [
      {
        "extracted_name": {
          "value": "Troutman Sanders LLP"
        }
      }
    ],
    "owners_all": [
      {
        "recorded_date": "2016-02-25",
        "execution_date": "2016-01-28",
        "extracted_name": {
          "value": "AUTOMOTIVE ENERGY SUPPLY CORPORATION"
        },
        "extracted_address": "10-1, HIRONODAI 2-CHOME, ZAMA-SHI, KANAGAWA, 252-0012",
        "extracted_country": "JP"
      }
    ]
  },
  "classifications_ipcr": {
    "classifications": [
      {
        "symbol": "H01M2/02"
      },
      {
        "symbol": "H01M2/14"
      },
      {
        "symbol": "H01M2/18"
      },
      {
        "symbol": "H01M10/0525"
      },
      {
        "symbol": "H01M10/0585"
      }
    ]
  },
  "classifications_cpc": {
    "classifications": [
      {
        "symbol": "H01M10/0525"
      },
      {
        "symbol": "H01M10/0525"
      },
      {
        "symbol": "H01M50/463"
      },
      {
        "symbol": "H01M10/0525"
      },
      {
        "symbol": "H01M10/0585"
      },
      {
        "symbol": "H01M10/0585"
      },
      {
        "symbol": "H01M50/10"
      },
      {
        "symbol": "H01M50/116"
      },
      {
        "symbol": "H01M50/116"
      },
      {
        "symbol": "H01M50/40"
      },
      {
        "symbol": "H01M50/40"
      },
      {
        "symbol": "H01M50/409"
      },
      {
        "symbol": "H01M50/543"
      },
      {
        "symbol": "H01M50/543"
      },
      {
        "symbol": "Y02E60/10"
      }
    ]
  },
  "references_cited": {
    "citations": [
      {
        "sequence": 1,
        "patcit": {
          "document_id": {
            "jurisdiction": "US",
            "doc_number": "2011151307",
            "kind": "A1",
            "date": "2011-06-23"
          },
          "lens_id": "052-557-140-975-892"
        }
      },
      {
        "sequence": 2,
        "patcit": {
          "document_id": {
            "jurisdiction": "US",
            "doc_number": "2011287301",
            "kind": "A1",
            "date": "2011-11-24"
          },
          "lens_id": "050-516-769-883-801"
        }
      },
      {
        "sequence": 3,
        "patcit": {
          "document_id": {
            "jurisdiction": "US",
            "doc_number": "2014205887",
            "kind": "A1",
            "date": "2014-07-24"
          },
          "lens_id": "041-534-822-806-155"
        }
      },
      {
        "sequence": 4,
        "patcit": {
          "document_id": {
            "jurisdiction": "US",
            "doc_number": "2015056492",
            "kind": "A1",
            "date": "2015-02-26"
          },
          "lens_id": "101-776-463-080-028"
        }
      },
      {
        "sequence": 5,
        "patcit": {
          "document_id": {
            "jurisdiction": "WO",
            "doc_number": "2013047778",
            "kind": "A1",
            "date": "2013-04-04"
          },
          "lens_id": "135-661-134-273-324"
        }
      },
      {
        "sequence": 1,
        "patcit": {
          "document_id": {
            "jurisdiction": "US",
            "doc_number": "2011143183",
            "kind": "A1",
            "date": "2011-06-16"
          },
          "lens_id": "095-161-033-897-779"
        }
      },
      {
        "sequence": 2,
        "patcit": {
          "document_id": {
            "jurisdiction": "US",
            "doc_number": "2014349169",
            "kind": "A1",
            "date": "2014-11-27"
          },
          "lens_id": "075-950-005-288-26X"
        }
      },
      {
        "sequence": 3,
        "patcit": {
          "document_id": {
            "jurisdiction": "US",
            "doc_number": "2015050542",
            "kind": "A1",
            "date": "2015-02-19"
          },
          "lens_id": "003-582-946-821-435"
        }
      },
      {
        "sequence": 4,
        "patcit": {
          "document_id": {
            "jurisdiction": "CN",
            "doc_number": "102124591",
            "kind": "A",
            "date": "2011-07-13"
          },
          "lens_id": "157-805-739-981-807"
        }
      },
      {
        "sequence": 5,
        "patcit": {
          "document_id": {
            "jurisdiction": "CN",
            "doc_number": "104106155",
            "kind": "A",
            "date": "2014-10-15"
          },
          "lens_id": "003-865-201-672-551"
        }
      },
      {
        "sequence": 6,
        "patcit": {
          "document_id": {
            "jurisdiction": "CN",
            "doc_number": "104205416",
            "kind": "A",
            "date": "2014-12-10"
          },
          "lens_id": "182-508-848-265-100"
        }
      },
      {
        "sequence": 7,
        "patcit": {
          "document_id": {
            "jurisdiction": "EP",
            "doc_number": "2747167",
            "kind": "A1",
            "date": "2014-06-25"
          },
          "lens_id": "167-072-626-506-628"
        }
      },
      {
        "sequence": 8,
        "patcit": {
          "document_id": {
            "jurisdiction": "JP",
            "doc_number": "2009277397",
            "kind": "A",
            "date": "2009-11-26"
          },
          "lens_id": "061-699-339-033-165"
        }
      },
      {
        "sequence": 9,
        "nplcit": {
          "text": "Extended European Search Report dated Apr. 14, 2016 issued in corresponding European Patent Application No. 16157356.3."
        }
      }
    ],
    "patent_count": 13,
    "npl_count": 1
  },
  "cited_by": {}
}

Any Suggestions or Ideas?

dictionary nested python
2 Answers

Do you want a column for each and every key? or only specific ones? For example, the cited_by key has no value in it.

However, assign the data you provided to a variable names your_data and try this code:

import pandas as pd
list_for_df =[]
classifications = your_data["classifications_cpc"]
symbol_list = classifications["classifications"]
for symbol in symbol_list:
    list_for_df.append(symbol["symbol"])
df = pd.DataFrame(list_for_df,columns=["classifications_cpc"])

The data frame will look like that:

classifications_cpc
0   H01M10/0525
1   H01M10/0525
2   H01M50/463
3   H01M10/0525
4   H01M10/0585
5   H01M10/0585
6   H01M50/10
7   H01M50/116
8   H01M50/116
9   H01M50/40
10  H01M50/40
11  H01M50/409
12  H01M50/543
13  H01M50/543
14  Y02E60/10
user_155145720

Let me try to approach your requirements. As the column names 'classifications_cpc' or 'parties' or 'classifications_ipcr' are each an array of unequal lengths, it would not make sense to put them together into a single DataFrame. Each resultant row will have unrelated fields grouped together.

What you might be looking for is to extract values using specific key in the nested dictionary or 'lists of dictionaries' . For example using recursive function to extract values using some key:

data = {...nested dictionary or 'lists of dictionaries'...}

def get_vals(nested, key):
    result = []
    if isinstance(nested, list) and nested != []:   #non-empty list
        for lis in nested:
            result.extend(get_vals(lis, key))
    elif isinstance(nested, dict) and nested != {}:   #non-empty dict
        for val in nested.values():
            if isinstance(val, (list, dict)):   #(list or dict) in dict
                result.extend(get_vals(val, key))
        if key in nested.keys():   #key found in dict
            result.append(nested[key])
    return result

get_vals(data, 'value')

Output

['AUTOMOTIVE ENERGY SUPPLY CORP',
 'SAKAGUCHI SHINICHIRO',
 'KIMURA AIKA',
 'MIZUTA MASATOMO',
 'Troutman Sanders LLP',
 'AUTOMOTIVE ENERGY SUPPLY CORPORATION']

Or to look for the key 'classifications', you would get 2 lists from 'classifications_ipcr' and 'classifications_cpc':

get_vals(data, 'classifications')

[[{'symbol': 'H01M2/02'},
  {'symbol': 'H01M2/14'},
  {'symbol': 'H01M2/18'},
  {'symbol': 'H01M10/0525'},
  {'symbol': 'H01M10/0585'}],
 [{'symbol': 'H01M10/0525'},
  {'symbol': 'H01M10/0525'},
  {'symbol': 'H01M50/463'},
  {'symbol': 'H01M10/0525'},
  {'symbol': 'H01M10/0585'},
  {'symbol': 'H01M10/0585'},
  {'symbol': 'H01M50/10'},
  {'symbol': 'H01M50/116'},
  {'symbol': 'H01M50/116'},
  {'symbol': 'H01M50/40'},
  {'symbol': 'H01M50/40'},
  {'symbol': 'H01M50/409'},
  {'symbol': 'H01M50/543'},
  {'symbol': 'H01M50/543'},
  {'symbol': 'Y02E60/10'}]]

Another way is to use built-in function pd.json_normalize(), but you have to identify specific keys linkage to arrive at the data you desire.

df = pd.json_normalize(data['classifications_cpc']['classifications'])

Output df

    symbol
0   H01M10/0525
1   H01M10/0525
2   H01M50/463
3   H01M10/0525
4   H01M10/0585
5   H01M10/0585
6   H01M50/10
7   H01M50/116
8   H01M50/116
9   H01M50/40
10  H01M50/40
11  H01M50/409
12  H01M50/543
13  H01M50/543
14  Y02E60/10
user_123706870
Related