0
$\begingroup$

I am new to Python and I tried writing a script that extracts air quality json data from an API every hour and logs it into a same excel file. My code doesn't return anything. Is my code correct ? and how can I make it log into the excel file every hour please ? Thank you very much.

Here is the script:

def write_to_excel():
    request = requests.get("https://api.waqi.info/feed/paris/? 
     token=?")
    request_text = request.text
    JSON = json.loads(request_text)

    filterJSON = {
        'time': str(JSON['data']['time']['s']),
        'co': str(JSON['data']['iaqi']['co']['v']),
        'h': str(JSON['data']['iaqi']['h']['v']),
        'no2': str(JSON['data']['iaqi']['no2']['v']),
        'o3': str(JSON['data']['iaqi']['o3']['v']),
        'p': str(JSON['data']['iaqi']['p']['v']),
        'pm10': str(JSON['data']['iaqi']['pm10']['v']),
        'pm25': str(JSON['data']['iaqi']['pm25']['v']),
        'so2': str(JSON['data']['iaqi']['so2']['v']),
        't': str(JSON['data']['iaqi']['t']['v']),
        'w': str(JSON['data']['iaqi']['w']['v']),
    }

    liste.append(filterJSON)
    try:
        os.remove("airquality.xlsx")
    except:
        pass

    pd.DataFrame(liste).to_excel('airquality.xlsx')
    print(liste)


if __name__ == "__main__":
     schedule.every(3).seconds.do(write_to_excel)
     while True:
         schedule.run_pending()

'''

$\endgroup$
0
$\begingroup$

Assuming that example is working for you, trying to write the data every 3 seconds, you need to just change the scheduling to be

schedule.every(1).hour.do(write_to_excel)

You are currently writing the data at each interval to the same file, so you will overwrite the file every time. You could do a few things here:

  • Open the excel file (e.g. into a pandas DataFrame), append the new data and save it all back to disk. This is pretty inefficient though and will have problems once you have a lot of data.
  • Write the data to a database, extending it every hour. This is the most professional solution.
  • Write a new file to disk each hour, including e.g. the timestamp of the hour in the filename to make each file unique. This is simple and just means you iterate over the files one-by-one when reading them later to do analysis or plotting etc.

You could change your function to be like this, implementing the first option above:

def request_data_and_save(excel_file: str = "air_quality.xlsx"):
    request = requests.get("https://api.waqi.info/feed/paris/?token=?")
    request_text = request.text
    JSON = json.loads(request_text)

    filterJSON = {
        'time': str(JSON['data']['time']['s']),
        'co': str(JSON['data']['iaqi']['co']['v']),
        'h': str(JSON['data']['iaqi']['h']['v']),
        'no2': str(JSON['data']['iaqi']['no2']['v']),
        'o3': str(JSON['data']['iaqi']['o3']['v']),
        'p': str(JSON['data']['iaqi']['p']['v']),
        'pm10': str(JSON['data']['iaqi']['pm10']['v']),
        'pm25': str(JSON['data']['iaqi']['pm25']['v']),
        'so2': str(JSON['data']['iaqi']['so2']['v']),
        't': str(JSON['data']['iaqi']['t']['v']),
        'w': str(JSON['data']['iaqi']['w']['v']),
    }

    # Get a writer and append data to the file
    with pd.ExcelWriter(excel_file, mode="a") as xl:
        df = pd.DataFrame.from_dict(filterJSON)
        df.to_excel(xl)

if __name__ == "__main__":
    schedule.every().hour.do(write_to_excel)
    while True:
        schedule.run_pending()

NOTE: this code is not tested

$\endgroup$
9
  • $\begingroup$ Thank you very much! It just says "AttributeError: module 'schedule' has no attribute 'every'" so I cannot know if your code works :( $\endgroup$
    – Ghazal
    Apr 17 '20 at 15:30
  • $\begingroup$ Maybe you have installed something different to what I used as schedule. I am tlaking about this library: github.com/dbader/schedule - it definitely does offer what I suggested. $\endgroup$
    – n1k31t4
    Apr 17 '20 at 17:18
  • $\begingroup$ hello! I don't have the problem anymore but your code doesn't work :( it says : FileNotFoundError: [Errno 2] No such file or directory: 'air_quality.xlsx'. Maybe I should create it in the code first? $\endgroup$
    – Ghazal
    Apr 19 '20 at 11:28
  • $\begingroup$ Yes, good point. I had assumed you already had the file with some data in it. You could just check if it exists, if not, create it and write the data. mode = "a" if os.path.exists(excel_file) else "w", then pd.ExcelWriter(excel_file, mode=mode). $\endgroup$
    – n1k31t4
    Apr 19 '20 at 12:43
  • $\begingroup$ It worked! Thank you very much. Ps : do you think I can also use the "a+" mode? $\endgroup$
    – Ghazal
    Apr 20 '20 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.