I am trying to merge two different csv files using Pandas but am running into errors while doing so.
The first file is aapl.csv, which looks like this:
Date Close High Low Open Volume
Symbol
AAPL 2017-05-25 153.87 154.3500 153.0300 153.7300 19235598
AAPL 2017-05-26 153.61 154.2400 153.3100 154.0000 21927637
The second file is corr_column.csv, which looks like this:
Corr
0.01
0.02
I want to merge them in a way that 'Corr' shows up as a column after 'Volume'.
I have tried using pd.concat, as provided in the documentation:
https://pandas.pydata.org/pandas-docs/stable/merging.html
This is my code:
import datetime as dt
import matplotlib.pyplot as plt
from matplotlib import style
import pandas as pd
pd.core.common.is_list_like = pd.api.types.is_list_like
import pandas_datareader.data as web
from mpl_finance import candlestick_ohlc
import matplotlib.dates as mdates
from matplotlib.dates import DateFormatter, MonthLocator, YearLocator, DayLocator
style.use( 'ggplot' )
##start = dt.datetime( 2017, 5, 29 )
##end = dt.datetime( 2018, 5, 29 )
##
##
##df = web.DataReader( AAPL, 'morningstar', start, end )
##
##df.to_csv( aapl.csv )
df = pd.read_csv( '/Users/zubairjohal/Documents/aapl.csv' , parse_dates=True, index_col=0 )
df_ohlc = df
corr_data = pd.read_csv( '/Users/zubairjohal/Documents/corr_column.csv', parse_dates=True, index_col=0 )
corr_data.dropna( inplace=True )
df.dropna( inplace=True )
merged = pd.concat( [ df, corr_data ], axis=1 )
merged.to_csv( 'combine2.csv', index=False )
print( merged )
However, while printing it, I am running into an error, as displayed below:
Traceback (most recent call last):
File "/Users/zubairjohal/Documents/nw5.py", line 34, in <module>
merged = pd.concat( [ df, corr_data ], axis=1 )
File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pandas/core/reshape/concat.py", line 226, in concat
return op.get_result()
File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pandas/core/reshape/concat.py", line 423, in get_result
copy=self.copy)
File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site- packages/pandas/core/internals.py", line 5425, in concatenate_block_managers
return BlockManager(blocks, axes)
File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pandas/core/internals.py", line 3282, in __init__
self._verify_integrity()
File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pandas/core/internals.py", line 3493, in _verify_integrity
construction_error(tot_items, block.shape[1:], self.axes)
File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pandas/core/internals.py", line 4843, in construction_error
passed, implied))
ValueError: Shape of passed values is (6, 68896), indices imply (6, 514)
Any suggestion, reference or alternative would be greatly appreciated.
You can try this :
pd.concat([df_ohlc.reset_index(), corr_data], axis=1).set_index("Symbol")
outputs :
Close Date High Low Open Volume Corr
Symbol
AAPL 153.87 2017-05-25 154.35 153.03 153.73 19235598.0 0.01
AAPL 153.61 2017-05-26 154.24 153.31 154.00 21927637.0 0.02
This works if your dataframes are in the way you printed them df_ohlc with AAPL as index and corr having no index.