Trying to convert a radar data file, that was sent to me in JSON format, to manageable DataFrame.
The first three lines of the file look like this:
{"id":1,"length":43,"crc":"D81B2DB5","timestamp":1617,"hexdata":"30002EFFD7021483000069E03BF78BE702A001E0FE2104B51D21020234269604D174E75DA008A50312B0620620B6","CAT048":{"I010":{"SAC":20,"SIC":131},"I140":{"ToD":0.8203125},"I020":{"TYP":7,"SIM":0,"RDP":0,"SPI":0,"RAB":0,"FX":0},"I040":{"RHO":59.9648438,"THETA":196.7376709},"I070":{"V":0,"G":0,"L":0,"spare":0,"Mode3A":"1240"},"I090":{"V":0,"G":0,"FL":120},"I130":{"SRLP":{"SRL":1.4501953},"SRRP":{"SRR":4},"SAMP":{"SAM":-75},"PRLP":{"PRL":1.2744141},"PAMP":{"PAM":33},"RPDP":{"RPD":0.0078125},"APDP":{"APD":0.0439453}},"I220":{"ACAddr":"342696"},"I240":{"TId":"AME4956 "},"I161":{"Tn":2213},"I200":{"CGS":172.92,"CHdg":248.0383301},"I170":{"CNF":0,"RAD":0,"DOU":0,"MAH":0,"CDM":3,"FX":0},"I230":{"COM":1,"STAT":0,"SI":0,"spare":0,"ModeSSSC":1,"ARC":0,"AIC":1,"BDS16":1,"BDS37":6}},"lat":38.585666818124,"lon":2.3784905351223,"h":3658.0244306503}
{"id":1,"length":40,"crc":"065756DA","timestamp":2468,"hexdata":"30002BFBB70214830000D2A000C8C0510A38E01804EA34239701803000000000004008BE00369EAE4624A0","CAT048":{"I010":{"SAC":20,"SIC":131},"I140":{"ToD":1.640625},"I020":{"TYP":5,"SIM":0,"RDP":0,"SPI":0,"RAB":0,"FX":0},"I040":{"RHO":0.78125,"THETA":270.4449463},"I070":{"V":0,"G":0,"L":0,"spare":0,"Mode3A":"5070"},"I130":{"SRLP":{"SRL":1.0546875},"SRRP":{"SRR":4},"SAMP":{"SAM":-22}},"I220":{"ACAddr":"342397"},"I250":[{"MCP_ALT_STATUS":1,"MCP_ALT":96,"FMS_ALT_STATUS":0,"FMS_ALT":0,"BP_STATUS":0,"BP":0,"res":0,"MODE_STATUS":0,"VNAV":0,"ALT_HOLD":0,"APP":0,"TARGET_ALT_STATUS":0,"TARGET_ALT_SOURCE":0,"BDS":"40"}],"I161":{"Tn":2238},"I200":{"CGS":11.88,"CHdg":223.1433105},"I170":{"CNF":0,"RAD":2,"DOU":0,"MAH":0,"CDM":3,"FX":0},"I230":{"COM":1,"STAT":1,"SI":0,"spare":0,"ModeSSSC":1,"ARC":0,"AIC":1,"BDS16":0,"BDS37":0}},"lat":39.543535327942,"lon":2.7284206653891,"h":4.2666605189443}
{"id":2,"length":64,"crc":"A45FA0D0","timestamp":2468,"hexdata":"300043FFF7021483000115A0896BE1B70AC105C8E01403BC4BB184508672CB482003C8480030A4018040FFD3C13A7FFCEC509E1A1F342037FF6008C1081E3CF54620F5","CAT048":{"I010":{"SAC":20,"SIC":131},"I140":{"ToD":2.1640625},"I020":{"TYP":5,"SIM":0,"RDP":0,"SPI":0,"RAB":0,"FX":0},"I040":{"RHO":137.4179688,"THETA":317.411499},"I070":{"V":0,"G":0,"L":0,"spare":0,"Mode3A":"5301"},"I090":{"V":0,"G":0,"FL":370},"I130":{"SRLP":{"SRL":0.8789062},"SRRP":{"SRR":3},"SAMP":{"SAM":-68}},"I220":{"ACAddr":"4BB184"},"I240":{"TId":"THY224 "},"I250":[{"MCP_ALT_STATUS":1,"MCP_ALT":37008,"FMS_ALT_STATUS":0,"FMS_ALT":0,"BP_STATUS":1,"BP":213,"res":0,"MODE_STATUS":1,"VNAV":1,"ALT_HOLD":0,"APP":0,"TARGET_ALT_STATUS":0,"TARGET_ALT_SOURCE":0,"BDS":"40"},{"RA_STATUS":1,"RA":-0.3515625,"TTA_STATUS":1,"TTA":84.375,"GS_STATUS":1,"GS":466,"TAR_STATUS":1,"TAR":-0.03125,"TAS_STATUS":1,"TAS":472,"BDS":"50"},{"HDG_STATUS":1,"HDG":84.5507812,"IAS_STAT":1,"IAS":271,"MACH_STATUS":1,"MACH":0.832,"BAR_STATU
I can see these lines contain the info I need, like callsign ("TId": "AME4956 "), heading and so on. Is there a nice Pythonic way to get these values into a Dataframe?
CodePudding user response：
This is almost valid JSON, except the final line seems to be truncated. Pandas can import dictionaries with almost no pain:
import json
import pandas as pd
infos = []
with open(infofile) as fid:
for ln in fid:
infos.append(json.loads(ln))
df = pd.DataFrame(infos)
print(df)
prints:
id length crc timestamp hexdata CAT048 lat lon h
0 1 43 D81B2DB5 1617 30002EFFD7021483000069E03BF78BE702A001E0FE2104... {'I010': {'SAC': 20, 'SIC': 131}, 'I140': {'To... 38.585667 2.378491 3658.024431
1 1 40 065756DA 2468 30002BFBB70214830000D2A000C8C0510A38E01804EA34... {'I010': {'SAC': 20, 'SIC': 131}, 'I140': {'To... 39.543535 2.728421 4.266661
2 2 64 A45FA0D0 2468 300043FFF7021483000115A0896BE1B70AC105C8E01403... {'I010': {'SAC': 20, 'SIC': 131}, 'I140': {'To... NaN NaN NaN