1

I want to request some data from a python module tushare. By using this code, I can each time get a line of data. However I want to send the server a request for like every 5 seconds and put all the data within 4 hrs into one excel file. I notice that pandas is already built in tushare. How to put the data together and generate only one excel file?

    import tushare as ts
    df=ts.get_realtime_quotes('000875')

    df.to_excel(r'C:\Users\stockfile\000875.xlsx')
Qing
  • 17
  • 4

1 Answers1

0

You can do it with for example

df = df.append(ts.get_realtime_quotes('000875'))

Given the number of calls, it nay be better to create a data frame and fill it with data rows as they arrive. Something like this:

# Here, just getting column names:
columns = ts.get_realtime_quotes('000875').columns
# Choose the right number of calls, N,
df = pd.DataFrame(index = range(N), columns = columns)
for i in range(N):
    df.iloc[0] = ts.get_realtime_quotes('000875').iloc[0]
    sleep(5)

Another way to do it (possibly simpler and without preallocating the empty data frame) would be storing answers from tushare in a list and then applying pd.concat.

list_of_dfs = []
for _ in range(N):
    list_of_dfs.append(ts.get_realtime_quotes('000875'))
    sleep(5)
full_df = pd.concat(list_of_dfs)

This way you don't need to know the number of requests in advance (for example, if you decide to write the for loop without explicit number of repetitions).

ptrj
  • 5,152
  • 18
  • 31
  • thank you for your reply. I might ask a stupid question..what do you mean by number of calls?? – Qing Apr 18 '16 at 18:08
  • it returns one row of data each time, and this row contains 34 columns – Qing Apr 18 '16 at 18:12
  • @Qing If you're going to be sending requests every 5 seconds for 4 hours, then it will be N = 4*60*60/5 requests (or calls). So the code first preallocates the data frame of that size and then fills the rows. – ptrj Apr 18 '16 at 19:14
  • @Qing I added another solution. – ptrj Apr 18 '16 at 19:26
  • thank you so much! it works for me. btw do you know a way to open the excel file and write without overlapping previous results? – Qing Apr 19 '16 at 03:12
  • I think the second solution is more achievable for me. easily understanding! – Qing Apr 19 '16 at 03:17
  • Glad I could help. As for appending to an excel file, a safe solution is to read the content to a data frame first, append to it the other data frame, and finally save it back to file. According to [this](http://stackoverflow.com/a/20221655/5717589) you can append data to separate sheets. – ptrj Apr 19 '16 at 03:59