CODEWITHSUNDEEP
pythonpandasdataframe
nrvaller
nrvaller

Reputation: 373

Pandas merging 2 dataframes on their similar columns(which is the index)

I have two dataframes, which in both I happened to set 'timeStamp' as the index. df_1.set_index('timeStamp', inplace=True).

df_1

                     value
timeStamp                 
2016-11-23 20:00:00  37.21
2016-11-23 21:00:00  37.79
2016-11-23 22:00:00  33.99
2016-11-23 23:00:00  32.66
2016-11-24 00:00:00  31.61

df_2

                     value
timeStamp                 
2016-11-23 23:00:00  32.92
2016-11-24 00:00:00  31.54
2016-11-24 01:00:00  29.14

I wanted to make a dataframe comparing both values when the time is shared. I tried combined_df= pd.merge(df_real, df_fc, on='timeStamp', how='inner') and got a key error.

So instead of merging two dataframes on an index, I kept the dataframes without 'timeStamp' as their index. For example.

df I used instead for merging

             timeStamp  value
0  2016-11-23 20:00:00  37.21
1  2016-11-23 21:00:00  37.79
2  2016-11-23 22:00:00  33.99
3  2016-11-23 23:00:00  32.66
13 2016-11-24 00:00:00  31.61

Then I was able to merge and my new df was set(shown below). I also then set the index to timestamp, later on.

            timeStamp  value_x  value_y 
0  2016-11-23 23:00:00    32.66    32.92

my question Why couldn't I merge on the column name that was specified as an index? I wanted to set that merge to a new dataframe...

Upvotes: 2

Views: 452

Answers (4)

letmecheck
letmecheck

Reputation: 1369

try this:

df_real.merge(df_fc, on='timeStamp', how='inner')

test code:

import pandas as pd
d = {'time1': ['A', 'B'], 'val1': [2, 4]}
df = pd.DataFrame(data=d)
df.set_index('time1')

d1 = {'time1': ['A', 'B','C'], 'val1': [5, 6, 9]}
df2 = pd.DataFrame(data=d1)
df2.set_index('time1')

df.merge(df2, on = 'time1')

output is:

    time1   val1_x  val1_y
  0    A       2      5
  1    B       4      6

Upvotes: 0

Sankar guru
Sankar guru

Reputation: 1015

DataFrame joining/merging

pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None,
         left_index=False, right_index=False, sort=True,
         suffixes=('_x', '_y'), copy=True, indicator=False,
         validate=None)

https://pandas.pydata.org/pandas-docs/stable/merging.html

Upvotes: 1

zipa
zipa

Reputation: 27869

You need to indicate you are merging on index:

pd.merge(df_1, df_2, left_index=True, right_index=True, how='inner')

Upvotes: 1

PyPingu
PyPingu

Reputation: 1747

I believe that you CAN merge on an index. You just seem to have used the wrong syntax. Instead of specifying on you should try using left_index and right_index.

See the documentation for merges here

Upvotes: 1

Related Questions