Join two Pandas Dataframes

Question

We have two tables:

Table 1: EventLog

class EventLog(Base): """""" __tablename__ = 'event_logs' id = Column(Integer, primary_key=True, autoincrement=True) # Keys event_id = Column(Integer) data = Column(String) signature = Column(String) # Unique constraint __table_args__ = (UniqueConstraint('event_id', 'signature'),)

Table 2: Machine_Event_Logs

class Machine_Event_Logs(Base): """""" __tablename__ = 'machine_event_logs' id = Column(Integer, primary_key=True, autoincrement=True) # Keys machine_id = Column(String, ForeignKey("machines.id")) event_log_id = Column(String, ForeignKey("event_logs.id")) event_record_id = Column(Integer) time_created = Column(String) # Unique constraint __table_args__ = (UniqueConstraint('machine_id', 'event_log_id', 'event_record_id', 'time_created'),) # Relationships event_logs = relationship("EventLog")

The relationship between EventLogs and Machine_Event_Logs is 1 to many.

Whereby we register a unique event log into the EventLogs table and then register millions of entries into Machine_Event_Logs for every time we encounter that event.

Goal: We're trying to join both table to display the entire timeline of event logs captured.

We've tried multiple combinations of the merge() function in Panda Dataframe but it only returns a bunch of NaN or empty. For example:

pd.merge(event_logs, machine_event_logs, how='left', left_on='id', right_on='event_log_id')

Any ideas on how to solve this?

Thank in in advance for your assistance.

Parfait · Accepted Answer · 2016-11-23 22:12:57Z

According to your data schema, you have incompatible types where id in event_logs is an Integer and event_log_id in machine_event_logs is a String column. In Python the equality of a string and its equivalent numeric value yields false:

print('0'==0) # False

Therefore your pandas left join merge returns all NAN on right hand side since no matches are successfully found. Consider converting to align types for proper merging:

event_logs['id'] = event_logs['id'].astype(str)

OR

machine_event_logs['event_log_id'] = machine_event_logs['event_log_id'].astype(int)

Collectives™ on Stack Overflow

Join two Pandas Dataframes

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related