Creating a Dataframe from an appended list (with datetime)

February 23, 2022

Due to API restraints I am sending a long list of Instruments to an API in batches and the appending the results to a list. When I only use the API call, the output is a dataframe I can work with. However when I try to split into batches and the append my outputs, I can’t figure out how to make it a workable dataframe.

The API Function (Pricegetter), when used by itself produces a dataframe like this:

Output of Pricegetter function

When used as a function in the following code, the output is a list (shown below code):

dict_list = ric_lists #ric_lists is list of ALL RICs
batch_list = []
return_list = []

for i in dict_list:
    batch_list.append(i)
    if len(batch_list)  == 5:
        return_list.append(Pricegetter(batch_list))
        batch_list.clear()

if batch_list:
    return_list.append(Pricegetter(batch_list))

THe output list:

[              KAER.VI
 2011-01-04  18.056605
 2011-01-05  18.056605
 2011-01-11  18.253407
 2011-01-12  18.253407
 2011-01-18  18.253407
 ...               ...
 2021-12-23  14.900000
 2021-12-27  15.100000
 2021-12-28  15.100000
 2021-12-29  15.200000
 2021-12-30  15.300000
 
 [1794 rows x 1 columns],
             BHAV.VI
 2011-01-03    43.05
 2011-01-07    43.01
 2011-01-11    43.00
 2011-01-19    43.05
 2011-01-20    43.05
 ...             ...
 2021-12-17    95.00
 2021-12-20    98.00
 2021-12-21    98.00
 2021-12-23    98.50
 2021-12-27    98.50
 
 [918 rows x 1 columns],
....]

How do I transform this to a dataframe with dates as the index and the instrument names as column headers like in the example picture?

>Solution :

Try:

>>> pd.concat(return_list).sort_index()
              KAER.VI  DUEG.DE
2011-01-03        NaN   5.9250
2011-01-04  18.056605      NaN
2011-01-04        NaN   6.0600
2011-01-05  18.056605      NaN
2011-01-05        NaN   6.1400
2011-01-06        NaN   6.0650
2011-01-07        NaN   6.1375
2011-01-11  18.253407      NaN
2011-01-12  18.253407      NaN
2011-01-18  18.253407      NaN