Pandas Dataframe Only Returning first Row of JSON Data

Question

I'm working on a web scraping project, and have all the right code that returns me the json data in the format that I want if I used the #print command below, but when I got to run the same code except through Pandas Dataframe it only returns the first row of Data that I'm looking for. Just running the print, it returns the expected 17 rows of data I'm looking for. Dataframe to CSV gives me the first row only. Totally stumped! So grateful for anyone's help!

for item in response['body']:
    DepartureDate = item['legs'][0][0]['departDate']
    ReturnDate = item['legs'][1][0]['departDate']
    Airline = item['legs'][0][0]['airline']['code']
    Origin = item['legs'][0][0]['depart']
    Destination = item['legs'][0][0]['destination']
    OD = (Origin + Destination)
    TrueBaseFare = item['breakdown']['baseFareAmount']
    YQYR = item['breakdown']['fuelSurcharge']
    TAX = item['breakdown']['totalTax']
    TTL = item['breakdown']['totalFareAmount']
    MARKEDUPTTL = item['breakdown']['totalCalculatedFareAmount']
    MARKUP = ((MARKEDUPTTL - TTL) / (TTL)*100)
    FBC = item['fareBasisCode']

    #print(DepartureDate,ReturnDate,Airline,OD,TrueBaseFare,YQYR,TAX,TTL,MARKEDUPTTL,MARKUP,FBC)

MI = pd.DataFrame(
     {'Dept': [DepartureDate],
     'Ret': [ReturnDate],
     'AirlineCode': [Airline],
     'Routing': [OD],
     'RealFare': [TrueBaseFare],
     'Fuel': [YQYR],
     'Taxes': [TAX],
     'RealTotal': [TTL],
     'AgencyTotal': [MARKEDUPTTL],
     'Margin': [MARKUP],
     'FareBasis': [FBC],
    })

df = pd.DataFrame(MI)

pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
pd.set_option('display.width', None)

df.to_csv('MITest7.csv')

dimay · Accepted Answer · 2021-01-16 11:55:23Z

3

When you print all your values after the cycle, you will see that you get only the last values. To resolve this problem you need to create lists and put there your values. Try this:

DepartureDate = []
ReturnDate = []
Airline = []
Origin = []
Destination = []
OD = []
TrueBaseFare = []
YQYR = []
TAX = []
TTL = []
MARKEDUPTTL = []
MARKUP = []
FBC = []

for item in response['body']:
    DepartureDate.append(item['legs'][0][0]['departDate'])
    ReturnDate.append(item['legs'][1][0]['departDate'])
    Airline.append(item['legs'][0][0]['airline']['code'])
    Origin.append(item['legs'][0][0]['depart'])
    Destination.append(item['legs'][0][0]['destination'])
    OD.append((Origin[-1] + Destination[-1]))
    TrueBaseFare.append(item['breakdown']['baseFareAmount'])
    YQYR.append(item['breakdown']['fuelSurcharge'])
    TAX.append(item['breakdown']['totalTax'])
    TTL.append(item['breakdown']['totalFareAmount'])
    MARKEDUPTTL.append(item['breakdown']['totalCalculatedFareAmount'])
    MARKUP.append(((MARKEDUPTTL[-1] - TTL[-1]) / (TTL[-1])*100))
    FBC.append(item['fareBasisCode'])

edited Jan 16, 2021 at 11:55

answered Jan 16, 2021 at 8:55

dimay

2,8341 gold badge17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joseph Colby Over a year ago

Thank you!! That worked to have the .csv file have all the expected data, but oddly it is putting all the returns into the same row. It should be displaying 11 columns and 17 rows of data. Right now it is showing 11 columns with 1 row with each cell including all 17 rows. Any thoughts?

Collectives™ on Stack Overflow

Pandas Dataframe Only Returning first Row of JSON Data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related