Creating a Dataframe from an appended list (with datetime)

Question

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?

pd.concat gives me the right format, but only the first 5 prices of the first instrument and last five prices of the last instrument are corrctly inserted, all other fields are populated with NaN — FinQbeginner, Feb 23 '22 at 14:14
Can you provide the output of the following statements: `return_list[0].head().to_dict()` and `return_list[-1].head().to_dict()`? — not_speshal, Feb 23 '22 at 14:17
`{'KAER.VI': {Timestamp('2011-01-04 00:00:00'): 18.056605, Timestamp('2011-01-05 00:00:00'): 18.056605, Timestamp('2011-01-11 00:00:00'): 18.253407, Timestamp('2011-01-12 00:00:00'): 18.253407, Timestamp('2011-01-18 00:00:00'): 18.253407}}` and: `{'DUEG.DE': {Timestamp('2011-01-03 00:00:00'): 5.925, Timestamp('2011-01-04 00:00:00'): 6.06, Timestamp('2011-01-05 00:00:00'): 6.14, Timestamp('2011-01-06 00:00:00'): 6.065, Timestamp('2011-01-07 00:00:00'): 6.1375}}` — FinQbeginner, Feb 23 '22 at 14:20

score 0 · Accepted Answer · answered Feb 23 '22 at 14:29

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

Creating a Dataframe from an appended list (with datetime)

1 Answers1