I have weather observation data received in JSON which I'd like to flatten.
Rep in 'Period'{'SiteRep': {'DV': {'type': 'Obs',
'Location': [{'i': '3002',
'lat': '60.749',
'lon': '-0.854',
'name': 'BALTASOUND',
'Period': [{'Rep': {'$': '1380',
'D': 'SW',
'G': '34',
'H': '79.5',
'P': '1019',
'S': '25',
'T': '7.9',
'V': '13000',
'W': '8',
'Dp': '4.6',
'Pt': 'F'},
'type': 'Day',
'value': '2019-12-31Z'},
{'Rep': [{'$': '0',
'D': 'SW',
'G': '32',
'H': '84.0',
'P': '1018',
'S': '21',
'T': '7.5',
'V': '13000',
'W': '8',
'Dp': '5.0',
'Pt': 'F'},
{'$': '60',
'D': 'SW',
'G': '34',
'H': '81.7',
'P': '1018',
'S': '22',
'T': '7.5',
'V': '12000',
'W': '8',
'Dp': '4.6',
'Pt': 'F'},
{'$': '120',
'D': 'SW',
'G': '36',
'H': '79.9',
'P': '1017',
'S': '24',
'T': '7.9',
'V': '11000',
'W': '8',
'Dp': '4.7',
'Pt': 'F'},
{'$': '180',
'D': 'SW',
'G': '40',
'H': '82.3',
'P': '1016',
'S': '23',
'T': '7.5',
'V': '13000',
'W': '8',
'Dp': '4.7',
'Pt': 'F'},
{'$': '240',
'D': 'SW',
'G': '33',
'H': '84.6',
'P': '1015',
'S': '18',
'T': '8.0',
'V': '12000',
'W': '8',
'Dp': '5.6',
'Pt': 'F'},
{'$': '300',
'D': 'SW',
'G': '33',
'H': '85.3',
'P': '1015',
'S': '24',
'T': '8.3',
'V': '11000',
'W': '8',
'Dp': '6.0',
'Pt': 'F'},
{'$': '360',
'D': 'WSW',
'G': '41',
'H': '89.0',
'P': '1014',
'S': '30',
'T': '8.5',
'V': '8000',
'W': '8',
'Dp': '6.8',
'Pt': 'F'},
{'$': '420',
'D': 'SW',
'G': '43',
'H': '89.6',
'P': '1013',
'S': '28',
'T': '8.7',
'V': '7000',
'W': '7',
'Dp': '7.1',
'Pt': 'F'},
{'$': '480',
'D': 'SW',
'G': '39',
'H': '88.4',
'P': '1013',
'S': '23',
'T': '8.7',
'V': '15000',
'W': '7',
'Dp': '6.9',
'Pt': 'F'},
{'$': '540',
'D': 'SW',
'G': '40',
'H': '84.3',
'P': '1013',
'S': '29',
'T': '9.1',
'V': '19000',
'W': '8',
'Dp': '6.6',
'Pt': 'F'},
{'$': '600',
'D': 'SW',
'G': '41',
'H': '85.4',
'P': '1012',
'S': '24',
'T': '8.9',
'V': '12000',
'W': '8',
'Dp': '6.6',
'Pt': 'F'},
{'$': '660',
'D': 'SW',
'G': '38',
'H': '84.2',
'P': '1012',
'S': '28',
'T': '9.2',
'V': '13000',
'W': '8',
'Dp': '6.7',
'Pt': 'F'},
{'$': '720',
'D': 'SW',
'G': '47',
'H': '83.6',
'P': '1011',
'S': '32',
'T': '9.4',
'V': '12000',
'W': '8',
'Dp': '6.8',
'Pt': 'F'},
{'$': '780',
'D': 'WSW',
'G': '45',
'H': '84.8',
'P': '1011',
'S': '30',
'T': '9.4',
'V': '11000',
'W': '8',
'Dp': '7.0',
'Pt': 'F'},
{'$': '840',
'D': 'SW',
'G': '43',
'H': '86.0',
'P': '1010',
'S': '28',
'T': '9.4',
'V': '11000',
'W': '7',
'Dp': '7.2',
'Pt': 'F'},
{'$': '900',
'D': 'WSW',
'G': '40',
'H': '85.4',
'P': '1009',
'S': '29',
'T': '9.4',
'V': '12000',
'W': '8',
'Dp': '7.1',
'Pt': 'F'},
{'$': '960',
'D': 'SW',
'G': '39',
'H': '86.0',
'P': '1009',
'S': '25',
'T': '9.2',
'V': '11000',
'W': '8',
'Dp': '7.0',
'Pt': 'F'},
{'$': '1020',
'D': 'SW',
'G': '33',
'H': '87.8',
'P': '1009',
'S': '23',
'T': '8.9',
'V': '11000',
'W': '8',
'Dp': '7.0',
'Pt': 'F'},
{'$': '1080',
'D': 'SW',
'G': '36',
'H': '85.5',
'P': '1008',
'S': '23',
'T': '8.9',
'V': '11000',
'W': '8',
'Dp': '6.6',
'Pt': 'F'},
{'$': '1140',
'D': 'SW',
'G': '40',
'H': '86.6',
'P': '1007',
'S': '28',
'T': '8.8',
'V': '14000',
'W': '8',
'Dp': '6.7',
'Pt': 'F'},
{'$': '1200',
'D': 'SSW',
'G': '39',
'H': '84.8',
'P': '1006',
'S': '28',
'T': '8.8',
'V': '13000',
'W': '8',
'Dp': '6.4',
'Pt': 'F'},
{'$': '1260',
'D': 'SSW',
'G': '37',
'H': '87.7',
'P': '1005',
'S': '26',
'T': '8.0',
'V': '15000',
'W': '8',
'Dp': '6.1',
'Pt': 'F'},
{'$': '1320',
'D': 'S',
'G': '37',
'H': '88.4',
'P': '1003',
'S': '24',
'T': '8.0',
'V': '13000',
'W': '8',
'Dp': '6.2',
'Pt': 'F'},
{'$': '1380',
'D': 'S',
'G': '38',
'H': '89.6',
'P': '1002',
'S': '29',
'T': '7.6',
'V': '11000',
'W': '8',
'Dp': '6.0',
'Pt': 'F'}],
'type': 'Day',
'value': '2020-01-01Z'}]}]}}}
The structure of JSON looks like this where each period has two reports:
SiteRep - DV - Location - Period (0) - Rep (0)
- Rep(1)
Period (1) - Rep (0)
- Rep(1)
The desired output would be the table where Location, Period and Report values are flattened.
| i | lat | lon | name |country | continent| elevation| name |Rep(0)$| Rep(0)D|Rep(0)G|..
|---|-----|------|-------|--------|----------|----------|------|-------|--------|-------|..
| | | | | | | | | | | |
I've managed to get Location flattened
normalised_data = pd.json_normalize(df['observations'], record_path=['SiteRep','DV','Location'])
so now my data looks like
i lat lon name Period country continent elevation
0 3002 60.749 -0.854 BALTASOUND [{'Rep': {'$': '1380', 'D': 'SW', 'G': '34', 'H': '79.5', 'P': '1019', 'S': '25', 'T': '7.9', 'V': '13000', 'W': '8', 'Dp': '4.6', 'Pt': 'F'}, 'type': 'Day', 'value': '2019-12-31Z'}, {'Rep': [{'$': '0', 'D': 'SW', 'G': '32', 'H': '84.0', 'P': '1018', 'S': '21', 'T': '7.5', 'V': '13000', 'W': '8', 'Dp': '5.0', 'Pt': 'F'}, {'$': '60', 'D': 'SW', 'G': '34', 'H': '81.7', 'P': '1018', 'S': '22', 'T': '7.5', 'V': '12000', 'W': '8', 'Dp': '4.6', 'Pt': 'F'}, {'$': '120', 'D': 'SW', 'G': '36', 'H': '79.9', 'P': '1017', 'S': '24', 'T': '7.9', 'V': '11000', 'W': '8', 'Dp': '4.7', 'Pt': 'F'}, {'$': '180', 'D': 'SW', 'G': '40', 'H': '82.3', 'P': '1016', 'S': '23', 'T': '7.5', 'V': '13000', 'W': '8', 'Dp': '4.7', 'Pt': 'F'}, {'$': '240', 'D': 'SW', 'G': '33', 'H': '84.6', 'P': '1015', 'S': '18', 'T': '8.0', 'V': '12000', 'W': '8', 'Dp': '5.6', 'Pt': 'F'}, {'$': '300', 'D': 'SW', 'G': '33', 'H': '85.3', 'P': '1015', 'S': '24', 'T': '8.3', 'V': '11000', 'W': '8', 'Dp': '6.0', 'Pt': 'F'}, {'$': '360', 'D': 'WSW', 'G': '41', 'H': '89.0', 'P': '1014', 'S': '30', 'T': '8.5', 'V': '8000', 'W': '8', 'Dp': '6.8', 'Pt': 'F'}, {'$': '420', 'D': 'SW', 'G': '43', 'H': '89.6', 'P': '1013', 'S': '28', 'T': '8.7', 'V': '7000', 'W': '7', 'Dp': '7.1', 'Pt': 'F'}, {'$': '480', 'D': 'SW', 'G': '39', 'H': '88.4', 'P': '1013', 'S': '23', 'T': '8.7', 'V': '15000', 'W': '7', 'Dp': '6.9', 'Pt': 'F'}, {'$': '540', 'D': 'SW', 'G': '40', 'H': '84.3', 'P': '1013', 'S': '29', 'T': '9.1', 'V': '19000', 'W': '8', 'Dp': '6.6', 'Pt': 'F'}, {'$': '600', 'D': 'SW', 'G': '41', 'H': '85.4', 'P': '1012', 'S': '24', 'T': '8.9', 'V': '12000', 'W': '8', 'Dp': '6.6', 'Pt': 'F'}, {'$': '660', 'D': 'SW', 'G': '38', 'H': '84.2', 'P': '1012', 'S': '28', 'T': '9.2', 'V': '13000', 'W': '8', 'Dp': '6.7', 'Pt': 'F'}, {'$': '720', 'D': 'SW', 'G': '47', 'H': '83.6', 'P': '1011', 'S': '32', 'T': '9.4', 'V': '12000', 'W': '8', 'Dp': '6.8', 'Pt': 'F'}, {'$': '780', 'D': 'WSW', 'G': '45', 'H': '84.8', 'P': '1011', 'S': '30', 'T': '9.4', 'V': '11000', 'W': '8', 'Dp': '7.0', 'Pt': 'F'}, {'$': '840', 'D': 'SW', 'G': '43', 'H': '86.0', 'P': '1010', 'S': '28', 'T': '9.4', 'V': '11000', 'W': '7', 'Dp': '7.2', 'Pt': 'F'}, {'$': '900', 'D': 'WSW', 'G': '40', 'H': '85.4', 'P': '1009', 'S': '29', 'T': '9.4', 'V': '12000', 'W': '8', 'Dp': '7.1', 'Pt': 'F'}, {'$': '960', 'D': 'SW', 'G': '39', 'H': '86.0', 'P': '1009', 'S': '25', 'T': '9.2', 'V': '11000', 'W': '8', 'Dp': '7.0', 'Pt': 'F'}, {'$': '1020', 'D': 'SW', 'G': '33', 'H': '87.8', 'P': '1009', 'S': '23', 'T': '8.9', 'V': '11000', 'W': '8', 'Dp': '7.0', 'Pt': 'F'}, {'$': '1080', 'D': 'SW', 'G': '36', 'H': '85.5', 'P': '1008', 'S': '23', 'T': '8.9', 'V': '11000', 'W': '8', 'Dp': '6.6', 'Pt': 'F'}, {'$': '1140', 'D': 'SW', 'G': '40', 'H': '86.6', 'P': '1007', 'S': '28', 'T': '8.8', 'V': '14000', 'W': '8', 'Dp': '6.7', 'Pt': 'F'}, {'$': '1200', 'D': 'SSW', 'G': '39', 'H': '84.8', 'P': '1006', 'S': '28', 'T': '8.8', 'V': '13000', 'W': '8', 'Dp': '6.4', 'Pt': 'F'}, {'$': '1260', 'D': 'SSW', 'G': '37', 'H': '87.7', 'P': '1005', 'S': '26', 'T': '8.0', 'V': '15000', 'W': '8', 'Dp': '6.1', 'Pt': 'F'}, {'$': '1320', 'D': 'S', 'G': '37', 'H': '88.4', 'P': '1003', 'S': '24', 'T': '8.0', 'V': '13000', 'W': '8', 'Dp': '6.2', 'Pt': 'F'}, {'$': '1380', 'D': 'S', 'G': '38', 'H': '89.6', 'P': '1002', 'S': '29', 'T': '7.6', 'V': '11000', 'W': '8', 'Dp': '6.0', 'Pt': 'F'}], 'type': 'Day', 'value': '2020-01-01Z'}] SCOTLAND EUROPE 15.0
1 3005 60.139 -1.183 LERWICK (S. SCREEN) [{'Rep': {'$': '1380', 'D': 'W', 'G': '41', 'H': '89.5', 'P': '1020', 'S': '28', 'T': '7.2', 'V': '15000', 'W': '8', 'Dp': '5.6', 'Pt': 'F'}, 'type': 'Day', 'value': '2019-12-31Z'}, {'Rep': [{'$': '0', 'D': 'WSW', 'G': '44', 'H': '88.1', 'P': '1019', 'S': '33', 'T': '6.9', 'V': '15000', 'W': '7', 'Dp': '5.1', 'Pt': 'F'}, {'$': '60', 'D': 'WSW', 'G': '47', 'H': '90.2', 'P': '1018', 'S': '36', 'T': '6.9', 'V': '15000', 'W': '7', 'Dp': '5.4', 'Pt': 'F'}, {'$': '120', 'D': 'WSW', 'G': '52', 'H': '88.8', 'P': '1018', 'S': '32', 'T': '6.9', 'V': '17000', 'W': '8', 'Dp': '5.2', 'Pt': 'F'}, {'$': '180', 'D': 'WSW', 'G': '47', 'H': '89.4', 'P': '1017', 'S': '34', 'T': '7.4', 'V': '12000', 'W': '8', 'Dp': '5.8', 'Pt': 'F'}, {'$': '240', 'D': 'WSW', 'G': '51', 'H': '89.4', 'P': '1016', 'S': '38', 'T': '7.4', 'V': '14000', 'W': '8', 'Dp': '5.8', 'Pt': 'F'}, {'$': '300', 'D': 'WSW', 'G': '48', 'H': '90.8', 'P': '1015', 'S': '33', 'T': '7.7', 'V': '13000', 'W': '8', 'Dp': '6.3', 'Pt': 'F'}, {'$': '360', 'D': 'WSW', 'G': '49', 'H': '92.0', 'P': '1015', 'S': '34', 'T': '7.9', 'V': '10000', 'W': '8', 'Dp': '6.7', 'Pt': 'F'}, {'$': '420', 'D': 'WSW', 'G': '47', 'H': '92.1', 'P': '1014', 'S': '38', 'T': '8.0', 'V': '8000', 'W': '8', 'Dp': '6.8', 'Pt': 'F'}, {'$': '480', 'D': 'WSW', 'G': '48', 'H': '94.0', 'P': '1014', 'S': '34', 'T': '7.9', 'V': '10000', 'W': '11', 'Dp': '7.0', 'Pt': 'F'}, {'$': '540', 'D': 'WSW', 'G': '55', 'H': '90.2', 'P': '1014', 'S': '40', 'T': '8.1', 'V': '12000', 'W': '7', 'Dp': '6.6', 'Pt': 'F'}, {'$': '600', 'D': 'WSW', 'G': '52', 'H': '88.9', 'P': '1013', 'S': '39', 'T': '8.3', 'V': '15000', 'W': '7', 'Dp': '6.6', 'Pt': 'F'}, {'$': '660', 'D': 'WSW', 'G': '54', 'H': '90.1', 'P': '1013', 'S': '39', 'T': '8.3', 'V': '12000', 'W': '7', 'Dp': '6.8', 'Pt': 'F'}, {'$': '720', 'D': 'WSW', 'G': '53', 'H': '90.9', 'P': '1012', 'S': '38', 'T': '8.5', 'V': '15000', 'W': '7', 'Dp': '7.1', 'Pt': 'F'}, {'$': '780', 'D': 'WSW', 'G': '53', 'H': '91.5', 'P': '1011', 'S': '39', 'T': '8.5', 'V': '12000', 'W': '7', 'Dp': '7.2', 'Pt': 'F'}, {'$': '840', 'D': 'WSW', 'G': '49', 'H': '92.7', 'P': '1011', 'S': '37', 'T': '8.3', 'V': '12000', 'W': '7', 'Dp': '7.2', 'Pt': 'F'}, {'$': '900', 'D': 'WSW', 'G': '51', 'H': '89.6', 'P': '1010', 'S': '34', 'T': '8.3', 'V': '12000', 'W': '7', 'Dp': '6.7', 'Pt': 'F'}, {'$': '960', 'D': 'WSW', 'G': '46', 'H': '88.9', 'P': '1010', 'S': '34', 'T': '8.3', 'V': '15000', 'W': '7', 'Dp': '6.6', 'Pt': 'F'}, {'$': '1020', 'D': 'WSW', 'G': '46', 'H': '86.5', 'P': '1009', 'S': '34', 'T': '8.4', 'V': '18000', 'W': '7', 'Dp': '6.3', 'Pt': 'F'}, {'$': '1080', 'D': 'WSW', 'G': '46', 'H': '84.8', 'P': '1009', 'S': '36', 'T': '8.5', 'V': '18000', 'W': '7', 'Dp': '6.1', 'Pt': 'F'}, {'$': '1140', 'D': 'SSW', 'G': '43', 'H': '88.3', 'P': '1009', 'S': '28', 'T': '7.8', 'V': '18000', 'W': '7', 'Dp': '6.0', 'Pt': 'F'}, {'$': '1200', 'D': 'SSW', 'G': '36', 'H': '88.9', 'P': '1008', 'S': '25', 'T': '7.5', 'V': '20000', 'W': '8', 'Dp': '5.8', 'Pt': 'F'}, {'$': '1260', 'D': 'SSW', 'G': '36', 'H': '88.9', 'P': '1006', 'S': '25', 'T': '7.5', 'V': '15000', 'W': '8', 'Dp': '5.8', 'Pt': 'F'}, {'$': '1320', 'D': 'SSW', 'G': '36', 'H': '89.6', 'P': '1005', 'S': '24', 'T': '7.1', 'V': '13000', 'W': '8', 'Dp': '5.5', 'Pt': 'F'}, {'$': '1380', 'D': 'SSW', 'G': '38', 'H': '86.4', 'P': '1003', 'S': '28', 'T': '7.2', 'V': '18000', 'W': '8', 'Dp': '5.1', 'Pt': 'F'}], 'type': 'Day', 'value': '2020-01-01Z'}] SCOTLAND EUROPE 82.0
2 3008 59.527 -1.628 FAIR ISLE [{'Rep': {'$': '1380', 'D': 'SW', 'G': '31', 'H': '83.8', 'P': '1022', 'S': '24', 'T': '6.4', 'V': '17000', 'W': '7', 'Dp': '3.9', 'Pt': 'F'}, 'type': 'Day', 'value': '2019-12-31Z'}, {'Rep': [{'$': '0', 'D': 'SW', 'G': '30', 'H': '88.1', 'P': '1022', 'S': '16', 'T': '6.0', 'V': '11000', 'W': '0', 'Dp': '4.2', 'Pt': 'F'}, {'$': '60', 'D': 'SW', 'H': '82.1', 'P': '1021', 'S': '18', 'T': '6.5', 'V': '15000', 'W': '0', 'Dp': '3.7', 'Pt': 'F'}, {'$': '120', 'D': 'WSW', 'G': '33', 'H': '74.3', 'P': '1020', 'S': '18', 'T': '6.6', 'V': '24000', 'W': '0', 'Dp': '2.4', 'Pt': 'F'}, {'$': '180', 'D': 'WSW', 'G': '30', 'H': '79.2', 'P': '1019', 'S': '23', 'T': '6.6', 'V': '20000', 'W': '0', 'Dp': '3.3', 'Pt': 'F'}, {'$': '240', 'D': 'SW', 'G': '31', 'H': '82.6', 'P': '1018', 'S': '21', 'T': '6.5', 'V': '17000', 'W': '2', 'Dp': '3.8', 'Pt': 'F'}, {'$': '300', 'D': 'SW', 'H': '81.5', 'P': '1018', 'S': '17', 'T': '6.5', 'V': '18000', 'W': '0', 'Dp': '3.6', 'Pt': 'F'}, {'$': '360', 'D': 'SW', 'H': '80.9', 'P': '1018', 'S': '16', 'T': '6.6', 'V': '15000', 'W': '0', 'Dp': '3.6', 'Pt': 'F'}, {'$': '420', 'D': 'SW', 'H': '78.7', 'P': '1017', 'S': '17', 'T': '7.2', 'V': '14000', 'W': '7', 'Dp': '3.8', 'Pt': 'F'}, {'$': '480', 'D': 'SW', 'H': '84.0', 'P': '1017', 'S': '18', 'T': '7.6', 'V': '18000', 'W': '8', 'Dp': '5.1', 'Pt': 'F'}, {'$': '540', 'D': 'WSW', 'G': '39', 'H': '84.1', 'P': '1016', 'S': '26', 'T': '8.2', 'V': '17000', 'W': '7', 'Dp': '5.7', 'Pt': 'F'}, {'$': '600', 'D': 'SW', 'G': '34', 'H': '78.8', 'P': '1016', 'S': '24', 'T': '8.0', 'V': '16000', 'W': '7', 'Dp': '4.6', 'Pt': 'F'}, {'$': '660', 'D': 'SW', 'G': '29', 'H': '82.3', 'P': '1016', 'S': '21', 'T': '8.1', 'V': '15000', 'W': '7', 'Dp': '5.3', 'Pt': 'F'}, {'$': '720', 'D': 'SSW', 'G': '30', 'H': '84.7', 'P': '1015', 'S': '18', 'T': '8.2', 'V': '10000', 'W': '7', 'Dp': '5.8', 'Pt': 'F'}, {'$': '780', 'D': 'SW', 'G': '30', 'H': '85.3', 'P': '1014', 'S': '23', 'T': '8.1', 'V': '12000', 'W': '7', 'Dp': '5.8', 'Pt': 'F'}, {'$': '840', 'D': 'SW', 'G': '32', 'H': '86.5', 'P': '1013', 'S': '23', 'T': '7.9', 'V': '9000', 'W': '7', 'Dp': '5.8', 'Pt': 'F'}, {'$': '900', 'D': 'SW', 'G': '33', 'H': '87.0', 'P': '1013', 'S': '22', 'T': '8.0', 'V': '12000', 'W': '7', 'Dp': '6.0', 'Pt': 'F'}, {'$': '960', 'D': 'SW', 'G': '31', 'H': '87.7', 'P': '1012', 'S': '22', 'T': '7.9', 'V': '14000', 'W': '7', 'Dp': '6.0', 'Pt': 'F'}, {'$': '1020', 'D': 'SSW', 'G': '31', 'H': '86.5', 'P': '1012', 'S': '22', 'T': '7.9', 'V': '11000', 'W': '7', 'Dp': '5.8', 'Pt': 'F'}, {'$': '1080', 'D': 'SSW', 'G': '32', 'H': '89.0', 'P': '1011', 'S': '21', 'T': '7.7', 'V': '10000', 'W': '7', 'Dp': '6.0', 'Pt': 'F'}, {'$': '1140', 'D': 'SSW', 'G': '33', 'H': '88.9', 'P': '1010', 'S': '25', 'T': '7.8', 'V': '11000', 'W': '7', 'Dp': '6.1', 'Pt': 'F'}, {'$': '1200', 'D': 'S', 'G': '36', 'H': '88.3', 'P': '1009', 'S': '26', 'T': '7.5', 'V': '15000', 'W': '8', 'Dp': '5.7', 'Pt': 'F'}, {'$': '1260', 'D': 'S', 'G': '43', 'H': '83.5', 'P': '1007', 'S': '33', 'T': '7.5', 'V': '15000', 'W': '8', 'Dp': '4.9', 'Pt': 'F'}, {'$': '1320', 'D': 'S', 'G': '43', 'H': '80.0', 'P': '1006', 'S': '31', 'T': '7.6', 'V': '15000', 'W': '7', 'Dp': '4.4', 'Pt': 'F'}, {'$': '1380', 'D': 'S', 'G': '45', 'H': '81.3', 'P': '1005', 'S': '30', 'T': '7.5', 'V': '17000', 'W': '8', 'Dp': '4.5', 'Pt': 'F'}], 'type': 'Day', 'value': '2020-01-01Z'}] SCOTLAND EUROPE 57.0
3 3017 58.954 -2.9 KIRKWALL [{'Rep': {'$': '1380', 'D': 'SW', 'H': '85.9', 'P': '1022', 'S': '21', 'T': '3.7', 'V': '35000', 'W': '0', 'Dp': '1.6', 'Pt': 'F'}, 'type': 'Day', 'value': '2019-12-31Z'}, {'Rep': [{'$': '0', 'D': 'SW', 'H': '84.0', 'P': '1022', 'S': '13', 'T': '3.9', 'V': '35000', 'W': '0', 'Dp': '1.5', 'Pt': 'F'}, {'$': '60', 'D': 'SW', 'H': '78.6', 'P': '1021', 'S': '11', 'T': '3.6', 'V': '50000', 'W': '0', 'Dp': '0.3', 'Pt': 'F'}, {'$': '120', 'D': 'SSW', 'H': '79.4', 'P': '1020', 'S': '15', 'T': '3.7', 'V': '55000', 'W': '0', 'Dp': '0.5', 'Pt': 'F'}, {'$': '180', 'D': 'SSW', 'H': '80.1', 'P': '1020', 'S': '9', 'T': '4.0', 'V': '45000', 'W': '0', 'Dp': '0.9', 'Pt': 'F'}, {'$': '240', 'D': 'S', 'H': '83.9', 'P': '1018', 'S': '10', 'T': '2.6', 'V': '35000', 'W': '0', 'Dp': '0.2', 'Pt': 'F'}, {'$': '300', 'D': 'W', 'H': '81.0', 'P': '1018', 'S': '2', 'T': '2.5', 'V': '45000', 'W': '0', 'Dp': '-0.4', 'Pt': 'F'}, {'$': '360', 'D': 'SSW', 'H': '75.3', 'P': '1018', 'S': '10', 'T': '3.8', 'V': '55000', 'W': '0', 'Dp': '-0.1', 'Pt': 'F'}, {'$': '420', 'D': 'SSW', 'H': '80.5', 'P': '1017', 'S': '11', 'T': '3.7', 'V': '50000', 'W': '0', 'Dp': '0.7', 'Pt': 'F'}, {'$': '480', 'D': 'SSW', 'H': '76.7', 'P': '1017', 'S': '16', 'T': '5.2', 'V': '50000', 'W': '0', 'Dp': '1.5', 'Pt': 'F'}, {'$': '540', 'D': 'SSW', 'H': '83.7', 'P': '1017', 'S': '14', 'T': '5.6', 'V': '30000', 'W': '2', 'Dp': '3.1', 'Pt': 'F'}, {'$': '600', 'D': 'SW', 'H': '85.7', 'P': '1016', 'S': '16', 'T': '5.5', 'V': '29000', 'W': '3', 'Dp': '3.3', 'Pt': 'F'}, {'$': '660', 'D': 'SW', 'H': '79.5', 'P': '1016', 'S': '14', 'T': '7.9', 'V': '35000', 'W': '8', 'Dp': '4.6', 'Pt': 'F'}, {'$': '720', 'D': 'SSW', 'H': '80.0', 'P': '1016', 'S': '16', 'T': '7.8', 'V': '30000', 'W': '7', 'Dp': '4.6', 'Pt': 'F'}, {'$': '780', 'D': 'SW', 'H': '83.4', 'P': '1015', 'S': '18', 'T': '7.6', 'V': '30000', 'W': '8', 'Dp': '5.0', 'Pt': 'F'}, {'$': '840', 'D': 'SW', 'H': '82.9', 'P': '1014', 'S': '15', 'T': '7.8', 'V': '40000', 'W': '7', 'Dp': '5.1', 'Pt': 'F'}, {'$': '900', 'D': 'SW', 'G': '29', 'H': '84.0', 'P': '1013', 'S': '22', 'T': '7.6', 'V': '40000', 'W': '7', 'Dp': '5.1', 'Pt': 'F'}, {'$': '960', 'D': 'SSW', 'H': '82.9', 'P': '1012', 'S': '18', 'T': '7.1', 'V': '50000', 'W': '0', 'Dp': '4.4', 'Pt': 'F'}, {'$': '1020', 'D': 'S', 'H': '86.3', 'P': '1012', 'S': '17', 'T': '6.6', 'V': '26000', 'W': '7', 'Dp': '4.5', 'Pt': 'F'}, {'$': '1080', 'D': 'S', 'H': '87.5', 'P': '1011', 'S': '21', 'T': '6.3', 'V': '28000', 'W': '7', 'Dp': '4.4', 'Pt': 'F'}, {'$': '1140', 'D': 'SSW', 'H': '88.1', 'P': '1010', 'S': '19', 'T': '6.4', 'V': '23000', 'W': '2', 'Dp': '4.6', 'Pt': 'F'}, {'$': '1200', 'D': 'S', 'G': '29', 'H': '87.6', 'P': '1009', 'S': '21', 'T': '6.6', 'V': '24000', 'W': '7', 'Dp': '4.7', 'Pt': 'F'}, {'$': '1260', 'D': 'S', 'G': '29', 'H': '83.9', 'P': '1007', 'S': '19', 'T': '6.7', 'V': '29000', 'W': '8', 'Dp': '4.2', 'Pt': 'F'}, {'$': '1320', 'D': 'S', 'G': '29', 'H': '81.7', 'P': '1006', 'S': '22', 'T': '6.8', 'V': '30000', 'W': '8', 'Dp': '3.9', 'Pt': 'F'}, {'$': '1380', 'D': 'S', 'G': '31', 'H': '82.4', 'P': '1004', 'S': '24', 'T': '7.1', 'V': '26000', 'W': '8', 'Dp': '4.3', 'Pt': 'F'}], 'type': 'Day', 'value': '2020-01-01Z'}] SCOTLAND EUROPE 26.0
4 3023 57.358 -7.397 SOUTH UIST RANGE [{'Rep': {'$': '1380', 'D': 'S', 'H': '89.4', 'P': '1025', 'S': '22', 'T': '7.3', 'V': '15000', 'W': '8', 'Dp': '5.7', 'Pt': 'F'}, 'type': 'Day', 'value': '2019-12-31Z'}, {'Rep': [{'$': '0', 'D': 'S', 'H': '93.3', 'P': '1024', 'S': '19', 'T': '7.3', 'V': '15000', 'W': '8', 'Dp': '6.3', 'Pt': 'F'}, {'$': '60', 'D': 'S', 'H': '94.6', 'P': '1023', 'S': '22', 'T': '7.9', 'V': '12000', 'W': '8', 'Dp': '7.1', 'Pt': 'F'}, {'$': '120', 'D': 'S', 'G': '33', 'H': '90.2', 'P': '1022', 'S': '26', 'T': '8.5', 'V': '25000', 'W': '7', 'Dp': '7.0', 'Pt': 'F'}, {'$': '180', 'D': 'S', 'G': '39', 'H': '87.7', 'P': '1021', 'S': '29', 'T': '8.1', 'V': '40000', 'W': '8', 'Dp': '6.2', 'Pt': 'F'}, {'$': '240', 'D': 'SSW', 'G': '39', 'H': '84.7', 'P': '1021', 'S': '29', 'T': '8.5', 'V': '20000', 'W': '8', 'Dp': '6.1', 'Pt': 'F'}, {'$': '300', 'D': 'SSW', 'G': '43', 'H': '85.9', 'P': '1020', 'S': '31', 'T': '8.5', 'V': '23000', 'W': '8', 'Dp': '6.3', 'Pt': 'F'}, {'$': '360', 'D': 'S', 'G': '38', 'H': '90.8', 'P': '1020', 'S': '25', 'T': '8.5', 'V': '15000', 'W': '8', 'Dp': '7.1', 'Pt': 'F'}, {'$': '420', 'D': 'SSW', 'G': '38', 'H': '92.0', 'P': '1019', 'S': '26', 'T': '8.4', 'V': '5000', 'W': '8', 'Dp': '7.2', 'Pt': 'F'}, {'$': '480', 'D': 'S', 'G': '38', 'H': '97.9', 'P': '1019', 'S': '26', 'T': '8.2', 'V': '3700', 'W': '9', 'Dp': '7.9', 'Pt': 'F'}, {'$': '540', 'D': 'SSW', 'G': '41', 'H': '97.9', 'P': '1018', 'S': '30', 'T': '8.4', 'V': '4800', 'W': '8', 'Dp': '8.1', 'Pt': 'F'}, {'$': '600', 'D': 'SSW', 'G': '37', 'H': '95.9', 'P': '1018', 'S': '28', 'T': '8.9', 'V': '11000', 'W': '8', 'Dp': '8.3', 'Pt': 'F'}, {'$': '660', 'D': 'SSW', 'G': '38', 'H': '93.4', 'P': '1018', 'S': '28', 'T': '9.1', 'V': '13000', 'W': '8', 'Dp': '8.1', 'Pt': 'F'}, {'$': '720', 'D': 'SSW', 'G': '37', 'H': '92.1', 'P': '1017', 'S': '28', 'T': '9.0', 'V': '15000', 'W': '8', 'Dp': '7.8', 'Pt': 'F'}, {'$': '780', 'D': 'S', 'G': '38', 'H': '90.9', 'P': '1016', 'S': '28', 'T': '9.1', 'V': '9000', 'W': '8', 'Dp': '7.7', 'Pt': 'F'}, {'$': '840', 'D': 'S', 'G': '41', 'H': '87.8', 'P': '1015', 'S': '30', 'T': '9.1', 'V': '19000', 'W': '8', 'Dp': '7.2', 'Pt': 'F'}, {'$': '900', 'D': 'S', 'G': '44', 'H': '87.2', 'P': '1014', 'S': '31', 'T': '9.1', 'V': '18000', 'W': '8', 'Dp': '7.1', 'Pt': 'F'}, {'$': '960', 'D': 'S', 'G': '46', 'H': '86.6', 'P': '1013', 'S': '31', 'T': '9.1', 'V': '24000', 'W': '8', 'Dp': '7.0', 'Pt': 'F'}, {'$': '1020', 'D': 'S', 'G': '43', 'H': '87.2', 'P': '1012', 'S': '29', 'T': '9.1', 'V': '25000', 'W': '8', 'Dp': '7.1', 'Pt': 'F'}, {'$': '1080', 'D': 'S', 'G': '44', 'H': '91.5', 'P': '1011', 'S': '33', 'T': '8.9', 'V': '14000', 'W': '7', 'Dp': '7.6', 'Pt': 'F'}, {'$': '1140', 'D': 'S', 'G': '47', 'H': '92.8', 'P': '1010', 'S': '33', 'T': '8.7', 'V': '7000', 'W': '8', 'Dp': '7.6', 'Pt': 'F'}, {'$': '1200', 'D': 'S', 'G': '48', 'H': '91.4', 'P': '1009', 'S': '33', 'T': '8.8', 'V': '12000', 'W': '8', 'Dp': '7.5', 'Pt': 'F'}, {'$': '1260', 'D': 'S', 'G': '47', 'H': '91.5', 'P': '1008', 'S': '34', 'T': '8.7', 'V': '18000', 'W': '8', 'Dp': '7.4', 'Pt': 'F'}, {'$': '1320', 'D': 'S', 'G': '46', 'H': '89.0', 'P': '1007', 'S': '33', 'T': '9.0', 'V': '19000', 'W': '8', 'Dp': '7.3', 'Pt': 'F'}, {'$': '1380', 'D': 'S', 'G': '44', 'H': '88.5', 'P': '1006', 'S': '34', 'T': '9.2', 'V': '12000', 'W': '8', 'Dp': '7.4', 'Pt': 'F'}], 'type': 'Day', 'value': '2020-01-01Z'}] SCOTLAND EUROPE 4.0
What would be the best way to flatten Period column ? is there a better way to achieve desired result?
Thank you.
P.S full json file is at https://wetransfer.com/downloads/5dd39d51e640d94a87e04297bfa1db3d20200909162616/c41164
json_normalize to open the dicts.explode to explode the lists of dicts
dict in the list will move to a separate row.json_normalize on the new column of dicts'Location' has a 'Period''Period' is a list of dicts.
dict is 'Rep', which is a dictdict is also 'Rep', but it is a list of dicts'Period' is normlized, the first 'Rep' gets expanded into separate columns ('Rep.$', 'Rep.D', etc.), but the 2nd 'Rep' is a column of NaN and lists of dicts.lists of dicts in 'Rep' get exploded, so each dict is on a separate row.
dicts are then normalized to separate columns ('$', 'D', etc.), the column headers are renamed to add 'Rep.' to the front, and finally, used to fill the NaNs in the corresponding columns in dataframe df.import pandas as pd
import json
# read in the JSON file
with open('metoffice.json', encoding='utf-8') as f:
data = json.loads(f.read())
# normalize Location
df = pd.json_normalize(data, ['SiteRep', 'DV', 'Location'])
# explode the list of dicts in Period
df = df.explode('Period', ignore_index=True)
# normalize and join Period back to df
df = df.join(pd.json_normalize(df.Period)).drop(columns=['Period'])
# Rep contains NaNs or lists of dicts
# NaN can't be exploded so they must be filled with empty lists
# .fillna([]) does not work
df.Rep = df.Rep.fillna({i: [] for i in df.index})
# explode the lists on Rep
df = df.explode('Rep', ignore_index=True)
# fillna with {} to use json_normalize
df.Rep = df.Rep.fillna({i: {} for i in df.index})
# normalize Rep
rep = pd.json_normalize(df.Rep)
# add Rep. to beginning of column names in the rep dataframe
rep.columns = [f'Rep.{v}' for v in rep.columns]
# fillna on the the Rep. columns from the rep dataframe and drop the Rep column
df = df.fillna(rep).drop(columns=['Rep'])
'Rep', for the first 'Location', which matches the JSON file. i lat lon name country continent elevation type value Rep.$ Rep.D Rep.G Rep.H Rep.P Rep.S Rep.T Rep.V Rep.W Rep.Dp Rep.Pt
0 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2019-12-31Z 1380 SW 34 79.5 1019 25 7.9 13000 8 4.6 F
1 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 0 SW 32 84.0 1018 21 7.5 13000 8 5.0 F
2 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 60 SW 34 81.7 1018 22 7.5 12000 8 4.6 F
3 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 120 SW 36 79.9 1017 24 7.9 11000 8 4.7 F
4 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 180 SW 40 82.3 1016 23 7.5 13000 8 4.7 F
5 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 240 SW 33 84.6 1015 18 8.0 12000 8 5.6 F
6 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 300 SW 33 85.3 1015 24 8.3 11000 8 6.0 F
7 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 360 WSW 41 89.0 1014 30 8.5 8000 8 6.8 F
8 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 420 SW 43 89.6 1013 28 8.7 7000 7 7.1 F
9 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 480 SW 39 88.4 1013 23 8.7 15000 7 6.9 F
10 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 540 SW 40 84.3 1013 29 9.1 19000 8 6.6 F
11 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 600 SW 41 85.4 1012 24 8.9 12000 8 6.6 F
12 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 660 SW 38 84.2 1012 28 9.2 13000 8 6.7 F
13 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 720 SW 47 83.6 1011 32 9.4 12000 8 6.8 F
14 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 780 WSW 45 84.8 1011 30 9.4 11000 8 7.0 F
15 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 840 SW 43 86.0 1010 28 9.4 11000 7 7.2 F
16 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 900 WSW 40 85.4 1009 29 9.4 12000 8 7.1 F
17 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 960 SW 39 86.0 1009 25 9.2 11000 8 7.0 F
18 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 1020 SW 33 87.8 1009 23 8.9 11000 8 7.0 F
19 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 1080 SW 36 85.5 1008 23 8.9 11000 8 6.6 F
20 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 1140 SW 40 86.6 1007 28 8.8 14000 8 6.7 F
21 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 1200 SSW 39 84.8 1006 28 8.8 13000 8 6.4 F
22 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 1260 SSW 37 87.7 1005 26 8.0 15000 8 6.1 F
23 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 1320 S 37 88.4 1003 24 8.0 13000 8 6.2 F
24 3002 60.749 -0.854 BALTASOUND SCOTLAND EUROPE 15.0 Day 2020-01-01Z 1380 S 38 89.6 1002 29 7.6 11000 8 6.0 F
25 3005 60.139 -1.183 LERWICK (S. SCREEN) SCOTLAND EUROPE 82.0 Day 2019-12-31Z 1380 W 41 89.5 1020 28 7.2 15000 8 5.6 F