从Python的嵌套字典到平坦的Pandas数据框

huangapple
go评论79阅读模式
英文:

From Python's nested dictionary to flat Pandas dataframe

问题

I have a nested dictionary of the public information of employment history of people and I would like to construct the panel data similar to the following table.

Here is the nested dictionary.

The nested dictionary for person 1 in the above table is as follows.

  1. {
  2.     'basicInformation': {
  3.         'individualId': 6092353,
  4.         'firstName': 'A',
  5.         'middleName': 'ANTHONY',
  6.         'lastName': 'OLIVETTI',
  7.         'otherNames': ['ALBERT A OLIVETTI', 'ALBERT ANTHONY OLIVETTI', 'ANTHONY A OLIVETTI', 'ANTHONY  OLIVETTI'],
  8.         'bcScope': 'Active',
  9.         'iaScope': 'Active',
  10.         'daysInIndustryCalculatedDate': '10/16/2013'
  11.     },
  12.     'currentEmployments': [
  13.         {
  14.             'firmId': 8174,
  15.             'firmName': 'UBS FINANCIAL SERVICES INC.',
  16.             'iaOnly': 'N',
  17.             'registrationBeginDate': '10/17/2013',
  18.             'firmBCScope': 'ACTIVE',
  19.             'firmIAScope': 'ACTIVE',
  20.             'iaSECNumber': '7163',
  21.             'iaSECNumberType': '801',
  22.             'bdSECNumber': '16267',
  23.             'branchOfficeLocations': [
  24.                 {
  25.                     'locatedAtFlag': 'Y',
  26.                     'supervisedFromFlag': 'N',
  27.                     'privateResidenceFlag': 'N',
  28.                     'branchOfficeId': '88789',
  29.                     'street1': '1251 AVE OF THE AMERICAS',
  30.                     'street2': '2ND FLOOR',
  31.                     'city': 'NEW YORK',
  32.                     'cityAlias': ['MANHATTAN', 'NEW YORK', 'NEW YORK CITY', 'NY', 'NY CITY', 'NYC'],
  33.                     'state': 'NY',
  34.                     'country': 'United States',
  35.                     'zipCode': '10020',
  36.                     'latitude': '40.758908',
  37.                     'longitude': '-73.97902',
  38.                     'geoLocation': '40.758908,-73.97902',
  39.                     'nonRegisteredOfficeFlag': 'N',
  40.                     'elaBeginDate': '07/15/2013'
  41.                 }
  42.             ]
  43.         }
  44.     ],
  45.     'currentIAEmployments': [
  46.         {
  47.             'firmId': 8174,
  48.             'firmName': 'UBS FINANCIAL SERVICES INC.',
  49.             'iaOnly': 'Y',
  50.             'registrationBeginDate': '2/24/2014',
  51.             'firmBCScope': 'ACTIVE',
  52.             'firmIAScope': 'ACTIVE',
  53.             'iaSECNumber': '7163',
  54.             'iaSECNumberType': '801',
  55.             'bdSECNumber': '16267',
  56.             'branchOfficeLocations': [
  57.                 {
  58.                     'locatedAtFlag': 'Y',
  59.                     'supervisedFromFlag': 'N',
  60.                     'privateResidenceFlag': 'N',
  61.                     'branchOfficeId': '88789',
  62.                     'street1': '1251 AVE OF THE AMERICAS',
  63.                     'street2': '2ND FLOOR',
  64.                     'city': 'NEW YORK',
  65.                     'cityAlias': ['MANHATTAN', 'NEW YORK', 'NEW YORK CITY', 'NY', 'NY CITY', 'NYC'],
  66.                     'state': 'NY',
  67.                     'country': 'United States',
  68.                     'zipCode': '10020',
  69.                     'latitude': '40.758908',
  70.                     'longitude': '-73.97902',
  71.                     'geoLocation': '40.758908,-73.97902',
  72.                     'nonRegisteredOfficeFlag': 'N',
  73.                     'elaBeginDate': '07/15/2013'
  74.                 }
  75.             ]
  76.         }
  77.     ],
  78.     'previousEmployments': [],
  79.     'previousIAEmployments': [],
  80.     'disclosureFlag': 'N',
  81.     'iaDisclosureFlag': 'N',
  82.     'disclosures': [],
  83.     'examsCount': {
  84.         'stateExamCount': 1,
  85.         'principalExamCount': 0,
  86.         'productExamCount': 3
  87.     },
  88.     'stateExamCategory': [
  89.         {
  90.             'examCategory': 'Series 66',
  91.             'examName': 'Uniform Combined State Law Examination',
  92.             'examTakenDate': '2/18/2014',
  93.             'examScope': 'BOTH'
  94.         }
  95.     ],
  96.     'principalExamCategory': [],
  97.     'productExamCategory': [
  98.         {
  99.             'examCategory': 'SIE',
  100.             'examName': 'Securities Industry Essentials Examination',
  101.             'examTakenDate': '10/1/2018',
  102.             'examScope': 'BC'
  103.         },
  104.         {
  105.             'examCategory': 'Series 3',
  106.             'examName': 'National Commodity Futures Examination',
  107.             'examTakenDate': '10/27/2014',
  108.             'examScope': 'BC'
  109.         },
  110.         {
  111.             'examCategory': 'Series 7',
  112.             'examName': 'General Securities Representative Examination',
  113.             'examTaken
  115. <details>
  116. <summary>英文:</summary>
  118. I have a nested dictionary of the public information of employment history of people and I would like to construct the panel data similar to the following table.
  120. [![enter image description here][1]][1]
  123. Here is the  nested dictionary.
  125. The nested dictionary for person 1 in the above table is as follows.
  128.     {&#39;basicInformation&#39;: {&#39;individualId&#39;: 6092353,
  129.     &#39;firstName&#39;: &#39;A&#39;,&#39;middleName&#39;: &#39;ANTHONY&#39;,&#39;lastName&#39;: &#39;OLIVETTI&#39;,
  130.     &#39;otherNames&#39;: [&#39;ALBERT A OLIVETTI&#39;,
  131.     &#39;ALBERT ANTHONY OLIVETTI&#39;,
  132.     &#39;ANTHONY A OLIVETTI&#39;,
  133.     &#39;ANTHONY  OLIVETTI&#39;],
  134.     &#39;bcScope&#39;: &#39;Active&#39;,
  135.     &#39;iaScope&#39;: &#39;Active&#39;,
  136.     &#39;daysInIndustryCalculatedDate&#39;: &#39;10/16/2013&#39;},
  137.     &#39;currentEmployments&#39;: [{&#39;firmId&#39;: 8174,
  138.     &#39;firmName&#39;: &#39;UBS FINANCIAL SERVICES INC.&#39;,
  139.     &#39;iaOnly&#39;: &#39;N&#39;,
  140.     &#39;registrationBeginDate&#39;: &#39;10/17/2013&#39;,
  141.     &#39;firmBCScope&#39;: &#39;ACTIVE&#39;,
  142.     &#39;firmIAScope&#39;: &#39;ACTIVE&#39;,
  143.     &#39;iaSECNumber&#39;: &#39;7163&#39;,
  144.     &#39;iaSECNumberType&#39;: &#39;801&#39;,
  145.     &#39;bdSECNumber&#39;: &#39;16267&#39;,
  146.     &#39;branchOfficeLocations&#39;: [{&#39;locatedAtFlag&#39;: &#39;Y&#39;,
  147.     &#39;supervisedFromFlag&#39;: &#39;N&#39;,
  148.     &#39;privateResidenceFlag&#39;: &#39;N&#39;,
  149.     &#39;branchOfficeId&#39;: &#39;88789&#39;,
  150.     &#39;street1&#39;: &#39;1251 AVE OF THE AMERICAS&#39;,
  151.     &#39;street2&#39;: &#39;2ND FLOOR&#39;,
  152.     &#39;city&#39;: &#39;NEW YORK&#39;,
  153.     &#39;cityAlias&#39;: [&#39;MANHATTAN&#39;,
  154.     &#39;NEW YORK&#39;,
  155.     &#39;NEW YORK CITY&#39;,
  156.     &#39;NY&#39;,
  157.     &#39;NY CITY&#39;,
  158.     &#39;NYC&#39;],
  159.     &#39;state&#39;: &#39;NY&#39;,
  160.     &#39;country&#39;: &#39;United States&#39;,
  161.     &#39;zipCode&#39;: &#39;10020&#39;,
  162.     &#39;latitude&#39;: &#39;40.758908&#39;,
  163.     &#39;longitude&#39;: &#39;-73.97902&#39;,
  164.     &#39;geoLocation&#39;: &#39;40.758908,-73.97902&#39;,
  165.     &#39;nonRegisteredOfficeFlag&#39;: &#39;N&#39;,
  166.     &#39;elaBeginDate&#39;: &#39;07/15/2013&#39;}]}],
  167.     &#39;currentIAEmployments&#39;: [{&#39;firmId&#39;: 8174,
  168.     &#39;firmName&#39;: &#39;UBS FINANCIAL SERVICES INC.&#39;,
  169.     &#39;iaOnly&#39;: &#39;Y&#39;,
  170.     &#39;registrationBeginDate&#39;: &#39;2/24/2014&#39;,
  171.     &#39;firmBCScope&#39;: &#39;ACTIVE&#39;,
  172.     &#39;firmIAScope&#39;: &#39;ACTIVE&#39;,
  173.     &#39;iaSECNumber&#39;: &#39;7163&#39;,
  174.     &#39;iaSECNumberType&#39;: &#39;801&#39;,
  175.     &#39;bdSECNumber&#39;: &#39;16267&#39;,
  176.     &#39;branchOfficeLocations&#39;: [{&#39;locatedAtFlag&#39;: &#39;Y&#39;,
  177.      &#39;supervisedFromFlag&#39;: &#39;N&#39;,
  178.      &#39;privateResidenceFlag&#39;: &#39;N&#39;,
  179.      &#39;branchOfficeId&#39;: &#39;88789&#39;,
  180.      &#39;street1&#39;: &#39;1251 AVE OF THE AMERICAS&#39;,
  181.      &#39;street2&#39;: &#39;2ND FLOOR&#39;,
  182.      &#39;city&#39;: &#39;NEW YORK&#39;,
  183.      &#39;cityAlias&#39;: [&#39;MANHATTAN&#39;,
  184.       &#39;NEW YORK&#39;,
  185.       &#39;NEW YORK CITY&#39;,
  186.       &#39;NY&#39;,
  187.       &#39;NY CITY&#39;,
  188.       &#39;NYC&#39;],
  189.      &#39;state&#39;: &#39;NY&#39;,
  190.      &#39;country&#39;: &#39;United States&#39;,
  191.      &#39;zipCode&#39;: &#39;10020&#39;,
  192.      &#39;latitude&#39;: &#39;40.758908&#39;,
  193.      &#39;longitude&#39;: &#39;-73.97902&#39;,
  194.      &#39;geoLocation&#39;: &#39;40.758908,-73.97902&#39;,
  195.      &#39;nonRegisteredOfficeFlag&#39;: &#39;N&#39;,
  196.      &#39;elaBeginDate&#39;: &#39;07/15/2013&#39;}]}],
  197.      &#39;previousEmployments&#39;: [],
  198.      &#39;previousIAEmployments&#39;: [],
  199.      &#39;disclosureFlag&#39;: &#39;N&#39;,
  200.      &#39;iaDisclosureFlag&#39;: &#39;N&#39;,
  201.      &#39;disclosures&#39;: [],
  202.      &#39;examsCount&#39;: {&#39;stateExamCount&#39;: 1,
  203.      &#39;principalExamCount&#39;: 0,
  204.      &#39;productExamCount&#39;: 3},
  205.      &#39;stateExamCategory&#39;: [{&#39;examCategory&#39;: &#39;Series 66&#39;,
  206.      &#39;examName&#39;: &#39;Uniform Combined State Law Examination&#39;,
  207.      &#39;examTakenDate&#39;: &#39;2/18/2014&#39;,
  208.      &#39;examScope&#39;: &#39;BOTH&#39;}],
  209.      &#39;principalExamCategory&#39;: [],
  210.      &#39;productExamCategory&#39;: [{&#39;examCategory&#39;: &#39;SIE&#39;,
  211.      &#39;examName&#39;: &#39;Securities Industry Essentials Examination&#39;,
  212.      &#39;examTakenDate&#39;: &#39;10/1/2018&#39;,
  213.      &#39;examScope&#39;: &#39;BC&#39;},
  214.      {&#39;examCategory&#39;: &#39;Series 3&#39;,
  215.      &#39;examName&#39;: &#39;National Commodity Futures Examination&#39;,
  216.      &#39;examTakenDate&#39;: &#39;10/27/2014&#39;,
  217.      &#39;examScope&#39;: &#39;BC&#39;},
  218.      {&#39;examCategory&#39;: &#39;Series 7&#39;,
  219.      &#39;examName&#39;: &#39;General Securities Representative Examination&#39;,
  220.      &#39;examTakenDate&#39;: &#39;10/17/2013&#39;,
  221.      &#39;examScope&#39;: &#39;BC&#39;}],
  222.      &#39;registrationCount&#39;: {&#39;approvedSRORegistrationCount&#39;: 10,
  223.      &#39;approvedFinraRegistrationCount&#39;: 1,
  224.      &#39;approvedStateRegistrationCount&#39;: 7,
  225.      &#39;approvedIAStateRegistrationCount&#39;: 2},
  226.      &#39;registeredStates&#39;: [{&#39;state&#39;: &#39;California&#39;,
  227.      &#39;regScope&#39;: &#39;BC&#39;,
  228.      &#39;status&#39;: &#39;APPROVED&#39;,
  229.      &#39;regDate&#39;: &#39;5/31/2022&#39;},
  230.      {&#39;state&#39;: &#39;Connecticut&#39;,
  231.      &#39;regScope&#39;: &#39;BC&#39;,
  232.      &#39;status&#39;: &#39;APPROVED&#39;,
  233.      &#39;regDate&#39;: &#39;2/26/2014&#39;},
  234.      {&#39;state&#39;: &#39;Florida&#39;,
  235.      &#39;regScope&#39;: &#39;BC&#39;,
  236.      &#39;status&#39;: &#39;APPROVED&#39;,
  237.      &#39;regDate&#39;: &#39;2/26/2014&#39;},
  238.      {&#39;state&#39;: &#39;New Jersey&#39;,
  239.      &#39;regScope&#39;: &#39;BC&#39;,
  240.      &#39;status&#39;: &#39;APPROVED&#39;,
  241.      &#39;regDate&#39;: &#39;1/23/2014&#39;},
  242.      {&#39;state&#39;: &#39;New Jersey&#39;,
  243.      &#39;regScope&#39;: &#39;IA&#39;,
  244.      &#39;status&#39;: &#39;APPROVED&#39;,
  245.      &#39;regDate&#39;: &#39;2/24/2014&#39;},
  246.      {&#39;state&#39;: &#39;New York&#39;,
  247.      &#39;regScope&#39;: &#39;BC&#39;,
  248.      &#39;status&#39;: &#39;APPROVED&#39;,
  249.      &#39;regDate&#39;: &#39;2/18/2014&#39;},
  250.      {&#39;state&#39;: &#39;New York&#39;,
  251.      &#39;regScope&#39;: &#39;IA&#39;,
  252.      &#39;status&#39;: &#39;APPROVED&#39;,
  253.      &#39;regDate&#39;: &#39;10/26/2021&#39;},
  254.      {&#39;state&#39;: &#39;North Carolina&#39;,
  255.      &#39;regScope&#39;: &#39;BC&#39;,
  256.      &#39;status&#39;: &#39;APPROVED&#39;,
  257.      &#39;regDate&#39;: &#39;5/31/2022&#39;},
  258.      {&#39;state&#39;: &#39;Pennsylvania&#39;,
  259.      &#39;regScope&#39;: &#39;BC&#39;,
  260.      &#39;status&#39;: &#39;APPROVED&#39;,
  261.      &#39;regDate&#39;: &#39;2/26/2014&#39;}],
  262.      &#39;registeredSROs&#39;: [{&#39;sro&#39;: &#39;BOX Exchange LLC&#39;, &#39;status&#39;: &#39;APPROVED&#39;},
  263.      {&#39;sro&#39;: &#39;Cboe Exchange, Inc.&#39;, &#39;status&#39;: &#39;APPROVED&#39;},
  264.      {&#39;sro&#39;: &#39;FINRA&#39;, &#39;status&#39;: &#39;APPROVED&#39;},
  265.      {&#39;sro&#39;: &#39;NYSE American LLC&#39;, &#39;status&#39;: &#39;APPROVED&#39;},
  266.      {&#39;sro&#39;: &#39;NYSE Arca, Inc.&#39;, &#39;status&#39;: &#39;APPROVED&#39;},
  267.      {&#39;sro&#39;: &#39;NYSE Chicago, Inc.&#39;, &#39;status&#39;: &#39;APPROVED&#39;},
  268.      {&#39;sro&#39;: &#39;Nasdaq ISE, LLC&#39;, &#39;status&#39;: &#39;APPROVED&#39;},
  269.      {&#39;sro&#39;: &#39;Nasdaq PHLX LLC&#39;, &#39;status&#39;: &#39;APPROVED&#39;},
  270.      {&#39;sro&#39;: &#39;Nasdaq Stock Market&#39;, &#39;status&#39;: &#39;APPROVED&#39;},
  271.      {&#39;sro&#39;: &#39;New York Stock Exchange&#39;, &#39;status&#39;: &#39;APPROVED&#39;}],
  272.      &#39;brokerDetails&#39;: {&#39;hasBCComments&#39;: &#39;N&#39;,
  273.      &#39;hasIAComments&#39;: &#39;N&#39;,
  274.      &#39;legacyReportStatusDescription&#39;: &#39;Not Requested&#39;}}
  278. The nested dictionary for person 2 in the above table is as follows.
  283.     {&#39;basicInformation&#39;: {&#39;individualId&#39;: 2652161,
  284.     &#39;firstName&#39;: &#39;ALBERT&#39;,
  285.     &#39;middleName&#39;: &#39;B&#39;,
  286.     &#39;lastName&#39;: &#39;HORMAN&#39;,
  287.     &#39;otherNames&#39;: [&#39;A B HORMAN&#39;, &#39;ALBERT WILLIAM HORMAN&#39;, &#39;BILL  HORMAN&#39;],
  288.     &#39;bcScope&#39;: &#39;Active&#39;,
  289.     &#39;iaScope&#39;: &#39;Active&#39;,
  290.     &#39;daysInIndustryCalculatedDate&#39;: &#39;9/17/1995&#39;},
  291.     &#39;currentEmployments&#39;: [{&#39;firmId&#39;: 7784,
  292.     &#39;firmName&#39;: &#39;FIDELITY BROKERAGE SERVICES LLC&#39;,
  293.     &#39;iaOnly&#39;: &#39;N&#39;,
  294.     &#39;registrationBeginDate&#39;: &#39;1/1/2008&#39;,
  295.     &#39;firmBCScope&#39;: &#39;ACTIVE&#39;,
  296.     &#39;firmIAScope&#39;: &#39;NOTINSCOPE&#39;,
  297.     &#39;bdSECNumber&#39;: &#39;23292&#39;,
  298.     &#39;branchOfficeLocations&#39;: [{&#39;locatedAtFlag&#39;: &#39;Y&#39;,
  299.     &#39;supervisedFromFlag&#39;: &#39;N&#39;,
  300.     &#39;privateResidenceFlag&#39;: &#39;N&#39;,
  301.     &#39;branchOfficeId&#39;: &#39;369366&#39;,
  302.      &#39;street1&#39;: &#39;825 EAST 1180 SOUTH&#39;,
  303.      &#39;city&#39;: &#39;AMERICAN FORK&#39;,
  304.      &#39;cityAlias&#39;: [&#39;AM FORK&#39;, &#39;AMERICAN FORK&#39;, &#39;HIGHLAND&#39;, &#39;TIMPANOGOS&#39;],
  305.      &#39;state&#39;: &#39;UT&#39;,
  306.      &#39;country&#39;: &#39;United States&#39;,
  307.      &#39;zipCode&#39;: &#39;84003&#39;,
  308.      &#39;latitude&#39;: &#39;40.405984&#39;,
  309.      &#39;longitude&#39;: &#39;-111.82903&#39;,
  310.      &#39;geoLocation&#39;: &#39;40.405984,-111.82903&#39;,
  311.      &#39;nonRegisteredOfficeFlag&#39;: &#39;N&#39;,
  312.      &#39;elaBeginDate&#39;: &#39;07/04/2022&#39;}]}],
  313.      &#39;currentIAEmployments&#39;: [{&#39;firmId&#39;: 288590,
  314.      &#39;firmName&#39;: &#39;FIDELITY PERSONAL AND WORKPLACE ADVISORS&#39;,
  315.      &#39;iaOnly&#39;: &#39;Y&#39;,
  316.      &#39;registrationBeginDate&#39;: &#39;7/13/2018&#39;,
  317.      &#39;firmBCScope&#39;: &#39;NOTINSCOPE&#39;,
  318.      &#39;firmIAScope&#39;: &#39;ACTIVE&#39;,
  319.      &#39;iaSECNumber&#39;: &#39;112027&#39;,
  320.      &#39;iaSECNumberType&#39;: &#39;801&#39;,
  321.      &#39;branchOfficeLocations&#39;: [{&#39;locatedAtFlag&#39;: &#39;Y&#39;,
  322.      &#39;supervisedFromFlag&#39;: &#39;N&#39;,
  323.      &#39;privateResidenceFlag&#39;: &#39;N&#39;,
  324.      &#39;street1&#39;: &#39;245 SUMMER STREET, V2A&#39;,
  325.      &#39;city&#39;: &#39;BOSTON&#39;,
  326.      &#39;cityAlias&#39;: [&#39;BOSTON&#39;],
  327.      &#39;state&#39;: &#39;MA&#39;,
  328.      &#39;country&#39;: &#39;United States&#39;,
  329.      &#39;zipCode&#39;: &#39;02210&#39;,
  330.      &#39;latitude&#39;: &#39;42.346571&#39;,
  331.      &#39;longitude&#39;: &#39;-71.039563&#39;,
  332.      &#39;geoLocation&#39;: &#39;42.346571,-71.039563&#39;,
  333.      &#39;nonRegisteredOfficeFlag&#39;: &#39;Y&#39;,
  334.      &#39;elaBeginDate&#39;: &#39;07/13/2018&#39;}]}],
  335.      &#39;previousEmployments&#39;: [{&#39;iaOnly&#39;: &#39;N&#39;,
  336.      &#39;bdSECNumber&#39;: &#39;35097&#39;,
  337.      &#39;firmId&#39;: 17507,
  338.      &#39;firmName&#39;: &#39;FIDELITY INVESTMENTS INSTITUTIONAL SERVICES COMPANY, INC.&#39;,
  339.      &#39;street1&#39;: &#39;49 NORTH 400 WEST&#39;,
  340.       &#39;city&#39;: &#39;SALT LAKE CITY&#39;,
  341.       &#39;state&#39;: &#39;UT&#39;,
  342.       &#39;zipCode&#39;: &#39;84101&#39;,
  343.       &#39;registrationBeginDate&#39;: &#39;1/3/2001&#39;,
  344.      &#39;registrationEndDate&#39;: &#39;1/1/2008&#39;,
  345.      &#39;firmBCScope&#39;: &#39;ACTIVE&#39;,
  346.      &#39;firmIAScope&#39;: &#39;NOTINSCOPE&#39;},
  347.      {&#39;iaOnly&#39;: &#39;N&#39;,
  348.      &#39;bdSECNumber&#39;: &#39;23292&#39;,
  349.       &#39;firmId&#39;: 7784,
  350.      &#39;firmName&#39;: &#39;FIDELITY BROKERAGE SERVICES LLC&#39;,
  351.      &#39;street1&#39;: &#39;900 SALEM STREET&#39;,
  352.      &#39;city&#39;: &#39;SMITHFIELD&#39;,
  353.      &#39;state&#39;: &#39;RI&#39;,
  354.      &#39;country&#39;: &#39;UNITED STATES&#39;,
  355.      &#39;zipCode&#39;: &#39;02917&#39;,
  356.      &#39;registrationBeginDate&#39;: &#39;9/18/1995&#39;,
  357.      &#39;registrationEndDate&#39;: &#39;1/4/2001&#39;,
  358.      &#39;firmBCScope&#39;: &#39;ACTIVE&#39;,
  359.      &#39;firmIAScope&#39;: &#39;NOTINSCOPE&#39;}],
  360.      &#39;previousIAEmployments&#39;: [{&#39;iaOnly&#39;: &#39;Y&#39;,
  361.      &#39;iaSECNumber&#39;: &#39;13243&#39;,
  362.      &#39;iaSECNumberType&#39;: &#39;801&#39;,
  363.      &#39;firmId&#39;: 104555,
  364.      &#39;firmName&#39;: &#39;STRATEGIC ADVISERS LLC&#39;,
  365.      &#39;street1&#39;: &#39;49 NORTH 400 WEST&#39;,
  366.      &#39;city&#39;: &#39;SALT LAKE CITY&#39;,
  367.      &#39;state&#39;: &#39;UT&#39;,
  368.      &#39;country&#39;: &#39;United States&#39;,
  369.      &#39;zipCode&#39;: &#39;84101&#39;,
  370.      &#39;registrationBeginDate&#39;: &#39;2/15/2008&#39;,
  371.      &#39;registrationEndDate&#39;: &#39;7/13/2018&#39;,
  372.      &#39;firmBCScope&#39;: &#39;NOTINSCOPE&#39;,
  373.      &#39;firmIAScope&#39;: &#39;ACTIVE&#39;}],
  374.      &#39;disclosureFlag&#39;: &#39;N&#39;,
  375.      &#39;iaDisclosureFlag&#39;: &#39;N&#39;,
  376.      &#39;disclosures&#39;: [],
  377.      &#39;examsCount&#39;: {&#39;stateExamCount&#39;: 2,
  378.      &#39;principalExamCount&#39;: 0,
  379.      &#39;productExamCount&#39;: 2},
  380.      &#39;stateExamCategory&#39;: [{&#39;examCategory&#39;: &#39;Series 66&#39;,
  381.      &#39;examName&#39;: &#39;Uniform Combined State Law Examination&#39;,
  382.      &#39;examTakenDate&#39;: &#39;2/26/2008&#39;,
  383.      &#39;examScope&#39;: &#39;BOTH&#39;},
  384.      {&#39;examCategory&#39;: &#39;Series 63&#39;,
  385.      &#39;examName&#39;: &#39;Uniform Securities Agent State Law Examination&#39;,
  386.      &#39;examTakenDate&#39;: &#39;9/7/1995&#39;,
  387.      &#39;examScope&#39;: &#39;BC&#39;}],
  388.      &#39;principalExamCategory&#39;: [],
  389.      &#39;productExamCategory&#39;: [{&#39;examCategory&#39;: &#39;SIE&#39;,
  390.      &#39;examName&#39;: &#39;Securities Industry Essentials Examination&#39;,
  391.      &#39;examTakenDate&#39;: &#39;10/1/2018&#39;,
  392.      &#39;examScope&#39;: &#39;BC&#39;},
  393.      {&#39;examCategory&#39;: &#39;Series 7&#39;,
  394.      &#39;examName&#39;: &#39;General Securities Representative Examination&#39;,
  395.      &#39;examTakenDate&#39;: &#39;9/16/1995&#39;,
  396.      &#39;examScope&#39;: &#39;BC&#39;}],
  397.      &#39;registrationCount&#39;: {&#39;approvedSRORegistrationCount&#39;: 2,
  398.      &#39;approvedFinraRegistrationCount&#39;: 1,
  399.      &#39;approvedStateRegistrationCount&#39;: 52,
  400.      &#39;approvedIAStateRegistrationCount&#39;: 2},
  401.      &#39;registeredStates&#39;: [{&#39;state&#39;: &#39;Alabama&#39;,
  402.      &#39;regScope&#39;: &#39;BC&#39;,
  403.      &#39;status&#39;: &#39;APPROVED&#39;,
  404.      &#39;regDate&#39;: &#39;1/1/2008&#39;},
  405.      {&#39;state&#39;: &#39;Alaska&#39;,
  406.      &#39;regScope&#39;: &#39;BC&#39;,
  407.      &#39;status&#39;: &#39;APPROVED&#39;,
  408.      &#39;regDate&#39;: &#39;1/1/2008&#39;},
  409.      {&#39;state&#39;: &#39;Arizona&#39;,
  410.      &#39;regScope&#39;: &#39;BC&#39;,
  411.      &#39;status&#39;: &#39;APPROVED&#39;,
  412.      &#39;regDate&#39;: &#39;1/1/2008&#39;},
  413.      {&#39;state&#39;: &#39;Arkansas&#39;,
  414.      &#39;regScope&#39;: &#39;BC&#39;,
  415.      &#39;status&#39;: &#39;APPROVED&#39;,
  416.      &#39;regDate&#39;: &#39;1/1/2008&#39;},
  417.      {&#39;state&#39;: &#39;California&#39;,
  418.      &#39;regScope&#39;: &#39;BC&#39;,
  419.      &#39;status&#39;: &#39;APPROVED&#39;,
  420.      &#39;regDate&#39;: &#39;1/1/2008&#39;},
  421.      {&#39;state&#39;: &#39;Colorado&#39;,
  422.      &#39;regScope&#39;: &#39;BC&#39;,
  423.      &#39;status&#39;: &#39;APPROVED&#39;,
  424.      &#39;regDate&#39;: &#39;1/1/2008&#39;},
  425.      {&#39;state&#39;: &#39;Connecticut&#39;,
  426.      &#39;regScope&#39;: &#39;BC&#39;,
  427.      &#39;status&#39;: &#39;APPROVED&#39;,
  428.      &#39;regDate&#39;: &#39;1/1/2008&#39;},
  429.     {&#39;state&#39;: &#39;Delaware&#39;,
  430.     &#39;regScope&#39;: &#39;BC&#39;,
  431.     &#39;status&#39;: &#39;APPROVED&#39;,
  432.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  433.     {&#39;state&#39;: &#39;District of Columbia&#39;,
  434.     &#39;regScope&#39;: &#39;BC&#39;,
  435.     &#39;status&#39;: &#39;APPROVED&#39;,
  436.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  437.     {&#39;state&#39;: &#39;Florida&#39;,
  438.     &#39;regScope&#39;: &#39;BC&#39;,
  439.     &#39;status&#39;: &#39;APPROVED&#39;,
  440.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  441.     {&#39;state&#39;: &#39;Georgia&#39;,
  442.     &#39;regScope&#39;: &#39;BC&#39;,
  443.     &#39;status&#39;: &#39;APPROVED&#39;,
  444.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  445.     {&#39;state&#39;: &#39;Hawaii&#39;,
  446.     &#39;regScope&#39;: &#39;BC&#39;,
  447.     &#39;status&#39;: &#39;APPROVED&#39;,
  448.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  449.     {&#39;state&#39;: &#39;Idaho&#39;,
  450.     &#39;regScope&#39;: &#39;BC&#39;,
  451.     &#39;status&#39;: &#39;APPROVED&#39;,
  452.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  453.     {&#39;state&#39;: &#39;Illinois&#39;,
  454.     &#39;regScope&#39;: &#39;BC&#39;,
  455.     &#39;status&#39;: &#39;APPROVED&#39;,
  456.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  457.     {&#39;state&#39;: &#39;Indiana&#39;,
  458.     &#39;regScope&#39;: &#39;BC&#39;,
  459.     &#39;status&#39;: &#39;APPROVED&#39;,
  460.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  461.     {&#39;state&#39;: &#39;Iowa&#39;,
  462.     &#39;regScope&#39;: &#39;BC&#39;,
  463.     &#39;status&#39;: &#39;APPROVED&#39;,
  464.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  465.     {&#39;state&#39;: &#39;Kansas&#39;,
  466.     &#39;regScope&#39;: &#39;BC&#39;,
  467.     &#39;status&#39;: &#39;APPROVED&#39;,
  468.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  469.     {&#39;state&#39;: &#39;Kentucky&#39;,
  470.     &#39;regScope&#39;: &#39;BC&#39;,
  471.     &#39;status&#39;: &#39;APPROVED&#39;,
  472.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  473.     {&#39;state&#39;: &#39;Louisiana&#39;,
  474.     &#39;regScope&#39;: &#39;BC&#39;,
  475.     &#39;status&#39;: &#39;APPROVED&#39;,
  476.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  477.      {&#39;state&#39;: &#39;Maine&#39;,
  478.      &#39;regScope&#39;: &#39;BC&#39;,
  479.      &#39;status&#39;: &#39;APPROVED&#39;,
  480.      &#39;regDate&#39;: &#39;1/1/2008&#39;},
  481.      {&#39;state&#39;: &#39;Maryland&#39;,
  482.     &#39;regScope&#39;: &#39;BC&#39;,
  483.     &#39;status&#39;: &#39;APPROVED&#39;,
  484.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  485.     {&#39;state&#39;: &#39;Massachusetts&#39;,
  486.      &#39;regScope&#39;: &#39;BC&#39;,
  487.     &#39;status&#39;: &#39;APPROVED&#39;,
  488.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  489.     {&#39;state&#39;: &#39;Michigan&#39;,
  490.     &#39;regScope&#39;: &#39;BC&#39;,
  491.     &#39;status&#39;: &#39;APPROVED&#39;,
  492.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  493.     {&#39;state&#39;: &#39;Minnesota&#39;,
  494.      &#39;regScope&#39;: &#39;BC&#39;,
  495.     &#39;status&#39;: &#39;APPROVED&#39;,
  496.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  497.     {&#39;state&#39;: &#39;Mississippi&#39;,
  498.     &#39;regScope&#39;: &#39;BC&#39;,
  499.     &#39;status&#39;: &#39;APPROVED&#39;,
  500.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  501.     {&#39;state&#39;: &#39;Missouri&#39;,
  502.     &#39;regScope&#39;: &#39;BC&#39;,
  503.     &#39;status&#39;: &#39;APPROVED&#39;,
  504.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  505.     {&#39;state&#39;: &#39;Montana&#39;,
  506.     &#39;regScope&#39;: &#39;BC&#39;,
  507.     &#39;status&#39;: &#39;APPROVED&#39;,
  508.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  509.     {&#39;state&#39;: &#39;Nebraska&#39;,
  510.     &#39;regScope&#39;: &#39;BC&#39;,
  511.     &#39;status&#39;: &#39;APPROVED&#39;,
  512.     &#39;regDate&#39;: &#39;1/1/2008&#39;},
  513.       {&#39;state&#39;: &#39;Nevada&#39;,
  514.       &#39;regScope&#39;: &#39;BC&#39;,
  515.       &#39;status&#39;: &#39;APPROVED&#39;,
  516.       &#39;regDate&#39;: &#39;1/1/2008&#39;},
  517.        {&#39;state&#39;: &#39;New Hampshire&#39;,
  518.        &#39;regScope&#39;: &#39;BC&#39;,
  519.       &#39;status&#39;: &#39;APPROVED&#39;,
  520.       &#39;regDate&#39;: &#39;1/1/2008&#39;},
  521.       {&#39;state&#39;: &#39;New Jersey&#39;,
  522.       &#39;regScope&#39;: &#39;BC&#39;,
  523.       &#39;status&#39;: &#39;APPROVED&#39;,
  524.       &#39;regDate&#39;: &#39;1/1/2008&#39;},
  525.       {&#39;state&#39;: &#39;New Mexico&#39;,
  526.       &#39;regScope&#39;: &#39;BC&#39;,
  527.       &#39;status&#39;: &#39;APPROVED&#39;,
  528.       &#39;regDate&#39;: &#39;1/1/2008&#39;},
  529.       {&#39;state&#39;: &#39;New York&#39;,
  530.       &#39;regScope&#39;: &#39;BC&#39;,
  531.       &#39;status&#39;: &#39;APPROVED&#39;,
  532.       &#39;regDate&#39;: &#39;1/1/2008&#39;},
  533.       {&#39;state&#39;: &#39;North Carolina&#39;,
  534.        &#39;regScope&#39;: &#39;BC&#39;,
  535.        &#39;status&#39;: &#39;APPROVED&#39;,
  536.       &#39;regDate&#39;: &#39;1/1/2008&#39;},
  537.       {&#39;state&#39;: &#39;North Dakota&#39;,
  538.       &#39;regScope&#39;: &#39;BC&#39;,
  539.        &#39;status&#39;: &#39;APPROVED&#39;,
  540.        &#39;regDate&#39;: &#39;1/1/2008&#39;},
  541.       {&#39;state&#39;: &#39;Ohio&#39;,
  542.       &#39;regScope&#39;: &#39;BC&#39;,
  543.        &#39;status&#39;: &#39;APPROVED&#39;,
  544.        &#39;regDate&#39;: &#39;1/1/2008&#39;},
  545.        {&#39;state&#39;: &#39;Oklahoma&#39;,
  546.        &#39;regScope&#39;: &#39;BC&#39;,
  547.        &#39;status&#39;: &#39;APPROVED&#39;,
  548.        &#39;regDate&#39;: &#39;1/1/2008&#39;},
  549.        {&#39;state&#39;: &#39;Oregon&#39;,
  550.        &#39;regScope&#39;: &#39;BC&#39;,
  551.        &#39;status&#39;: &#39;APPROVED&#39;,
  552.        &#39;regDate&#39;: &#39;1/1/2008&#39;},
  553.        {&#39;state&#39;: &#39;Pennsylvania&#39;,
  554.        &#39;regScope&#39;: &#39;BC&#39;,
  555.        &#39;status&#39;: &#39;APPROVED&#39;,
  556.        &#39;regDate&#39;: &#39;1/1/2008&#39;},
  557.        {&#39;state&#39;: &#39;Puerto Rico&#39;,
  558.        &#39;regScope&#39;: &#39;BC&#39;,
  559.        &#39;status&#39;: &#39;APPROVED&#39;,
  560.        &#39;regDate&#39;: &#39;1/1/2008&#39;},
  561.        {&#39;state&#39;: &#39;Rhode Island&#39;,
  562.        &#39;regScope&#39;: &#39;BC&#39;,
  563.        &#39;status&#39;: &#39;APPROVED&#39;,
  564.         &#39;regDate&#39;: &#39;1/1/2008&#39;},
  565.        {&#39;state&#39;: &#39;South Carolina&#39;,
  566.        &#39;regScope&#39;: &#39;BC&#39;,
  567.        &#39;status&#39;: &#39;APPROVED&#39;,
  568.        &#39;regDate&#39;: &#39;1/1/2008&#39;},
  569.        {&#39;state&#39;: &#39;South Dakota&#39;,
  570.        &#39;regScope&#39;: &#39;BC&#39;,
  571.        &#39;status&#39;: &#39;APPROVED&#39;,
  572.        &#39;regDate&#39;: &#39;1/1/2008&#39;},
  573.        {&#39;state&#39;: &#39;Tennessee&#39;,
  574.        &#39;regScope&#39;: &#39;BC&#39;,
  575.        &#39;status&#39;: &#39;APPROVED&#39;,
  576.        &#39;regDate&#39;: &#39;1/1/2008&#39;},
  577.        {&#39;state&#39;: &#39;Texas&#39;,
  578.         &#39;regScope&#39;: &#39;BC&#39;,
  579.        &#39;status&#39;: &#39;APPROVED&#39;,
  580.       &#39;regDate&#39;: &#39;1/1/2008&#39;},
  581.       {&#39;state&#39;: &#39;Texas&#39;,
  582.       &#39;regScope&#39;: &#39;IA&#39;,
  583.       &#39;status&#39;: &#39;APPROVED_RES&#39;,
  584.       &#39;regDate&#39;: &#39;7/13/2018&#39;},
  585.       {&#39;state&#39;: &#39;Utah&#39;,
  586.        &#39;regScope&#39;: &#39;BC&#39;,
  587.       &#39;status&#39;: &#39;APPROVED&#39;,
  588.       &#39;regDate&#39;: &#39;1/1/2008&#39;},
  589.       {&#39;state&#39;: &#39;Utah&#39;,
  590.       &#39;regScope&#39;: &#39;IA&#39;,
  591.       &#39;status&#39;: &#39;APPROVED&#39;,
  592.       &#39;regDate&#39;: &#39;7/13/2018&#39;},
  593.       {&#39;state&#39;: &#39;Vermont&#39;,
  594.       &#39;regScope&#39;: &#39;BC&#39;,
  595.       &#39;status&#39;: &#39;APPROVED&#39;,
  596.       &#39;regDate&#39;: &#39;1/1/2008&#39;},
  597.       {&#39;state&#39;: &#39;Virginia&#39;,
  598.       &#39;regScope&#39;: &#39;BC&#39;,
  599.       &#39;status&#39;: &#39;APPROVED&#39;,
  600.       &#39;regDate&#39;: &#39;1/1/2008&#39;},
  601.       {&#39;state&#39;: &#39;Washington&#39;,
  602.       &#39;regScope&#39;: &#39;BC&#39;,
  603.       &#39;status&#39;: &#39;APPROVED&#39;,
  604.       &#39;regDate&#39;: &#39;1/1/2008&#39;},
  605.       {&#39;state&#39;: &#39;West Virginia&#39;,
  606.       &#39;regScope&#39;: &#39;BC&#39;,
  607.       &#39;status&#39;: &#39;APPROVED&#39;,
  608.       &#39;regDate&#39;: &#39;1/1/2008&#39;},
  609.       {&#39;state&#39;: &#39;Wisconsin&#39;,
  610.       &#39;regScope&#39;: &#39;BC&#39;,
  611.       &#39;status&#39;: &#39;APPROVED&#39;,
  612.       &#39;regDate&#39;: &#39;1/1/2008&#39;},
  613.       {&#39;state&#39;: &#39;Wyoming&#39;,
  614.       &#39;regScope&#39;: &#39;BC&#39;,
  615.       &#39;status&#39;: &#39;APPROVED&#39;,
  616.       &#39;regDate&#39;: &#39;1/1/2008&#39;}],
  617.       &#39;registeredSROs&#39;: [{&#39;sro&#39;: &#39;FINRA&#39;, &#39;status&#39;: &#39;APPROVED&#39;},
  618.       {&#39;sro&#39;: &#39;New York Stock Exchange&#39;, &#39;status&#39;: &#39;APPROVED&#39;}],
  619.       &#39;brokerDetails&#39;: {&#39;hasBCComments&#39;: &#39;N&#39;,
  620.       &#39;hasIAComments&#39;: &#39;N&#39;,
  621.        &#39;legacyReportStatusDescription&#39;: &#39;Not Requested&#39;}}
  625. What I have tried to do is to implement JSON normalize and JSON flatten. I have modified the code like this for person 1 and person 2
  627.      import pandas as pds
  628.      from flatten_json import flatten
  629.      import json 
  631.      #person_json is what I stored each person JSON. There are 2 
  632.      #persons here. Thus, I do this two times to flatten the nested 
  633.      #dictionary.
  635.      person_temp = pds.json_normalize(flatten(person_json))
  637.      # This line of the code is credited to Mr.Timeless
  638.  
  639.      data_frame = (person_temp.set_axis(person_temp.columns.str.split(&quot;_&quot;, n=1, 
  640.      expand=True), axis=1).stack(1).droplevel(0))
  642.      data_frame
  644. Edited 1: Adding the captured photo of data_frame
  646. The sample data_frame looks like this. I show only some parts of data_frame because the dimension is equal to 111 rows-by-16 columns.
  648. [![enter image description here][2]][2]
  652. What I get from the above code is a data frame. However, I try to manage to construct the panel data like the first captured photo that I presented. The issue I found here is to extract &#39;Year&#39; and &#39;City&#39; and to construct them into the (unbalanced) panel data set.
  654. How should I do this?
  656. Any suggestions/comments are welcome.
  658. Thank you very much
  661.   [1]: https://i.stack.imgur.com/QQjPP.png
  662.   [2]: https://i.stack.imgur.com/Wkr1a.png
  664. </details>
  667. # 答案1
  668. **得分**: 1
  670. 我建议采用不同的方法。
  672. 首先，定义以下辅助函数：
  673. ```python
  674. import pandas as pd
  676. def flatten(data, new_data):
  677.     for key, value in data.items():
  678.         if isinstance(value, dict):
  679.             flatten(value, new_data)
  680.         if isinstance(value, str) or isinstance(value, int) or isinstance(value, list):
  681.             new_data[key] = value
  682.     return new_data
  684. def deal_with_dicts(df, columns):
  685.     for col in columns:
  686.         df = pd.concat([df, pd.json_normalize(df[col])], axis=1)
  687.         df = df.drop(columns=col)
  688.     return df
  690. def deal_with_duplicated_column_names(df):
  691.     duplicates = {k: 1 for k in df.columns}
  692.     new_cols = []
  693.     for col in df.columns:
  694.         if col in new_cols:
  695.             new_cols.append(col + f"_{duplicates[col]}")
  696.             duplicates[col] += 1
  697.         else:
  698.             new_cols.append(col)
  699.     df.columns = new_cols
  700.     return df

然后：

  1. from collections import defaultdict
  3. person1_data = flatten(person1, defaultdict(list))
  4. df = pd.json_normalize(person1_data)
  6. # ROUND 1
  7. for col in df.columns:
  8.     df = df.explode(col)  # 处理包含字典列表的列
  9. df = df.reset_index(drop=True)
  10. df = deal_with_dicts(
  11.     df,
  12.     [
  13.         "currentEmployments",
  14.         "currentIAEmployments",
  15.         "stateExamCategory",
  16.         "productExamCategory",
  17.         "registeredStates",
  18.         "registeredSROs",
  19.     ],
  20. )
  21. df = deal_with_duplicated_column_names(df)
  23. # ROUND 2
  24. for col in df.columns:
  25.     df = df.explode(col)  # 处理包含字典列表的列
  26. df = df.reset_index(drop=True)
  27. df = deal_with_dicts(df, ["branchOfficeLocations", "branchOfficeLocations_1"])
  28. df = deal_with_duplicated_column_names(df)
  30. # ROUND 3
  31. for col in df.columns:
  32.     df = df.explode(col)  # 处理包含字典列表的列
  33. df = df.reset_index(drop=True)

这将为您提供来自 person1 字典的所有数据，并将其展平为一个数据框：

  1. print(df.info())
  2. # 输出结果
  4. [38880 行 x 88 列]
  5. <class 'pandas.core.frame.DataFrame'>
  6. RangeIndex: 38880 entries, 0 to 38879
  7. Data columns (total 88 columns):
  8.  #   Column                            Non-Null Count  Dtype
  9. ---  ------                            --------------  -----
  10.  0   individualId                      38880 non-null  int64
  11.  1   firstName                         38880 non-null  object
  12.  2   middleName                        38880 non-null  object
  13.  3   lastName                          38880 non-null  object
  14.  4   otherNames                        38880 non-null  object
  15. ...
  16. # 其他列信息
  17. ...
  18.  87  elaBeginDate_1                    38880 non-null  object
  19. dtypes: int64(10), object(78)
  20. memory usage: 26.1+ MB
英文:

I suggest a different approach.

First, define the following helper functions:

  1. import pandas as pd
  4. def flatten(data, new_data):
  5.     for key, value in data.items():
  6.         if isinstance(value, dict):
  7.             flatten(value, new_data)
  8.         if isinstance(value, str) or isinstance(value, int) or isinstance(value, list):
  9.             new_data[key] = value
  10.     return new_data
  13. def deal_with_dicts(df, columns):
  14.     for col in columns:
  15.         df = pd.concat([df, pd.json_normalize(df[col])], axis=1)
  16.         df = df.drop(columns=col)
  17.     return df
  20. def deal_with_duplicated_column_names(df):
  21.     duplicates = {k: 1 for k in df.columns}
  22.     new_cols = []
  23.     for col in df.columns:
  24.         if col in new_cols:
  25.             new_cols.append(col + f&quot;_{duplicates[col]}&quot;)
  26.             duplicates[col] += 1
  27.         else:
  28.             new_cols.append(col)
  29.     df.columns = new_cols
  30.     return df

Then:

  1. from collections import defaultdict
  4. person1_data = flatten(person1, defaultdict(list))
  5. df = pd.json_normalize(person1_data)
  7. # ROUND 1
  8. for col in df.columns:
  9.     df = df.explode(col)  # Deal with lists of dicts
  10. df = df.reset_index(drop=True)
  11. df = deal_with_dicts(
  12.     df,
  13.     [
  14.         &quot;currentEmployments&quot;,
  15.         &quot;currentIAEmployments&quot;,
  16.         &quot;stateExamCategory&quot;,
  17.         &quot;productExamCategory&quot;,
  18.         &quot;registeredStates&quot;,
  19.         &quot;registeredSROs&quot;,
  20.     ],
  21. )
  22. df = deal_with_duplicated_column_names(df)
  24. # ROUND 2
  25. for col in df.columns:
  26.     df = df.explode(col)  # Deal with lists of dicts
  27. df = df.reset_index(drop=True)
  28. df = deal_with_dicts(df, [&quot;branchOfficeLocations&quot;, &quot;branchOfficeLocations_1&quot;])
  29. df = deal_with_duplicated_column_names(df)
  31. # ROUND 3
  32. for col in df.columns:
  33.     df = df.explode(col)  # Deal with lists of dicts
  34. df = df.reset_index(drop=True)

Which gives you all the data from person1 dictionary as a flattened dataframe:

  1. print(df.info())
  2. # Output
  4. [38880 rows x 88 columns]
  5. &lt;class &#39;pandas.core.frame.DataFrame&#39;&gt;
  6. RangeIndex: 38880 entries, 0 to 38879
  7. Data columns (total 88 columns):
  8.  #   Column                            Non-Null Count  Dtype
  9. ---  ------                            --------------  -----
  10.  0   individualId                      38880 non-null  int64
  11.  1   firstName                         38880 non-null  object
  12.  2   middleName                        38880 non-null  object
  13.  3   lastName                          38880 non-null  object
  14.  4   otherNames                        38880 non-null  object
  15.  5   bcScope                           38880 non-null  object
  16.  6   iaScope                           38880 non-null  object
  17.  7   daysInIndustryCalculatedDate      38880 non-null  object
  18.  8   previousEmployments               0 non-null      object
  19.  9   previousIAEmployments             0 non-null      object
  20.  10  disclosureFlag                    38880 non-null  object
  21.  11  iaDisclosureFlag                  38880 non-null  object
  22.  12  disclosures                       0 non-null      object
  23.  13  stateExamCount                    38880 non-null  int64
  24.  14  principalExamCount                38880 non-null  int64
  25.  15  productExamCount                  38880 non-null  int64
  26.  16  principalExamCategory             0 non-null      object
  27.  17  approvedSRORegistrationCount      38880 non-null  int64
  28.  18  approvedFinraRegistrationCount    38880 non-null  int64
  29.  19  approvedStateRegistrationCount    38880 non-null  int64
  30.  20  approvedIAStateRegistrationCount  38880 non-null  int64
  31.  21  hasBCComments                     38880 non-null  object
  32.  22  hasIAComments                     38880 non-null  object
  33.  23  legacyReportStatusDescription     38880 non-null  object
  34.  24  firmId                            38880 non-null  int64
  35.  25  firmName                          38880 non-null  object
  36.  26  iaOnly                            38880 non-null  object
  37.  27  registrationBeginDate             38880 non-null  object
  38.  28  firmBCScope                       38880 non-null  object
  39.  29  firmIAScope                       38880 non-null  object
  40.  30  iaSECNumber                       38880 non-null  object
  41.  31  iaSECNumberType                   38880 non-null  object
  42.  32  bdSECNumber                       38880 non-null  object
  43.  33  firmId_1                          38880 non-null  int64
  44.  34  firmName_1                        38880 non-null  object
  45.  35  iaOnly_1                          38880 non-null  object
  46.  36  registrationBeginDate_1           38880 non-null  object
  47.  37  firmBCScope_1                     38880 non-null  object
  48.  38  firmIAScope_1                     38880 non-null  object
  49.  39  iaSECNumber_1                     38880 non-null  object
  50.  40  iaSECNumberType_1                 38880 non-null  object
  51.  41  bdSECNumber_1                     38880 non-null  object
  52.  42  examCategory                      38880 non-null  object
  53.  43  examName                          38880 non-null  object
  54.  44  examTakenDate                     38880 non-null  object
  55.  45  examScope                         38880 non-null  object
  56.  46  examCategory_1                    38880 non-null  object
  57.  47  examName_1                        38880 non-null  object
  58.  48  examTakenDate_1                   38880 non-null  object
  59.  49  examScope_1                       38880 non-null  object
  60.  50  state                             38880 non-null  object
  61.  51  regScope                          38880 non-null  object
  62.  52  status                            38880 non-null  object
  63.  53  regDate                           38880 non-null  object
  64.  54  sro                               38880 non-null  object
  65.  55  status_1                          38880 non-null  object
  66.  56  locatedAtFlag                     38880 non-null  object
  67.  57  supervisedFromFlag                38880 non-null  object
  68.  58  privateResidenceFlag              38880 non-null  object
  69.  59  branchOfficeId                    38880 non-null  object
  70.  60  street1                           38880 non-null  object
  71.  61  street2                           38880 non-null  object
  72.  62  city                              38880 non-null  object
  73.  63  cityAlias                         38880 non-null  object
  74.  64  state_1                           38880 non-null  object
  75.  65  country                           38880 non-null  object
  76.  66  zipCode                           38880 non-null  object
  77.  67  latitude                          38880 non-null  object
  78.  68  longitude                         38880 non-null  object
  79.  69  geoLocation                       38880 non-null  object
  80.  70  nonRegisteredOfficeFlag           38880 non-null  object
  81.  71  elaBeginDate                      38880 non-null  object
  82.  72  locatedAtFlag_1                   38880 non-null  object
  83.  73  supervisedFromFlag_1              38880 non-null  object
  84.  74  privateResidenceFlag_1            38880 non-null  object
  85.  75  branchOfficeId_1                  38880 non-null  object
  86.  76  street1_1                         38880 non-null  object
  87.  77  street2_1                         38880 non-null  object
  88.  78  city_1                            38880 non-null  object
  89.  79  cityAlias_1                       38880 non-null  object
  90.  80  state_2                           38880 non-null  object
  91.  81  country_1                         38880 non-null  object
  92.  82  zipCode_1                         38880 non-null  object
  93.  83  latitude_1                        38880 non-null  object
  94.  84  longitude_1                       38880 non-null  object
  95.  85  geoLocation_1                     38880 non-null  object
  96.  86  nonRegisteredOfficeFlag_1         38880 non-null  object
  97.  87  elaBeginDate_1                    38880 non-null  object
  98. dtypes: int64(10), object(78)
  99. memory usage: 26.1+ MB

huangapple
  • 本文由 发表于 2023年6月2日 14:17:02
  • 转载请务必保留本文链接：https://go.coder-hub.com/76387591.html
  • dictionary
  • json-normalize
  • pandas
  • panel-data
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定