Pandas: Join dataframe with condition

by

Try the following:

# Transform data in first dataframe
df1 = pd.DataFrame(data)

# Save the data in another datframe
df2 = pd.DataFrame(data)

# Rename column names of second dataframe 
df2.rename(index=str, columns={'Reader_ID1': 'Reader_ID1_x', 'SITE_ID1': 'SITE_ID1_x', 'EVENT_TS1': 'EVENT_TS1_x'}, inplace=True)

# Merge the dataframes into another dataframe based on PERSONID and Badge_ID
df3 = pd.merge(df1, df2, how='outer', on=['PERSONID', 'Badge_ID'])

# Use df.loc() to fetch the data you want
df3.loc[(df3.Reader_ID1 < df3.Reader_ID1_x) & (df3.SITE_ID1 != df3.SITE_ID1_x) & (pd.to_datetime(df3['EVENT_TS1']) - pd.to_datetime(df3['EVENT_TS1_x'])<=datetime.timedelta(hours=event_time_diff))]

More Related Contents:

  1. How to do/workaround a conditional join in python Pandas?
  2. What is the difference between join and merge in Pandas?
  3. How to do “(df1 & not df2)” dataframe merge in pandas?
  4. How do I assign values based on multiple conditions for existing columns?
  5. How to drop rows of Pandas DataFrame whose value in a certain column is NaN
  6. How to make separator in pandas read_csv more flexible wrt whitespace, for irregular separators?
  7. Why isn’t my Pandas ‘apply’ function referencing multiple columns working? [closed]
  8. Convert the string 2.90K to 2900 or 5.2M to 5200000 in pandas dataframe
  9. Conditionally fill column values based on another columns value in pandas
  10. Drop rows containing empty cells from a pandas DataFrame
  11. pandas concat generates nan values
  12. Remove duplicate rows from Pandas dataframe where only some columns have the same value
  13. Normalize columns of a dataframe
  14. Pandas selecting by label sometimes return Series, sometimes returns DataFrame
  15. How to switch columns rows in a pandas dataframe
  16. Pandas Groupby and Sum Only One Column
  17. Quickest way to make a get_dummies type dataframe from a column with a multiple of strings
  18. Pandas groupby.size vs series.value_counts vs collections.Counter with multiple series
  19. “unstack” a pandas column containing lists into multiple rows [duplicate]
  20. How to check whether a pandas DataFrame is empty?
  21. Python pandas Filtering out nan from a data selection of a column of strings
  22. Copy pandas dataframe to excel using openpyxl
  23. How to drop unique rows in a pandas dataframe?
  24. pandas – find first occurrence
  25. How to convert a dataframe to a dictionary
  26. Read CSV into a dataFrame with varying row lengths using Pandas
  27. Filling missing values using forward and backward fill in pandas dataframe (ffill and bfill)
  28. Truncate `TimeStamp` column to hour precision in pandas `DataFrame`
  29. Remove row with null value from pandas data frame
  30. How to “select distinct” across multiple data frame columns in pandas?

Leave a Comment