1

looking for help I have exhausted my limited ability. The below code gives me everything I need except there are two embedded field 'sectionaltimes' and 'splittimes' which I also need to be included in the dataframe and therefore exported to excel as individual components rather than as a long string.

import requests
import json
import pandas as pd
import xlsxwriter

pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)

      
headers = {'User-Agent' : 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:103.0) Gecko/20100101 Firefox/103.0',
       'Accept-Language' : 'en-US,en;q=0.5'}

df=pd.DataFrame()
for race in range(1, 9):
    url = f"https://s3-ap-southeast-2.amazonaws.com/racevic.static/2015-01-01/flemington/sectionaltimes/race-{race}.json?callback=sectionaltimes_callback"
    r = requests.get(url, headers=headers)
    json_obj = json.loads(r.text.split('sectionaltimes_callback(')[1].rsplit(')', 1)[0])
    main_df=pd.DataFrame(json_obj['Horses'])
    df=pd.concat([df,main_df])
df.reset_index(drop=True, inplace=True)
df.to_excel("20150101.xlsx")

enter image description here

enter image description here

enter image description here

Learner
  • 31
  • 6
  • Your `df` returns 80 rows. Can you show the expected output ? – Timeless Aug 23 '22 at 03:29
  • There is two columns called "SectionalTimes" "SplitTimes" which are actually strings and I want to be able to break them up in the individual components rather for example the following layout [{'Distance': '1200m-1000m', 'Position': 2, 'Time': '12.80', 'AvgSpeed': 0.0}, {'Distance': '1000m-800m', 'Position': 2, 'Time': '12.70', 'AvgSpeed': 0.0}, {'Distance': '800m-600m', 'Position': 2, 'Time': '12.30', 'AvgSpeed': 0.0}, {'Distance': '600m-400m', 'Position': 2, 'Time': '12.16', 'AvgSpeed': 0.0}, – Learner Aug 23 '22 at 03:32
  • What do you mean by _break them up in the individual components_ ? Can't you display the expected output in your post/question ? It would be much easier for other to help you. – Timeless Aug 23 '22 at 03:37
  • I have added picture to better explain, I apologise if I cannot explain myself well enough I am on a steep learning curve – Learner Aug 23 '22 at 03:41
  • No worries but I'm afraid I still don't understand what you're looking for. You put two screenshots of the original `df` and not what you're expecting. Can you be more explicit with a much clearer output ? – Timeless Aug 23 '22 at 03:47
  • The third image is what I am hoping to expand the column called SectionalTimes, the numbers do not match up because it is from a different example. I did the conversion using json to csv online converter – Learner Aug 23 '22 at 03:55
  • I believe I probably need to create separate df for SectionalTimes and SplitTImes as I think they are separate within the json file as they are displayed with []. I am able to convert the json file to excel using an online converter and I want to write code to do this automatically. The third image is the output of the online json to csv converter. – Learner Aug 23 '22 at 03:56
  • 1
    Check out this post: https://stackoverflow.com/questions/72332222/how-do-i-json-normalize-a-specific-field-within-a-df-and-keep-the-other-column. You need to get rid of `[``]`in your two columns so you do the json transformation. Good luck ! – Timeless Aug 23 '22 at 04:16
  • 1
    Thank you very much I really appreciate you taking the time to respond. – Learner Aug 23 '22 at 04:56

0 Answers0