将json列映射到dataframe列

Question

我有下面的代码，它接受一些JSON输入并转换为一个数据帧

但是，由于JSON没有一个一致的模式，所以它完全没有对齐。（如果一个条目中不存在字段，则会将所有内容向左移动）

我有没有办法说下面的&；明确地定义它

df.field1 = json.field1

如果我能用他们的名字来定义，我就能做得很好：）

谢谢

output = subprocess.check_output(command, shell=True)

# output of subprocess will be bytes, converting to string.
if isinstance(output, bytes):
    output = output.decode()

output = json.loads(output)
df = pd.DataFrame(output['apps']['app'])
df = df.loc[df['startedTime'] > starttime]
df.to_csv('yarn_output.csv')

示例输入JSON

{"apps":{"app":[{"id":"application_1589431105417_21534","user":"udsldr","name":"HIVE-61a4ee14-1d26-4c7b-bf0d-1cc2a990557d","queue":"udsldr","state":"FINISHED","finalStatus":"SUCCEEDED","progress":100.0,"trackingUI":"History","trackingUrl":"http://uds-far-mn4.dab.02.net:8088/proxy/application_1589431105417_21534/","diagnostics":"Session stats:submittedDAGs=0, successfulDAGs=0, failedDAGs=0, killedDAGs=0\n","clusterId":1589431105417,"applicationType":"TEZ","applicationTags":"","priority":0,"startedTime":1590294649069,"finishedTime":1590294666011,"elapsedTime":16942,"amContainerLogs":"http://uds-far-dn150.dab.02.net:8042/node/containerlogs/container_e66_1589431105417_21534_01_000001/udsldr","amHostHttpAddress":"uds-far-dn150.dab.02.net:8042","allocatedMB":-1,"allocatedVCores":-1,"runningContainers":-1,"memorySeconds":144531,"vcoreSeconds":17,"queueUsagePercentage":0.0,"clusterUsagePercentage":0.0,"preemptedResourceMB":0,"preemptedResourceVCores":0,"numNonAMContainerPreempted":0,"numAMContainerPreempted":0,"logAggregationStatus":"SUCCEEDED","unmanagedApplication":false,"amNodeLabelExpression":""},{"id":"application_1589431105417_21535","user":"nifildr","name":"HIVE-850812d7-9d22-4be8-a225-7b341f6ea980","queue":"default","state":"FINISHED","finalStatus":"SUCCEEDED","progress":100.0,"trackingUI":"History","trackingUrl":"http://uds-far-mn4.dab.02.net:8088/proxy/application_1589431105417_21535/","diagnostics":"Session stats:submittedDAGs=0, successfulDAGs=1, failedDAGs=0, killedDAGs=0\n","clusterId":1589431105417,"applicationType":"TEZ","applicationTags":"","priority":0,"startedTime":1590294664397,"finishedTime":1590294801090,"elapsedTime":136693,"amContainerLogs":"http://uds-far-dn129.dab.02.net:8042/node/containerlogs/container_e66_1589431105417_21535_01_000001/nifildr","amHostHttpAddress":"uds-far-dn129.dab.02.net:8042","allocatedMB":-1,"allocatedVCores":-1,"runningContainers":-1,"memorySeconds":18279340,"vcoreSeconds":4248,"queueUsagePercentage":0.0,"clusterUsagePercentage":0.0,"preemptedResourceMB":0,"preemptedResourceVCores":0,"numNonAMContainerPreempted":0,"numAMContainerPreempted":0,"logAggregationStatus":"TIME_OUT","unmanagedApplication":false,"amNodeLabelExpression":""},{"id":"application_1589431105417_21532","user":"udsldr","name":"HIVE-73e0c359-32a5-4334-89da-4a8ae2bb1037","queue":"udsldr","state":"FINISHED","finalStatus":"SUCCEEDED","progress":100.0,"trackingUI":"History","trackingUrl":"http://uds-far-mn4.dab.02.net:8088/proxy/application_1589431105417_21532/","diagnostics":"Session stats:submittedDAGs=0, successfulDAGs=0, failedDAGs=0, killedDAGs=0\n","clusterId":1589431105417,"applicationType":"TEZ","applicationTags":"","priority":0,"startedTime":1590294622244,"finishedTime":1590294643808,"elapsedTime":21564,"amContainerLogs":"http://uds-far-dn35.dab.02.net:8042/node/containerlogs/container_e66_1589431105417_21532_01_000001/udsldr","amHostHttpAddress":"uds-far-dn35.dab.02.net:8042","allocatedMB":-1,"allocatedVCores":-1,"runningContainers":-1,"memorySeconds":182247,"vcoreSeconds":22,"queueUsagePercentage":0.0,"clusterUsagePercentage":0.0,"preemptedResourceMB":0,"preemptedResourceVCores":0,"numNonAMContainerPreempted":0,"numAMContainerPreempted":0,"logAggregationStatus":"SUCCEEDED","unmanagedApplication":false,"amNodeLabelExpression":""},{"id":"application_1589431105417_21533","user":"udssupport","name":"tcs.uds.webstats","queue":"udssystem","state":"FINISHED","finalStatus":"SUCCEEDED","progress":100.0,"trackingUI":"History","trackingUrl":"http://uds-far-mn4.dab.02.net:8088/proxy/application_1589431105417_21533/","diagnostics":"","clusterId":1589431105417,"applicationType":"SPARK","applicationTags":"","priority":0,"startedTime":1590294631138,"finishedTime":1590295670552,"elapsedTime":1039414,"amContainerLogs":"http://uds-far-dn148.dab.02.net:8042/node/containerlogs/container_e66_1589431105417_21533_01_000001/udssupport","amHostHttpAddress":"uds-far-dn148.dab.02.net:8042","allocatedMB":-1,"allocatedVCores":-1,"runningContainers":-1,"memorySeconds":4762538052,"vcoreSeconds":775756,"queueUsagePercentage":0.0,"clusterUsagePercentage":0.0,"preemptedResourceMB":0,"preemptedResourceVCores":0,"numNonAMContainerPreempted":0,"numAMContainerPreempted":0,"logAggregationStatus":"TIME_OUT","unmanagedApplication":false,"amNodeLabelExpression":""},{"id":"application_1589431105417_21530","user":"nifildr","name":"HIVE-e9a64e12-11f0-4ba8-b069-3be0ce561137","queue":"default","state":"FINISHED","finalStatus":"SUCCEEDED","progress":100.0,"trackingUI":"History","trackingUrl":"http://uds-far-mn4.dab.02.net:8088/proxy/application_1589431105417_21530/","diagnostics":"Session stats:submittedDAGs=0, successfulDAGs=3, failedDAGs=0, killedDAGs=0\n","clusterId":1589431105417,"applicationType":"TEZ","applicationTags":"","priority":0,"startedTime":1590294606965,"finishedTime":1590295033193,"elapsedTime":426228,"amContainerLogs":"http://uds-far-dn75.dab.02.net:8042/node/containerlogs/container_e66_1589431105417_21530_01_000001/nifildr","amHostHttpAddress":"uds-far-dn75.dab.02.net:8042","allocatedMB":-1,"allocatedVCores":-1,"runningContainers":-1,"memorySeconds":114397555,"vcoreSeconds":27175,"queueUsagePercentage":0.0,"clusterUsagePercentage":0.0,"preemptedResourceMB":0,"preemptedResourceVCores":0,"numNonAMContainerPreempted":0,"numAMContainerPreempted":0,"logAggregationStatus":"TIME_OUT","unmanagedApplication":false,"amNodeLabelExpression":""},{"id":"application_1589431105417_21531","user":"nifi","name":"HIVE-a063ddd1-5bf8-47b4-8ce3-8497c93b79a5","queue":"default","state":"FINISHED","finalStatus":"SUCCEEDED","progress":100.0,"trackingUI":"History","trackingUrl":"http://uds-far-mn4.dab.02.net:8088/proxy/application_1589431105417_21531/","diagnostics":"Session stats:submittedDAGs=0, successfulDAGs=0, failedDAGs=0, killedDAGs=0\n","clusterId":1589431105417,"applicationType":"TEZ","applicationTags":"","priority":0,"startedTime":1590294613578,"finishedTime":1590294655173,"elapsedTime":41595,"amContainerLogs":"http://uds-far-dn56.dab.02.net:8042/node/containerlogs/container_e66_1589431105417_21531_01_000001/nifi","amHostHttpAddress":"uds-far-dn56.dab.02.net:8042","allocatedMB":-1,"allocatedVCores":-1,"runningContainers":-1,"memorySeconds":345792,"vcoreSeconds":42,"queueUsagePercentage":0.0,"clusterUsagePercentage":0.0,"preemptedResourceMB":0,"preemptedResourceVCores":0,"numNonAMContainerPreempted":0,"numAMContainerPreempted":0,"logAggregationStatus":"SUCCEEDED","unmanagedApplication":false,"amNodeLabelExpression":""},{"id":"application_1589431105417_21528","user":"udsldr","name":"com.cardinality.LocationDB","queue":"udsldr","state":"FINISHED","finalStatus":"SUCCEEDED","progress":100.0,"trackingUI":"History","trackingUrl":"http://uds-far-mn4.dab.02.net:8088/proxy/application_1589431105417_21528/","diagnostics":"","clusterId":1589431105417,"applicationType":"SPARK","applicationTags":"5ec9f8480000f1697e683969","priority":0,"startedTime":1590294605875,"finishedTime":1590294782281,"elapsedTime":176406,"amContainerLogs":"http://uds-far-dn167.dab.02.net:8042/node/containerlogs/container_e66_1589431105417_21528_01_000001/udsldr","amHostHttpAddress":"uds-far-dn167.dab.02.net:8042","allocatedMB":-1,"allocatedVCores":-1,"runningContainers":-1,"memorySeconds":43389139,"vcoreSeconds":5239,"queueUsagePercentage":0.0,"clusterUsagePercentage":0.0,"preemptedResourceMB":0,"preemptedResourceVCores":0,"numNonAMContainerPreempted":0,"numAMContainerPreempted":0,"logAggregationStatus":"TIME_OUT","unmanagedApplication":false,"amNodeLabelExpression":""},{"id":"application_1589431105417_21529","user":"keenek1","name":"Clean DPI Report","queue":"default","state":"FINISHED","finalStatus":"SUCCEEDED","progress":100.0,"trackingUI":"History","trackingUrl":"http://uds-far-mn4.dab.02.net:8088/proxy/application_1589431105417_21529/","diagnostics":"","clusterId":1589431105417,"applicationType":"SPARK","applicationTags":"","priority":0,"startedTime":1590294607111,"finishedTime":1590295032105,"elapsedTime":424994,"amContainerLogs":"http://uds-far-dn62.dab.02.net:8042/node/containerlogs/container_e66_1589431105417_21529_01_000001/keenek1","amHostHttpAddress":"uds-far-dn62.dab.02.net:8042","allocatedMB":-1,"allocatedVCores":-1,"runningContainers":-1,"memorySeconds":2114077299,"vcoreSeconds":344079,"queueUsagePercentage":0.0,"clusterUsagePercentage":0.0,"preemptedResourceMB":0,"preemptedResourceVCores":0,"numNonAMContainerPreempted":0,"numAMContainerPreempted":0,"logAggregationStatus":"TIME_OUT","unmanagedApplication":false,"amNodeLabelExpression":""},{"id":"application_1589431105417_21542","user":"murugaa1","name":"HIVE-a1a5aadb-254c-4289-ad22-e9c7ce5e9814","queue":"default","state":"FINISHED","finalStatus":"SUCCEEDED","progress":100.0,"trackingUI":"History","trackingUrl":"http://uds-far-mn4.dab.02.net:8088/proxy/application_1589431105417_21542/","diagnostics":"Session stats:submittedDAGs=0, successfulDAGs=1, failedDAGs=0, killedDAGs=0\n","clusterId":1589431105417,"applicationType":"TEZ","applicationTags":"","priority":0,"startedTime":1590295275713,"finishedTime":1590295297948,"elapsedTime":22235,"amContainerLogs":"http://uds-far-dn46.dab.02.net:8042/node/containerlogs/container_e66_1589431105417_21542_01_000001/murugaa1","amHostHttpAddress":"uds-far-dn46.dab.02.net:8042","allocatedMB":-1,"allocatedVCores":-1,"runningContainers":-1,"memorySeconds":999465,"vcoreSeconds":217,"queueUsagePercentage":0.0,"clusterUsagePercentage":0.0,"preemptedResourceMB":0,"preemptedResourceVCores":0,"numNonAMContainerPreempted":0,"numAMContainerPreempted":0,"logAggregationStatus":"SUCCEEDED","unmanagedApplication":false,"amNodeLabelExpression":""},{"id":"application_1589431105417_21543","user":"murugaa1","name":"HIVE-cdc8a5da-f880-4f8e-9baf-b306095b9efb","queue":"default","state":"FINISHED","finalStatus":"SUCCEEDED","progress":100.0,"trackingUI":"History","trackingUrl":"http://uds-far-mn4.dab.02.net:8088/proxy/application_1589431105417_21543/","diagnostics":"Session stats:submittedDAGs=0, successfulDAGs=1, failedDAGs=0, killedDAGs=0\n","clusterId":1589431105417,"applicationType":"TEZ","applicationTags":"","priority":0,"startedTime":1590295277611,"finishedTime":1590295301515,"elapsedTime":23904,"amContainerLogs":"http://uds-far-dn41.dab.02.net:8042/node/containerlogs/container_e66_1589431105417_21543_01_000001/murugaa1","amHostHttpAddress":"uds-far-dn41.dab.02.net:8042","allocatedMB":-1,"allocatedVCores":-1,"runningContainers":-1,"memorySeconds":1077860,"vcoreSeconds":228,"queueUsagePercentage":0.0,"clusterUsagePercentage":0.0,"preemptedResourceMB":0,"preemptedResourceVCores":0,"numNonAMContainerPreempted":0,"numAMContainerPreempted":0,"logAggregationStatus":"SUCCEEDED","unmanagedApplication":false,"amNodeLabelExpression":""}]}}

CSV输出：

allocatedMB|applicationType|diagnostics|finalStatus|finishedTime|memorySeconds|queue|startedTime|user|vcoreSeconds
-1|TEZ|"Session stats:submittedDAGs=0, successfulDAGs=0, failedDAGs=0, killedDAGs=0
"|SUCCEEDED|1590294666011|144531|udsldr|1590294649069|udsldr|17
-1|TEZ|"Session stats:submittedDAGs=0, successfulDAGs=1, failedDAGs=0, killedDAGs=0
"|SUCCEEDED|1590294801090|18279340|default|1590294664397|nifildr|4248
-1|TEZ|"Session stats:submittedDAGs=0, successfulDAGs=0, failedDAGs=0, killedDAGs=0

Answer 1

在转换为csv时，请尝试使用不同的分隔符，我认为出现不对齐是因为逗号：

df.to_csv('123.csv', sep='|', index=['id'])