I'm looking for an optimum way to join two Dataframes where the same record exists in both. Examples would be:
dat1 = pd.DataFrame({"id": [1,2,3,4,5], "dat1": [34,56,57,45,23]})
dat2 = pd.DataFrame({"id": [2,3,4], "dat2": [19,19,20]})
dat1
id dat1
0 1 34
1 2 56
2 3 57
3 4 45
4 5 23
dat2
id dat2
0 2 19
1 3 19
2 4 20
With the aim being to create overall_data:
overall_data
id dat1 dat2
0 2 56 19
1 3 57 19
2 4 45 20
At the moment my method is:
dat3 = dat1['id'].isin(dat2['id'])
dat3 = pd.Dataframe(dat3)
dat3.columns = ['bool']
dat4 = dat1.join(dat3)
overall_data = dat4(dat4['bool'] == True)
Though this feels very messy. Is there a nicer way to do this?