Flatten Json Output

How can I turn this data into a flat data frame?

I've tried using json_normalize and pivot, but I can't seem to get the format right.

This is my desired out put format:

SiteName|SiteId|...|CompressorMeterRefID|TankID|TankNumber...|TankID|TankNumber...|TankID|... DateandTime|...

Please advise

[{'SiteName': 'Reinschmiedt 1-4H (CRP 11)',
'SiteId': 57,
'SiteRefId': 'OK10020',
'Choke': '',
'GasMeter1': 53.25,
'GasMeter1Name': 'Check Meter',
'GasMeter1RefId': '',
'GasMeter2Name': '',
'GasMeter2RefId': '',
'GasMeter3Name': '',
'GasMeter3RefId': '',
'OilMeter1Name': '',
'OilMeter1RefId': '',
'OilMeter2Name': '',
'OilMeter2RefId': '',
'WaterMeter1': 0.0,
'WaterMeter1Name': 'Water Meter',
'WaterMeter1RefId': '',
'WaterMeter2Name': '',
'WaterMeter2RefId': '',
'FlareMeterName': '',
'FlareMeterRefId': '',
'GasLiftMeterName': '',
'GasLiftMeterRefId': '',
'CompressorMeterName': '',
'CompressorMeterRefId': '',
'TankEntries': [{'TankId': 138,
'TankNumber': 2,
'TankLevelDateTime': '2018-07-01T12:00:00.0000000Z',
'TankLevelDateTimeLocal': '2018-07-01T07:00:00.0000000Z',
'TankTopGauge': 35.99,
'TankName': 'Oil Tank 209206',
'TankRefId': 0,
'TankRefId2': '',
'TankRefId3': ''},
{'TankId': 139,
'TankNumber': 3,
'TankLevelDateTime': '2018-07-01T12:00:00.0000000Z',
'TankLevelDateTimeLocal': '2018-07-01T07:00:00.0000000Z',
'TankTopGauge': 109.5,
'TankName': 'Oil Tank 209207',
'TankRefId': 0,
'TankRefId2': '',
'TankRefId3': ''}],
'DateAndTime': '2018-07-01T12:00:00.0000000Z',
'DateAndTimeLocal': '2018-07-01T07:00:00.0000000Z',
'UserName': 'ScadaVisor',
'Notes': ''},
{'SiteName': 'Allen 1-11H (CRP 8)',
.....
.....
.....


1 Answer
1

In r you can do it like this using jsonlite package:

result<- as.data.frame(jsonlite::stream_in(textConnection(data)))


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.