1

I have a function thats collects data from a sandbox api, and writes this data into a ecxel sheet using pandas and xlsxwriter combinded. My question is how I can get Python to write the new data into a new row every time I run the program? Now it's just overwriting the old data every time i run the program over again.

This is my code:

import pandas as pd
import xlsxwriter
import requests

def collection():
api_url = f'https://sandbox.millistream.com/mws.fcgi?usr=sandbox&pwd=sandbox&cmd=quote&list=39970\
&fields=symbol,name,diff1dprc,lastprice,time&orderby=symbol&order=asc&timezone=Europe/Oslo&filetype=json'
data = requests.get(api_url).json()

my_columns = ['Time', 'Stocks price']
final_dataframe = pd.DataFrame(columns=my_columns)

final_dataframe = final_dataframe.append(
    pd.Series(
        [
            data[2]['time'],
            data[2]['lastprice']
        ],
        index=my_columns),
    ignore_index=True
)

writer = pd.ExcelWriter('Testing.xlsx', engine='xlsxwriter')
final_dataframe.to_excel(writer, 'AKER', index=False)

background_color = '#000000'
font_color = '#E8130F'

string_format = writer.book.add_format(
{
'font_color': font_color,
'bg_color': background_color,
'border': 1
}
)
dollar_format = writer.book.add_format(
{
'num_format': '$0.00',
'font_color': font_color,
'bg_color': background_color,
'border': 1
}
)

writer.sheets['AKER'].set_column('A1:A506', 20, string_format)
writer.sheets['AKER'].set_column('B1:B506', 10, dollar_format)
writer.save()

This is the return in Excel: Output

And this is how I want the return to look: Expected output

ObjectJosh
  • 601
  • 3
  • 14
eriksen1110
  • 63
  • 7
  • Does [this](https://stackoverflow.com/a/54186803/15497888) answer your question? – Henry Ecker Apr 01 '21 at 04:24
  • Not exactly. The main problem is when I run the program over again, the new data just owerwrites the olds one. So I have to find a way that I can save the old data, and add the new data to a new row. – eriksen1110 Apr 01 '21 at 07:46

1 Answers1

2

You are overwriting the data because every time you run the script you are creating a new empty dataframe and you append the data to that. What you need to do is to firstly load the existing file, append the data to that, save it and repeat.

So in your code replace this 11th line

final_dataframe = pd.DataFrame(columns=my_columns)

with

final_dataframe = pd.read_excel('Testing.xlsx')

I run the program a couple of times and got your desired output:

enter image description here

Finally you are formatting column A all the way down until the maximum row. Not such a big problem but there are more elegant ways to only format the rows that contain data.

Dimitris Thomas
  • 1,363
  • 9
  • 14
  • Thank you! How will you recomand me to format only the rows that contain data? – eriksen1110 Apr 10 '21 at 12:15
  • For example, for the first column replace line 45 writer.sheets['AKER'].set_column('A1:A506', 20, string_format) with workbook = writer.book worksheet = writer.sheets['AKER'] col = 0 for row in range(0, final_dataframe.shape[0]): worksheet.write(row+1, col, final_dataframe.iloc[row, col], string_format) – Dimitris Thomas Apr 10 '21 at 12:43