0

I need to extract the same column from multiple DataFrames in a Dictionary and save them as a DataFrame so that each column name is labelled as the key in the Dictionary.

By extracting the 'close' column from each DataFrame in the Dictionary I end up with the Series below:

date close
2023-03-06 04:00:00 1
2023-03-06 05:00:00 2
2023-03-06 06:00:00 2

And I need to make a DataFrame with n columns that looks like this:

date SPY QQQ SMH
2023-03-06 04:00:00 1 3 2
2023-03-06 05:00:00 2 4 4
2023-03-06 06:00:00 3 2 3

The number of tickers varies.

Andy Visser
  • 95
  • 1
  • 11

1 Answers1

0

Let's say the dictionary you have created is called d, the corresponding empty data frame is called final_df and one of your dataframes with the data is called spy_df

You can simply loop through the dictionary like this:

for idx, val in d.items():
    final_df[idx] = val['close'] #idx would be the key value in the dictionary 

final_df.index = spy_df.index
P. Shroff
  • 396
  • 3
  • 5
  • for idx, val in data.items(): final_df[idx] = val['close'] #idx would be the key value in the dictionary – Andy Visser Mar 10 '23 at 02:44
  • ValueError: cannot reindex on an axis with duplicate labels – Andy Visser Mar 10 '23 at 02:45
  • This is the same error I get for every way I've tried. I've tried pd.concat, pd.join, pd.merge, etc. It's been almost 12 hours now, no idea where I'm going wrong here, – Andy Visser Mar 10 '23 at 02:47
  • 1
    @AndyVisser Then please provide a a [MRE](https://stackoverflow.com/help/minimal-reproducible-example) (also look [here](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples)) that replicates your problem. Otherwise it's just guessing around. – Timus Mar 10 '23 at 08:02