I am trying to get stock price from Tiingo and append dataframes

    data = pd.DataFrame()   

    lis=[ 
"AAPL",
"MSFT",
"AMZN",
"GOOGL",
"TSLA",
"GOOG",
"NVDA",
"FB",
"JPM",
"BAC",
"ADBE",
"MA",
"PFE",
"DIS",
"NFLX",
"INTC",
"VZ",
"MO"
]

for i in lis:
      data =data.append(client.get_dataframe([i],
                                      frequency='weekly',
                                      metric_name='close',
                                      startDate='2020-03-01',
                                      endDate='2021-12-10'))   

however, the result shows a bit different:

             AAPL  MFST
2020-03-01   100   NAN
2020-03-02   101   NAN
2020-03-03   103   NAN
...                NAN
2021-12-10   120   NAN

2020-03-01   NAN   600
2020-03-02   NAN   400
2020-03-03   NAN   300
...          NAN   
2021-12-10   NAN   1100

how can I make it look like this:

             AAPL  MFST
2020-03-01   100   600
2020-03-02   101   400
2020-03-03   103   300
...                
2021-12-10   120   1100

CodePudding user response：

Assuming you can't change the method to produce the initial input, you can fix it using groupby:

data = data.groupby(level=0).max()

CodePudding user response：

append method appends new rows. And that is exactly what you see as a result. You need to make join but not append.

try this:

new_data = client.get_dataframe([i],
                                      frequency='weekly',
                                      metric_name='close',
                                      startDate='2020-03-01',
                                      endDate='2021-12-10')
data.join(new_data)

docs for df.join() method are availuable here: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.join.html

CodePudding user response：

Instead of using pandas DataFrame.append method you could try pandas concatenate method.

import pandas as pd
dfs = []
for i in lis:
      dfs.append(client.get_dataframe([i],
                                      frequency='weekly',
                                      metric_name='close',
                                      startDate='2020-03-01',
                                      endDate='2021-12-10'))
data = pd.concat(dfs, axis=1) # set axis = 1 to concate by columns, not rows! 

Note, that this assumes your list of dfs has a) a common index and b) no extra columns besides say AAPL and MSFT.

If needed, you could also try merging:

from functools import reduce
data = reduce(lambda df1,df2: pd.merge(df1,df2,on='id'), dfs)

where id would be the column you that all dfs you pulled from client have in common. You may not need this since you have matching indexes and want to concatenate columns.