0

Below I have two dataframes, the first being dataframe det and the second being orig. I need to compare det['Detection'] with orig['Date/Time']. Once the values are found during the comparion, I need to copy values from orig and det to some final dataframe (final). The format that I need the final dataframe in is det['Date/Time'] orig['Lat'] orig['Lon'] orig['Dep'] det['Mag'] I hope that my formatting is adequate for folks. I was not sure how to handle the dataframes so I just placed them in tables. Some additional information that probably won't matter is that det is 3385 rows by 3 columns and orig is 818 rows by 9 columns.

det:

Date/Time Mag Detection
2008/12/27T01:06:56.37 0.280 2008/12/27T13:50:07.00
2008/12/27T01:17:39.39 0.485 2008/12/27T01:17:39.00
2008/12/27T01:33:23.00 -0.080 2008/12/27T01:17:39.00

orig:

Date/Time Lat Lon Dep Ml Mc N Dmin ehz
2008/12/27T01:17:39.00 44.5112 -110.3742 5.07 -9.99 0.51 5 6 3.2
2008/12/27T04:33:30.00 44.4985 -110.3750 4.24 -9.99 1.63 9 8 0.9
2008/12/27T05:38:22.00 44.4912 -110.3743 4.73 -9.99 0.37 8 8 0.8

final:

det['Date/Time'] orig['Lat'] orig['Lon'] orig['Dep'] det['Mag']
Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
nf95
  • 23
  • 4
  • 2
    seems like you wanna use inner merge. Check - https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.merge.html – Nk03 Jun 01 '21 at 20:11
  • 2
    I agree with @Nk03 something like `final = det.merge(orig[['Date/Time', 'Lat', 'Lon']], on='Date/Time')` see also [Pandas Merging 101](https://stackoverflow.com/q/53645882/15497888) – Henry Ecker Jun 01 '21 at 20:12
  • I had almost solved it using the inner merge method but it was missing a lot of the rows. I appreciate the input! @Don'tAccept was able to solve this problem. Thanks everyone! – nf95 Jun 01 '21 at 20:21

1 Answers1

1

You can merge the two dataframes, since you want to use Detection column from the first data frame and Date/Time column from the second dataframe, you can just rename the column of second dataframe while merging since the column name already exits in the first dataframe:

det.merge(org.rename(columns={'Date/Time': 'Detection'}))

OUTPUT:

                Date/Time    Mag               Detection      Lat       Lon   Dep    Ml    Mc  N  Dmin  ehz
0  2008/12/27T01:17:39.39  0.485  2008/12/27T01:17:39.00  44.5112 -110.3742  5.07 -9.99  0.51  5     6  3.2
1  2008/12/27T01:33:23.00 -0.080  2008/12/27T01:17:39.00  44.5112 -110.3742  5.07 -9.99  0.51  5     6  3.2

You can then select the columns you want.

ThePyGuy
  • 17,779
  • 5
  • 18
  • 45