Pandas merge displaying columns from single dataframe

Question

I'm joining two dataframes using merge in pandas, but the result contains columns from single dataframe. Here is the piece of code:

df1
date(index) | Total | Unique

df2
date(index) | Total | Unique

result = pd.merge(df1, df2, how="inner")
Output:
Total | Unqiue

Expected Output:
df1_Total | df2_Unique | df2_total |  df2_unique

I've also tried suffixes, but they doesn't work as well.

What is your join key? `pd.merge` is joining `on` the following criteria if not stated: If on is None and not merging on indexes then this defaults to the intersection of the columns in both DataFrames. — ycx, Mar 07 '19 at 07:28

score 2 · Answer 1 · answered Mar 07 '19 at 07:30

2

result = pd.merge(df1, df2, how="inner", on='date(index)')

This will join on date(index). Change your join key as necessary to what you require

answered Mar 07 '19 at 07:30

ycx

3,155
3
14
26

jezrael · Answer 2 · 2019-03-07T07:37:00.923

It is possible by DataFrame.add_prefix and left_on and right_on parameetrs, but get same values in both columns, because default inner join:

df1 = pd.DataFrame([(10,2),(20,4),(30,6)], 
                   columns=['Total','Unique'], 
                   index=pd.date_range('2015-01-01', periods=3))
df2 = pd.DataFrame([(10,2),(30,6),(20,600)], 
                    columns=['Total','Unique'], 
                    index=pd.date_range('2015-01-02', periods=3))

#print (df1)
#print (df2)

result = pd.merge(df1.add_prefix('df1_'), 
                  df2.add_prefix('df2_'),
                  left_on=['df1_Total','df1_Unique'],
                  right_on=['df2_Total','df2_Unique'])
print (result)
   df1_Total  df1_Unique  df2_Total  df2_Unique
0         10           2         10           2
1         30           6         30           6

If want join by index values is necessary use left_index and right_index with suffixes:

result = pd.merge(df1, 
                  df2,
                  left_index=True,
                  right_index=True,
                  suffixes=('_df1','_df2'))
print (result)
            Total_df1  Unique_df1  Total_df2  Unique_df2
2015-01-02         20           4         10           2
2015-01-03         30           6         30           6

score 0 · Answer 3 · answered Mar 07 '19 at 07:32

0

Specify merge function parameter on as 'Date' column

pd.merge(df1, df2, how="inner", on="Date")

answered Mar 07 '19 at 07:32

windstorm

375
2
10

Pandas merge displaying columns from single dataframe

3 Answers3