0

Not sure why it's not showing tables for df1 and df2 in main post. I am able to see it in preview.

I have two data frames df1 and df2. df1 has one column (row_no) and a DateTime index with freq = 'S'. df2 has normal indexing and has two columns (row_no, readings). Both data frames have the same number of rows. I want to join then using merge/join (like SQL) where row_no is the matching column, but I want to keep the DateTime index of df1 after merging.

df1:

row_no
2022-07-01 00:00:00 0
2022-07-01 00:00:01 1
2022-07-01 00:00:02 2

df2:

row_no readings
0 0 0.1534
1 1 0.3091
2 2 2.3042

Expected Output:

row_no readings
2022-07-01 00:00:00 0 0.1534
2022-07-01 00:00:01 1 0.3091
2022-07-01 00:00:02 2 2.3042

Currently, merging the two data frames is creating a new index, which I don't want.

Any suggestions would be really helpful.

Screenshot of data frames

furas
  • 134,197
  • 12
  • 106
  • 148
DevrajR
  • 1
  • 1
  • what did you try? pandas has `.join()`, `.merge()`, etc. which can use `on=` like in SQL. You could show your code with example data in code. – furas Aug 02 '22 at 08:07
  • I was trying join() and merge() both, but had issues with the Datetime index column in df2. The solution I got was using this code: df2.merge(df1, left_index=True, right_on='row_no') – DevrajR Aug 03 '22 at 07:48

1 Answers1

1

Use merge function along with index reset and set after matching your column(s).

pd.merge(df1.reset_index(), df2, left_on='row_no', right_on='row_no').set_index('index')

or

df2.merge(df1.reset_index(), on='row_no').set_index('index')

After resetting df1:

enter image description here

After merging df1 and df2 with 'row_no' column:

enter image description here

After setting the index:

enter image description here

Sai_MSP
  • 129
  • 4
  • 1
    Thanks for the feedback. I did find another solution which was: df2.merge(df1, left_index=True, right_on='row_no') I did try this before, where reset_index was used. But when setting back the DateTime index on new df, the Freq value is set to None. I need the "Freq" value to be 'S'. The above solution I tried worked. – DevrajR Aug 03 '22 at 07:43