merging two data frames

[yk303]

Hello,

I like to merge two dataframes. But the problem is that both tables have multiindexes.
When I try to merge, it can't find the common 'columns'. I just want it to merge on the basis of indexes (vertically).

I am pasting information about the two dataframes below plus the error.

thanks,

YK303

dfbuy=pd.DataFrame(Buy)
dfbuy.columns =['Buy' ]
dfbuy

Output:
	Buy
Year	
2012	7
2013	96
2014	98
2015	89
2016	73
2017	53
2018	55
2019	38
2020	47

dfbuy.columns

Output:
Index(['Buy'], dtype='object')

Sell= df1[df1.TRANSACTION_TYPE=='SELL'].groupby('Year').TRANSACTION_TYPE.count()
Sell

Output:
Year
2012     4
2013    94
2014    74
2015    79
2016    51
2017    25
2018    13
2019     2
2020    11
Name: TRANSACTION_TYPE, dtype: int64

dfsell.columns

Output:
Index(['Sell'], dtype='object')

newtable= pd.merge(dfbuy, dfsell)

Error:
---------------------------------------------------------------------------
MergeError                                Traceback (most recent call last)
<ipython-input-951-2c2a67ba86a8> in <module>
      1 #df_row = pd.concat([dfbuy, dfsell])
      2 #df_row
----> 3 newtable= pd.merge(dfbuy, dfsell)

~/opt/anaconda3/lib/python3.8/site-packages/pandas/core/reshape/merge.py in merge(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
     72     validate=None,
     73 ) -> "DataFrame":
---> 74     op = _MergeOperation(
     75         left,
     76         right,

~/opt/anaconda3/lib/python3.8/site-packages/pandas/core/reshape/merge.py in __init__(self, left, right, how, on, left_on, right_on, axis, left_index, right_index, sort, suffixes, copy, indicator, validate)
    643             warnings.warn(msg, UserWarning)
    644 
--> 645         self._validate_specification()
    646 
    647         # note this function has side effects

~/opt/anaconda3/lib/python3.8/site-packages/pandas/core/reshape/merge.py in _validate_specification(self)
   1212                 common_cols = self.left.columns.intersection(self.right.columns)
   1213                 if len(common_cols) == 0:
-> 1214                     raise MergeError(
   1215                         "No common columns to perform merge on. "
   1216                         f"Merge options: left_on={self.left_on}, "

MergeError: No common columns to perform merge on. Merge options: left_on=None, right_on=None, left_index=False, right_index=False

[rennerom]

I think the merge method expects a "type" of merge, or database style join, to be supplied. So it would always expect to know how like columns should be joined.

Alternatively, the concat method can concatenate objects along a particular axis which is what it sounds like you're trying to do? You'll need to explicitly provide the axis as columns by setting axis=1.

newtable= pd.concat([dfbuy], [dfsell],axis=1)