Concat data from dictionary based on date

Question

So I have a dictionary with data frames: For example df[0]=

    Date         StockPrice
    01/01/2020   34.2
    01/02/2020   35
    01/03/2020   36
      ...

df[1]=

    Date        Rate    
   01/01/2020   4
   01/02/2020   5
   01/03/2020   5
   ...

df[2]=

   Date       High   Low 
   01/01/2020   45    42
   01/03/2020   45    40
   ...

df[3]=

Date         Rate2
01/01/2020    5 
01/03/2020    6

I want to merge all these dataframes on the date so it would look like this:

newdateframe=

Date          StockPrice     Rate    High   Low    Rate 2
01/02/2020     34.2          4       45     42     5
01/02/2020     35            5       NA     NA     NA
01/03/2020     36            5       45     40     6

I tried to do this: pd.concat(df.values(), ignore_index=True) but that doesn't work because it just puts each dataframe side by side and doesn't even capture all my dates. My dictionary has over 10 dataframes so I dont want to do a merge one by one I'd rather do something similar to the above code or a loop.

Quang Hoang · Accepted Answer · 2020-12-15T16:44:11.240

0

Try:

pd.concat([d.set_index('Date') for d in df.values()], axis=1)

Output:

            StockPrice  Rate  High   Low  Rate2
01/01/2020        34.2     4  45.0  42.0    5.0
01/02/2020        35.0     5   NaN   NaN    NaN
01/03/2020        36.0     5  45.0  40.0    6.0

edited Dec 15 '20 at 16:44

answered Dec 15 '20 at 16:40

Quang Hoang

146,074
10
56
74

02/02/2016 goes to -01/02/2020 then starts at 2016 again – Michelle M Dec 15 '20 at 16:43
It works on the sample data, see the included output. What do you mean it doesn't capture all data? – Quang Hoang Dec 15 '20 at 16:45
is there a way to organize it so it captures the dates in order – Michelle M Dec 15 '20 at 16:46
and *dates in order* means? – Quang Hoang Dec 15 '20 at 16:48
So the output for me is for example it goes from 01/01/2020 to 06/04/2020 then starts again at 01/01/2020 for some of the data, do you know why it is doing that – Michelle M Dec 15 '20 at 16:49
Did you forget `axis=1`? – Quang Hoang Dec 15 '20 at 16:52
I included it, it's very weird because its only for some of the data – Michelle M Dec 15 '20 at 16:56
Should I loop through each dataframe and make sure the dates are all in the same format? – Michelle M Dec 15 '20 at 16:57
Gotta check for leading/trailing spaces in your `Date` columns. Otherwise it should not be doing that. – Quang Hoang Dec 15 '20 at 16:57

Concat data from dictionary based on date

1 Answers1