Merging mutliple pandas dfs time series on DATE index and which are contained in a python dictionary
<p>I have a python dictionary that contains CLOSE prices for several stocks, stock indices, fixed income instruments and currencies (AAPL, AORD, etc.), using a DATE index. The different DFs in the dictionary have different lengths, i.e. some time series are longer than others. All the DFs have the same field, ie. 'CLOSE'.</p>

<p>The length of the dictionary is variable. How can I merge all the DFs into a single one, by DATE index, and also using lsuffix = partial name and feature of the the file I am reading? (for example, the AAPL_CLOSE.csv file has a DATE &amp; a CLOSE field, but to differentiate from the other 'CLOSE' in the merged DF, its name should be AAPL_CLOSE)</p>

<p>This is what I have:</p>

<pre><code>asset_name = []
files_to_test = glob.glob('*_CLOSE*')
for name in files_to_test:
    asset_name.append(name.rsplit('_', 1)[0])
</code></pre>

<p>Which returns:</p>

<pre><code>asset_name = ['AAPL', 'AORD', 'EURODOLLAR1MONTH', 'NGETF', 'USDBRL']
files_to_test = ['AAPL_CLOSE.csv',
 'AORD_CLOSE.csv',
 'EURODOLLAR1MONTH_CLOSE.csv',
 'NGETF_CLOSE.csv',
 'USDBRL_CLOSE.csv']
</code></pre>

<p>Then:</p>

<pre><code>asset_dict = {}
for name, file in zip(asset_name, files_to_test):
    asset_dict[name] = pd.read_csv(file, index_col = 'DATE', parse_dates = True)
</code></pre>

<p>This is the little function I would like to generalize, to create a big merge of all the DFs in the dictionary by DATE, using lsuffix = the elements in asset_list. </p>

<pre><code>merged = asset_dict['AAPL'].join(asset_dict['AORD'], how = 'right', lsuffix ='_AAPL')
</code></pre>

<p>The DFs will have a lot of N/A due to the mismatch of lengths, but I will deal with that later.</p>

<p>After not getting any answers, I found a solution that works, although there might be better ones. This is what I did:</p>

<pre><code>asset_dict = {}
for name, file in zip(asset_name, files_to_test):
    asset_dict[name] = pd.read_csv(file, index_col='DATE', parse_dates=True)
    asset_dict[name].sort_index(ascending = True, inplace = True)
</code></pre>

<p>Pandas can <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.concat.html" rel="nofollow">concatenate</a> multilple dfs (at once, not one by one) contained in dictionaries, 'straight out of the box' without much tweaking, by specifying the axis and other parameters.</p>

<pre><code>df = pd.concat(asset_dict, axis = 1)
</code></pre>

<p>The resulting df is a multi-index df, which is a problem for me. Also, the time series for stock prices are all of different lengths, which creates a lot of NaNs. I solved bot problems with this:</p>

<pre><code>df.columns = df.columns.droplevel(1)
df.dropna(inplace = True)
</code></pre>

<p>Now, the columns of my df are these:</p>

<pre><code>['AAPL', 'AORD', 'EURODOLLAR1MONTH', 'NGETF', 'USDBRL']
</code></pre>

<p>But since I wanted them to contain the 'STOCK_CLOSE' format, I do this: </p>

<pre><code>old_columns = df.columns.tolist()
new_columns = []
for name in old_columns:
    new_name = name + '_CLOSE_'
    new_columns.append(new_name)
</code></pre>

python pandas dictionary