3

I've searched a few hours for a solution but none fit my case. I'm new to this forum and I have been learning Python over the past few weeks. Any feedback is very welcome.

I'm getting the following JSON response from the QPX Flights API from Google:

{"trips": {"data": {"tax": [{"id": "CJ_001", "name": "Netherlands Security   Service Charge", "kind": "qpxexpress#taxData"}, {"id": "RN_001", "name": "Netherlands Passenger Service Charge", "kind": "qpxexpress#taxData"}], "carrier": [{"name": "British Airways", "kind": "qpxexpress#carrierData", "code": "BA"}, {"name": "KLM Royal Dutch Airlines", "kind": "qpxexpress#carrierData", "code": "KL"}], "airport": [{"name": "Amsterdam Schiphol Airport", "kind": "qpxexpress#airportData", "city": "AMS", "code": "AMS"}, {"name": "London Heathrow", "kind": "qpxexpress#airportData", "city": "LON", "code": "LHR"}], "kind": "qpxexpress#data", "city": [{"name": "Amsterdam", "kind": "qpxexpress#cityData", "code": "AMS"}, {"name": "London", "kind": "qpxexpress#cityData", "code": "LON"}], "aircraft": [{"name": "Airbus A320", "kind": "qpxexpress#aircraftData", "code": "320"}, {"name": "Airbus A321", "kind": "qpxexpress#aircraftData", "code": "321"}, {"name": "Airbus A320", "kind": "qpxexpress#aircraftData", "code": "32A"}, {"name": "Boeing 737", "kind": "qpxexpress#aircraftData", "code": "73W"}, {"name": "Boeing 767", "kind": "qpxexpress#aircraftData", "code": "767"}, {"name": "Fokker 70", "kind": "qpxexpress#aircraftData", "code": "F70"}]}, "kind": "qpxexpress#tripOptions", "requestId": "BhQ2khus9jXocXWm90PsxV", "tripOption": [{"id": "E4xALyuxnXOLIYn7wHGKfO001", "pricing": [{"passengers": {"adultCount": 1, "kind": "qpxexpress#passengerCounts"}, "saleFareTotal": "EUR50.00", "latestTicketingTime": "2017-01-29T23:59-05:00", "segmentPricing": [{"kind": "qpxexpress#segmentPricing", "fareId": "AVLUwzfTaQlZrLiTu8VtkyPdViNbxFexh8kjsAELyroo", "segmentId": "G1zEk39f37ggRVWM"}], "baseFareTotal": "EUR50.00", "saleTaxTotal": "EUR23.53", "fareCalculation": "AMS BA LON 53.45OV2HO NUC 53.45 END ROE 0.935287 FARE EUR 50.00 XT 10.53CJ 13.00RN", "tax": [{"salePrice": "EUR10.53", "code": "CJ", "id": "CJ_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}, {"salePrice": "EUR13.00", "code": "RN", "id": "RN_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}], "fare": [{"carrier": "BA", "origin": "AMS", "basisCode": "OV2HO", "id": "AVLUwzfTaQlZrLiTu8VtkyPdViNbxFexh8kjsAELyroo", "kind": "qpxexpress#fareInfo", "destination": "LON"}], "saleTotal": "EUR73.53", "kind": "qpxexpress#pricingInfo", "ptc": "ADT"}], "kind": "qpxexpress#tripOption", "slice": [{"duration": 65, "kind": "qpxexpress#sliceInfo", "segment": [{"duration": 65, "cabin": "COACH", "leg": [{"aircraft": "32A", "duration": 65, "origin": "AMS", "mileage": 229, "destinationTerminal": "5", "id": "LBxqFQJ94+nxA452", "kind": "qpxexpress#legInfo", "departureTime": "2017-02-23T22:00+01:00", "meal": "Food and Beverages for Purchase", "destination": "LHR", "arrivalTime": "2017-02-23T22:05+00:00"}], "bookingCode": "O", "id": "G1zEk39f37ggRVWM", "kind": "qpxexpress#segmentInfo", "bookingCodeCount": 5, "marriedSegmentGroup": "0", "flight": {"carrier": "BA", "number": "427"}}]}], "saleTotal": "EUR73.53"}, {"id": "E4xALyuxnXOLIYn7wHGKfO002", "pricing": [{"passengers": {"adultCount": 1, "kind": "qpxexpress#passengerCounts"}, "saleFareTotal": "EUR50.00", "latestTicketingTime": "2017-01-29T23:59-05:00", "segmentPricing": [{"kind": "qpxexpress#segmentPricing", "fareId": "AVLUwzfTaQlZrLiTu8VtkyPdViNbxFexh8kjsAELyroo", "segmentId": "GCENLx0HbbMjpJuU"}], "baseFareTotal": "EUR50.00", "saleTaxTotal": "EUR23.53", "fareCalculation": "AMS BA LON 53.45OV2HO NUC 53.45 END ROE 0.935287 FARE EUR 50.00 XT 10.53CJ 13.00RN", "tax": [{"salePrice": "EUR10.53", "code": "CJ", "id": "CJ_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}, {"salePrice": "EUR13.00", "code": "RN", "id": "RN_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}], "fare": [{"carrier": "BA", "origin": "AMS", "basisCode": "OV2HO", "id": "AVLUwzfTaQlZrLiTu8VtkyPdViNbxFexh8kjsAELyroo", "kind": "qpxexpress#fareInfo", "destination": "LON"}], "saleTotal": "EUR73.53", "kind": "qpxexpress#pricingInfo", "ptc": "ADT"}], "kind": "qpxexpress#tripOption", "slice": [{"duration": 80, "kind": "qpxexpress#sliceInfo", "segment": [{"duration": 80, "cabin": "COACH", "leg": [{"aircraft": "767", "duration": 80, "origin": "AMS", "mileage": 229, "destinationTerminal": "5", "id": "LI0hZuGKKlUxi13I", "kind": "qpxexpress#legInfo", "departureTime": "2017-02-23T10:25+01:00", "meal": "Food and Beverages for Purchase", "destination": "LHR", "arrivalTime": "2017-02-23T10:45+00:00"}], "bookingCode": "O", "id": "GCENLx0HbbMjpJuU", "kind": "qpxexpress#segmentInfo", "bookingCodeCount": 9, "marriedSegmentGroup": "0", "flight": {"carrier": "BA", "number": "429"}}]}], "saleTotal": "EUR73.53"}, {"id": "E4xALyuxnXOLIYn7wHGKfO004", "pricing": [{"passengers": {"adultCount": 1, "kind": "qpxexpress#passengerCounts"}, "saleFareTotal": "EUR52.00", "latestTicketingTime": "2017-02-02T23:59-05:00", "segmentPricing": [{"kind": "qpxexpress#segmentPricing", "fareId": "Avqz5Oz2yvEq+Z9r2MO5Za+dHIbSnRoZ0dT09CoUNVDk", "segmentId": "G5-09q7T0cVtOyZK"}], "baseFareTotal": "EUR52.00", "saleTaxTotal": "EUR23.53", "fareCalculation": "AMS KL LHR 55.59TWKWNL NUC 55.59 END ROE 0.935287 FARE EUR 52.00 XT 10.53CJ 13.00RN", "tax": [{"salePrice": "EUR10.53", "code": "CJ", "id": "CJ_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}, {"salePrice": "EUR13.00", "code": "RN", "id": "RN_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}], "fare": [{"carrier": "KL", "origin": "AMS", "basisCode": "TWKWNL", "id": "Avqz5Oz2yvEq+Z9r2MO5Za+dHIbSnRoZ0dT09CoUNVDk", "kind": "qpxexpress#fareInfo", "destination": "LON"}], "saleTotal": "EUR75.53", "kind": "qpxexpress#pricingInfo", "ptc": "ADT"}], "kind": "qpxexpress#tripOption", "slice": [{"duration": 80, "kind": "qpxexpress#sliceInfo", "segment": [{"duration": 80, "cabin": "COACH", "leg": [{"aircraft": "73W", "duration": 80, "origin": "AMS", "mileage": 229, "destinationTerminal": "4", "id": "LUzIvksstTUVZGdm", "kind": "qpxexpress#legInfo", "departureTime": "2017-02-23T16:40+01:00", "meal": "Snack or Brunch", "destination": "LHR", "arrivalTime": "2017-02-23T17:00+00:00"}], "bookingCode": "T", "id": "G5-09q7T0cVtOyZK", "kind": "qpxexpress#segmentInfo", "bookingCodeCount": 9, "marriedSegmentGroup": "0", "flight": {"carrier": "KL", "number": "1023"}}]}], "saleTotal": "EUR75.53"}, {"id": "E4xALyuxnXOLIYn7wHGKfO006", "pricing": [{"passengers": {"adultCount": 1, "kind": "qpxexpress#passengerCounts"}, "saleFareTotal": "EUR52.00", "latestTicketingTime": "2017-02-02T23:59-05:00", "segmentPricing": [{"kind": "qpxexpress#segmentPricing", "fareId": "Avqz5Oz2yvEq+Z9r2MO5Za+dHIbSnRoZ0dT09CoUNVDk", "segmentId": "G+88fLqcZlZxtMPy"}], "baseFareTotal": "EUR52.00", "saleTaxTotal": "EUR23.53", "fareCalculation": "AMS KL LHR 55.59TWKWNL NUC 55.59 END ROE 0.935287 FARE EUR 52.00 XT 10.53CJ 13.00RN", "tax": [{"salePrice": "EUR10.53", "code": "CJ", "id": "CJ_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}, {"salePrice": "EUR13.00", "code": "RN", "id": "RN_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}], "fare": [{"carrier": "KL", "origin": "AMS", "basisCode": "TWKWNL", "id": "Avqz5Oz2yvEq+Z9r2MO5Za+dHIbSnRoZ0dT09CoUNVDk", "kind": "qpxexpress#fareInfo", "destination": "LON"}], "saleTotal": "EUR75.53", "kind": "qpxexpress#pricingInfo", "ptc": "ADT"}], "kind": "qpxexpress#tripOption", "slice": [{"duration": 85, "kind": "qpxexpress#sliceInfo", "segment": [{"duration": 85, "cabin": "COACH", "leg": [{"duration": 85, "mileage": 229, "origin": "AMS", "operatingDisclosure": "OPERATED BY KLM CITYHOPPER", "departureTime": "2017-02-23T13:25+01:00", "destination": "LHR", "aircraft": "F70", "meal": "Snack or Brunch", "id": "LjrWqpaYFS0ZeCQu", "kind": "qpxexpress#legInfo", "destinationTerminal": "4", "arrivalTime": "2017-02-23T13:50+00:00"}], "bookingCode": "T", "id": "G+88fLqcZlZxtMPy", "kind": "qpxexpress#segmentInfo", "bookingCodeCount": 9, "marriedSegmentGroup": "0", "flight": {"carrier": "KL", "number": "1017"}}]}], "saleTotal": "EUR75.53"}, {"id": "E4xALyuxnXOLIYn7wHGKfO003", "pricing": [{"passengers": {"adultCount": 1, "kind": "qpxexpress#passengerCounts"}, "saleFareTotal": "EUR52.00", "latestTicketingTime": "2017-02-02T23:59-05:00", "segmentPricing": [{"kind": "qpxexpress#segmentPricing", "fareId": "Avqz5Oz2yvEq+Z9r2MO5Za+dHIbSnRoZ0dT09CoUNVDk", "segmentId": "Gf7a6ekBsv4DkDJT"}], "baseFareTotal": "EUR52.00", "saleTaxTotal": "EUR23.53", "fareCalculation": "AMS KL LHR 55.59TWKWNL NUC 55.59 END ROE 0.935287 FARE EUR 52.00 XT 10.53CJ 13.00RN", "tax": [{"salePrice": "EUR10.53", "code": "CJ", "id": "CJ_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}, {"salePrice": "EUR13.00", "code": "RN", "id": "RN_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}], "fare": [{"carrier": "KL", "origin": "AMS", "basisCode": "TWKWNL", "id": "Avqz5Oz2yvEq+Z9r2MO5Za+dHIbSnRoZ0dT09CoUNVDk", "kind": "qpxexpress#fareInfo", "destination": "LON"}], "saleTotal": "EUR75.53", "kind": "qpxexpress#pricingInfo", "ptc": "ADT"}], "kind": "qpxexpress#tripOption", "slice": [{"duration": 80, "kind": "qpxexpress#sliceInfo", "segment": [{"duration": 80, "cabin": "COACH", "leg": [{"aircraft": "73W", "duration": 80, "origin": "AMS", "mileage": 229, "destinationTerminal": "4", "id": "L4xrJvVAub9Z6RLr", "kind": "qpxexpress#legInfo", "departureTime": "2017-02-23T14:05+01:00", "meal": "Snack or Brunch", "destination": "LHR", "arrivalTime": "2017-02-23T14:25+00:00"}], "bookingCode": "T", "id": "Gf7a6ekBsv4DkDJT", "kind": "qpxexpress#segmentInfo", "bookingCodeCount": 9, "marriedSegmentGroup": "0", "flight": {"carrier": "KL", "number": "1019"}}]}], "saleTotal": "EUR75.53"}, {"id": "E4xALyuxnXOLIYn7wHGKfO005", "pricing": [{"passengers": {"adultCount": 1, "kind": "qpxexpress#passengerCounts"}, "saleFareTotal": "EUR52.00", "latestTicketingTime": "2017-02-02T23:59-05:00", "segmentPricing": [{"kind": "qpxexpress#segmentPricing", "fareId": "Avqz5Oz2yvEq+Z9r2MO5Za+dHIbSnRoZ0dT09CoUNVDk", "segmentId": "GH-m3IFMAHigBWVY"}], "baseFareTotal": "EUR52.00", "saleTaxTotal": "EUR23.53", "fareCalculation": "AMS KL LHR 55.59TWKWNL NUC 55.59 END ROE 0.935287 FARE EUR 52.00 XT 10.53CJ 13.00RN", "tax": [{"salePrice": "EUR10.53", "code": "CJ", "id": "CJ_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}, {"salePrice": "EUR13.00", "code": "RN", "id": "RN_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}], "fare": [{"carrier": "KL", "origin": "AMS", "basisCode": "TWKWNL", "id": "Avqz5Oz2yvEq+Z9r2MO5Za+dHIbSnRoZ0dT09CoUNVDk", "kind": "qpxexpress#fareInfo", "destination": "LON"}], "saleTotal": "EUR75.53", "kind": "qpxexpress#pricingInfo", "ptc": "ADT"}], "kind": "qpxexpress#tripOption", "slice": [{"duration": 80, "kind": "qpxexpress#sliceInfo", "segment": [{"duration": 80, "cabin": "COACH", "leg": [{"aircraft": "73W", "duration": 80, "origin": "AMS", "mileage": 229, "destinationTerminal": "4", "id": "LuMeslHrXHwbRpt0", "kind": "qpxexpress#legInfo", "departureTime": "2017-02-23T15:45+01:00", "meal": "Snack or Brunch", "destination": "LHR", "arrivalTime": "2017-02-23T16:05+00:00"}], "bookingCode": "T", "id": "GH-m3IFMAHigBWVY", "kind": "qpxexpress#segmentInfo", "bookingCodeCount": 9, "marriedSegmentGroup": "0", "flight": {"carrier": "KL", "number": "1021"}}]}], "saleTotal": "EUR75.53"}, {"id": "E4xALyuxnXOLIYn7wHGKfO009", "pricing": [{"passengers": {"adultCount": 1, "kind": "qpxexpress#passengerCounts"}, "saleFareTotal": "EUR65.00", "latestTicketingTime": "2017-01-29T23:59-05:00", "segmentPricing": [{"kind": "qpxexpress#segmentPricing", "fareId": "Av0m+4T0gfPWPPwgf+2RMUjrZbH+V/WMYJcsuO7boQrA", "segmentId": "G80z2q6vFhi3qi3u"}], "baseFareTotal": "EUR65.00", "saleTaxTotal": "EUR23.53", "fareCalculation": "AMS BA LON 69.49QV2HO NUC 69.49 END ROE 0.935287 FARE EUR 65.00 XT 10.53CJ 13.00RN", "tax": [{"salePrice": "EUR10.53", "code": "CJ", "id": "CJ_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}, {"salePrice": "EUR13.00", "code": "RN", "id": "RN_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}], "fare": [{"carrier": "BA", "origin": "AMS", "basisCode": "QV2HO", "id": "Av0m+4T0gfPWPPwgf+2RMUjrZbH+V/WMYJcsuO7boQrA", "kind": "qpxexpress#fareInfo", "destination": "LON"}], "saleTotal": "EUR88.53", "kind": "qpxexpress#pricingInfo", "ptc": "ADT"}], "kind": "qpxexpress#tripOption", "slice": [{"duration": 80, "kind": "qpxexpress#sliceInfo", "segment": [{"duration": 80, "cabin": "COACH", "leg": [{"aircraft": "320", "duration": 80, "origin": "AMS", "mileage": 229, "destinationTerminal": "5", "id": "LlKQSTYZkyUEt08t", "kind": "qpxexpress#legInfo", "departureTime": "2017-02-23T07:30+01:00", "meal": "Food and Beverages for Purchase", "destination": "LHR", "arrivalTime": "2017-02-23T07:50+00:00"}], "bookingCode": "Q", "id": "G80z2q6vFhi3qi3u", "kind": "qpxexpress#segmentInfo", "bookingCodeCount": 6, "marriedSegmentGroup": "0", "flight": {"carrier": "BA", "number": "423"}}]}], "saleTotal": "EUR88.53"}, {"id": "E4xALyuxnXOLIYn7wHGKfO008", "pricing": [{"passengers": {"adultCount": 1, "kind": "qpxexpress#passengerCounts"}, "saleFareTotal": "EUR65.00", "latestTicketingTime": "2017-01-29T23:59-05:00", "segmentPricing": [{"kind": "qpxexpress#segmentPricing", "fareId": "Av0m+4T0gfPWPPwgf+2RMUjrZbH+V/WMYJcsuO7boQrA", "segmentId": "G5AkwObWJkOpFBc6"}], "baseFareTotal": "EUR65.00", "saleTaxTotal": "EUR23.53", "fareCalculation": "AMS BA LON 69.49QV2HO NUC 69.49 END ROE 0.935287 FARE EUR 65.00 XT 10.53CJ 13.00RN", "tax": [{"salePrice": "EUR10.53", "code": "CJ", "id": "CJ_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}, {"salePrice": "EUR13.00", "code": "RN", "id": "RN_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}], "fare": [{"carrier": "BA", "origin": "AMS", "basisCode": "QV2HO", "id": "Av0m+4T0gfPWPPwgf+2RMUjrZbH+V/WMYJcsuO7boQrA", "kind": "qpxexpress#fareInfo", "destination": "LON"}], "saleTotal": "EUR88.53", "kind": "qpxexpress#pricingInfo", "ptc": "ADT"}], "kind": "qpxexpress#tripOption", "slice": [{"duration": 75, "kind": "qpxexpress#sliceInfo", "segment": [{"duration": 75, "cabin": "COACH", "leg": [{"aircraft": "321", "duration": 75, "origin": "AMS", "mileage": 229, "destinationTerminal": "5", "id": "L7gBInJ8AQlYjD-Y", "kind": "qpxexpress#legInfo", "departureTime": "2017-02-23T16:10+01:00", "meal": "Food and Beverages for Purchase", "destination": "LHR", "arrivalTime": "2017-02-23T16:25+00:00"}], "bookingCode": "Q", "id": "G5AkwObWJkOpFBc6", "kind": "qpxexpress#segmentInfo", "bookingCodeCount": 8, "marriedSegmentGroup": "0", "flight": {"carrier": "BA", "number": "439"}}]}], "saleTotal": "EUR88.53"}, {"id": "E4xALyuxnXOLIYn7wHGKfO00A", "pricing": [{"passengers": {"adultCount": 1, "kind": "qpxexpress#passengerCounts"}, "saleFareTotal": "EUR65.00", "latestTicketingTime": "2017-01-29T23:59-05:00", "segmentPricing": [{"kind": "qpxexpress#segmentPricing", "fareId": "Av0m+4T0gfPWPPwgf+2RMUjrZbH+V/WMYJcsuO7boQrA", "segmentId": "GTxfb06FkQUnFyVd"}], "baseFareTotal": "EUR65.00", "saleTaxTotal": "EUR23.53", "fareCalculation": "AMS BA LON 69.49QV2HO NUC 69.49 END ROE 0.935287 FARE EUR 65.00 XT 10.53CJ 13.00RN", "tax": [{"salePrice": "EUR10.53", "code": "CJ", "id": "CJ_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}, {"salePrice": "EUR13.00", "code": "RN", "id": "RN_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}], "fare": [{"carrier": "BA", "origin": "AMS", "basisCode": "QV2HO", "id": "Av0m+4T0gfPWPPwgf+2RMUjrZbH+V/WMYJcsuO7boQrA", "kind": "qpxexpress#fareInfo", "destination": "LON"}], "saleTotal": "EUR88.53", "kind": "qpxexpress#pricingInfo", "ptc": "ADT"}], "kind": "qpxexpress#tripOption", "slice": [{"duration": 80, "kind": "qpxexpress#sliceInfo", "segment": [{"duration": 80, "cabin": "COACH", "leg": [{"aircraft": "320", "duration": 80, "origin": "AMS", "mileage": 229, "destinationTerminal": "5", "id": "L9cSW5fHx3M08c51", "kind": "qpxexpress#legInfo", "departureTime": "2017-02-23T14:05+01:00", "meal": "Food and Beverages for Purchase", "destination": "LHR", "arrivalTime": "2017-02-23T14:25+00:00"}], "bookingCode": "Q", "id": "GTxfb06FkQUnFyVd", "kind": "qpxexpress#segmentInfo", "bookingCodeCount": 6, "marriedSegmentGroup": "0", "flight": {"carrier": "BA", "number": "435"}}]}], "saleTotal": "EUR88.53"}, {"id": "E4xALyuxnXOLIYn7wHGKfO007", "pricing": [{"passengers": {"adultCount": 1, "kind": "qpxexpress#passengerCounts"}, "saleFareTotal": "EUR65.00", "latestTicketingTime": "2017-01-29T23:59-05:00", "segmentPricing": [{"kind": "qpxexpress#segmentPricing", "fareId": "Av0m+4T0gfPWPPwgf+2RMUjrZbH+V/WMYJcsuO7boQrA", "segmentId": "G0X5FxdiIt0JAMFD"}], "baseFareTotal": "EUR65.00", "saleTaxTotal": "EUR23.53", "fareCalculation": "AMS BA LON 69.49QV2HO NUC 69.49 END ROE 0.935287 FARE EUR 65.00 XT 10.53CJ 13.00RN", "tax": [{"salePrice": "EUR10.53", "code": "CJ", "id": "CJ_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}, {"salePrice": "EUR13.00", "code": "RN", "id": "RN_001", "kind": "qpxexpress#taxInfo", "chargeType": "GOVERNMENT", "country": "NL"}], "fare": [{"carrier": "BA", "origin": "AMS", "basisCode": "QV2HO", "id": "Av0m+4T0gfPWPPwgf+2RMUjrZbH+V/WMYJcsuO7boQrA", "kind": "qpxexpress#fareInfo", "destination": "LON"}], "saleTotal": "EUR88.53", "kind": "qpxexpress#pricingInfo", "ptc": "ADT"}], "kind": "qpxexpress#tripOption", "slice": [{"duration": 75, "kind": "qpxexpress#sliceInfo", "segment": [{"duration": 75, "cabin": "COACH", "leg": [{"aircraft": "32A", "duration": 75, "origin": "AMS", "mileage": 229, "destinationTerminal": "5", "id": "LEufacirrlmtQPpd", "kind": "qpxexpress#legInfo", "departureTime": "2017-02-23T12:00+01:00", "meal": "Food and Beverages for Purchase", "destination": "LHR", "arrivalTime": "2017-02-23T12:15+00:00"}], "bookingCode": "Q", "id": "G0X5FxdiIt0JAMFD", "kind": "qpxexpress#segmentInfo", "bookingCodeCount": 8, "marriedSegmentGroup": "0", "flight": {"carrier": "BA", "number": "431"}}]}], "saleTotal": "EUR88.53"}]}, "kind": "qpxExpress#tripsSearch"}

I've prettyprinted the data. For each flight, I would like all keys as columns and all values of the keys as rows. Desired output example (the more key-value pairs, the better):

fareId, aircraft name, origin, origin name, destination, destination name, carrier code, carrier name, adultCount, saleFareTotal, saleTaxTotal, saleTotal, arrivalTime, departureTime
Av0m+4T0gfPWPPwgf+2RMUjrZbH+V/WMYJcsuO7boQrA, Airbus A320, AMS, Amsterdam Schiphol Airport, LHR, London Heathrow, BA, British Airways, 1, EUR82.00, EUR67.24, EUR149.24, 2017-02-20T22:05+00:00, 2017-02-20T22:00+01:00
Avqz5Oz2yvEq+Z9r2MO5Za+dHIbSnRoZ0dT09CoUNVDk, Boeing 767, AMS, Amsterdam Schiphol Airport, LHR, London Heathrow, BA, British Airways, 1,  EUR82.00, EUR67.24, EUR149.24, 2017-02-23T09:05+01:00, 2017-02-23T06:45+00:00

I'm trying to normalize this data. Used many solutions on SO, Google, Youtube. I've tried loading the raw_data into a pandas dataframe, flattening the dictionary and normalizing the data like so:

#! python3
import json
import pandas as pd
from pandas.io.json import json_normalize
from pprint import pprint

with open ("jsondata-complex.json", 'r') as f:
    raw_data = json.load(f)
    keys_raw = raw_data.keys()
    pprint(raw_data)


#accessing raw_data
print("\nKeys in raw data: ", keys_raw, "\n\nBased on 'raw' data: \n")
print("Departure: ", raw_data["trips"]["tripOption"][0]["slice"][0]["segment"][0]['leg'][0]['departureTime'])
print("Arrival: ", raw_data["trips"]["tripOption"][0]["slice"][0]["segment"][0]['leg'][0]['arrivalTime'])
print("Carrier: ", raw_data["trips"]["data"]["carrier"][0]["name"])
print("Origin: ", raw_data["trips"]["data"]["city"][0]["name"], "(",raw_data["trips"]["data"]["city"][0]["code"],")")
print("Destination: ", raw_data["trips"]["data"]["city"][1]["name"],"(",raw_data["trips"]["data"]["city"][1]["code"],")")
print("Saletotal: ", raw_data["trips"]["tripOption"][0]["pricing"][0]["saleTotal"])

#fetching all nested keys    
def iteritems_nested(d):
  def fetch (suffixes, v0) :
    if isinstance(v0, dict):
      for k, v in v0.items() :
        for i in fetch(suffixes + [k], v):
          yield i
    else:
      yield (suffixes, v0)

  return fetch([], d)

#joining nested keys '.' and return dict
def flatten_dict(d) :
  return dict( ('.'.join(ks), v) for ks, v in iteritems_nested(d))

#passing raw json
iteritems_nested(raw_data)
flat_data = flatten_dict(raw_data)
keys_flat = flat_data.keys()

#accessing flat_data
print("Keys in flat dict: ", keys_flat, "\n\nBased on 'flat' data: \n")

print("Departure: ", flat_data['trips.tripOption'][0]["slice"][0]["segment"][0]['leg'][0]['departureTime'])
print("Arrival: ", flat_data['trips.tripOption'][0]["slice"][0]["segment"][0]['leg'][0]['arrivalTime'])
print("Carrier: ", flat_data['trips.data.carrier'][0]["name"])
print("Origin: ", flat_data['trips.data.city'][0]["name"], "(",flat_data['trips.data.city'][0]["code"],")")
print("Destination: ", flat_data['trips.data.city'][1]["name"],"(",flat_data['trips.data.city'][1]["code"],")")
print("Saletotal: ",flat_data['trips.tripOption'][0]["pricing"][0]["saleTotal"])

#normalized_raw_data = json_normalize(raw_data['trips'])
#normalized_flat_data = json_normalize(flat_data['trips.tripOption'])
#print(normalized_raw_data)
#print(normalized_flat_data)

Is there a better way to structure the JSON response in columns (keys) and rows (values)? Pandas dataframe does not like the flat_data or raw data.

Again, any feedback is welcome. Thanks in advance.

Improve this question

1 Answer 1

Reset to default
4

This should help you get your desired results. I made a class to help handle the data and iterated over each flight. I only have some of the data you want, but you can easily add more attributes to the class to hold all the data you want.

Important to note:

You need to add an attribute in the __init__ function, add the attribute in the to_list function, and add the attribute to the headers list. Also the file names are different, as I changed them for testing purposes.

import json
import csv

class Flight:
    def __init__(self, plane, data):
        self.name = plane
        self.origin = data["trips"]["data"]["airport"][0]["code"]
        self.origin_name = data["trips"]["data"]["airport"][0]["name"]
        self.destination = data["trips"]["data"]["airport"][1]["code"]
        self.destination_name = data["trips"]["data"]["airport"][1]["name"]

    def to_list(self):
        return [self.name, self.origin, self.origin_name, self.destination, self.destination_name]

with open ("flight.json", 'r') as f:
    raw_data = json.load(f)

out_data = []
for flight in raw_data['trips']['data']['aircraft']:
    out_data.append(Flight(flight['name'], raw_data).to_list())

headers = ['aircraft name', 'origin', 'origin name', 'destination']
with open('results.csv', 'w') as outfile:
    writer = csv.writer(outfile)
    writer.writerow(headers)
    writer.writerows(out_data)

Output:

aircraft name,origin,origin name,destination
Airbus A320,AMS,Amsterdam Schiphol Airport,LHR,London Heathrow
Airbus A321,AMS,Amsterdam Schiphol Airport,LHR,London Heathrow
Boeing 767,AMS,Amsterdam Schiphol Airport,LHR,London Heathrow
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks a lot for your response and effort. Interesting approach. Added some attributes to the functions and headers and it worked like a charm on the original JSON-file. The downside is, that with different output (more flights), the code doesn't work properly anymore. I'll try again tomorrow. I approved your answer, but my rep score isn't high enough to publicly affect the post score.
If you could show the other outputs, the script could be modified
Thanks. I've updated the output. In it, we should find 10 different fares. All with origin AMS and destination LHR, BUT with different arrivalTime. I want to list these flights accordingly and make the code so that it is flexible enough for changes in the output.
The script I originally wrote will give you the set of planes that are available for the trips. For the second part, you can write a second script that instead of iterating over the planes, it iterates over the trip options for trip in raw_data['tripOption']. You can make a class for each Trip and match the planes based off the aircraft code.
Alright, I'll give it a try. What I'm looking for is an overview of the different fares, with the appropiate carrier, origin, destination, arrivaltime, departuretime, totaleprice (these values are not constant, i.e. they are different for each trip). If I iterate over the trip options, will the script account for these variable values? Anyway, thanks for your post, I will accept it as the answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.