Merging multiple, unaligned data-frames into single pandas data-frame

Question

I have multiple data-frames with stock prices that I want to align into a single data-frame that contains only the close prices for all stocks.

I would expect all dates from all data-frames to be present in the date-column(index) and "NA" in case there was no close price for a stock on that date.

Example with two data-frames (df1 and df2):

In [5]: df1
Out[5]:
            Open   High   Low    Close
Date1
2012-01-05  22.00  22.66  23.11  24.04
2012-01-04  24.04  23.80  23.08  22.16
2012-01-03  22.16  21.27  20.42  21.24
2012-01-01  21.24  22.30  22.52  22.30

In [7]: df2
Out[7]:
             Open   High    Low  Close
Date1
2012-01-07  23.00  21.66  25.11  21.04
2012-01-06  22.00  22.66  23.11  24.04
2012-01-04  24.04  23.80  23.08  22.16
2012-01-02  22.16  21.27  20.42  21.24
2012-01-01  21.24  22.30  22.52  22.30

Now I can do

In [8]: frame=pd.DataFrame({"df1.Close":df1["Close"], "df2.Close":df2["Close"]})

and the result is as expected:

In [9]: frame
Out[9]:
            df1.Close  df2.Close
Date1
2012-01-01      22.30      22.30
2012-01-02        NaN      21.24
2012-01-03      21.24        NaN
2012-01-04      22.16      22.16
2012-01-05      24.04        NaN
2012-01-06        NaN      24.04
2012-01-07        NaN      21.04

How would I need to change my code to do the same for a dynamic number of data-frames? Right now, I have 8 data-frames I need to align this way. Is there any way to loop thru a list of data-frames and align them like above - instead of manually tying the data-frame names (something like df[0] to df[7] figuratively speaking)?

Thanks in advance and kind regards! Dirk

Answer 1

If you have the data-frames in a list (the actual data-frame objects, I mean, not their names) that looks something like this:

dflist = [df1, df2, df3, df4, df5, df6, df7, df8]

then the following code should do what it seems you're looking for:

frame = {}
for idx, df in enumerate(dflist):
    n = idx+1  # Since lists are 0-indexed
    name = "df{0:d}.Close".format(n)
    close = df["Close"]
    frame[name] = close

You could do this more compactly with a dict comprehension, but in example code I prefer to spell things out more explicitly for ease of understanding. For reference, the dict comprehension would look something like this:

{"df{0:d}.Close".format(idx+1): df["Close"] for idx, df in enumerate(dflist)}