Clever Geek Handbook

Concatenating two data frames by column and index

Hi, so I have two dataframes, the first one is a dataframe that was created by grouping another df by id (which is now an index) and then sorting by the "due" column.

df1:

         paid        due    
id          
3     13.000000     5.000000    
2     437.000000    5.000000    
5     90.000000     5.000000    
1     60.000000     5.000000    
4     675.000000    5.000000

The other is a normal framework with three columns: 'id', 'name' and 'country'.

df2:

id  name       country  
1   'AB'        'DE'
2   'CD'        'DE'
3   'EF'        'NL'
4   'HAH'       'SG'
5   'NOP'      'NOR'

So what I was trying to do was add the column "name" to the 1st dataframe based on the id number (which is the index in the first df and the column in the second). So I thought this code would work:

pd.merge(df1, df2['name'], left_index=True, right_on='id')

But I am getting error

ValueError: can not merge DataFrame with instance of type <class 'pandas.core.series.Series'>
0


source to share


3 answers


You can use rename

for a map with dict

:



df1['name'] = df1.rename(index=df2.set_index('id')['name']).index
print (df1)
     paid  due   name
id                   
3    13.0  5.0   'EF'
2   437.0  5.0   'CD'
5    90.0  5.0  'NOP'
1    60.0  5.0   'AB'
4   675.0  5.0  'HAH'
+1


source


You may find which pd.concat

is the best option here because it can accept a mix of data and series: http://pandas.pydata.org/pandas-docs/stable/merging.html#concatenating-with-mixed-ndims .



+1


source


So I figured out that I can't get one column of data like this, but I can remake df2 so that it only contains one required column:

df2=df2[['id', 'name']]
pd.merge(df1, df2, left_index=True, right_on='id')

And there is no more error.

0


source






More articles:


All Articles