I have the following 2 Pandas Dataframes where numberin df2 is contained in a row filename's column (it's a 1 to 1 relationship). I'd like to get the associated type value and add it to df1 if there is a match between numbers.
df1:
| id | filename | whatevercolumn |
|---|---|---|
| 1 | Source _ABC 11526 | 1 |
| 2 | ABC 11386-3 089 | 10 |
| 3 | ABC 070 8731 | 30 |
df2
| id | number | type |
|---|---|---|
| 1 | 11386 | A |
| 2 | 11526 | B |
And would like to merge them in a way to get the following df.
| id | filename | whatevercolumn | type |
|---|---|---|---|
| 1 | Source _ABC 11526 | 1 | B |
| 2 | ABC 11386-3 089 | 10 | A |
| 3 | ABC 070 8731 | 30 | None |
What I've tried : I've managed to do it by transforming df into lists and dict and using loops/ifs. But I'm sure a better method can be applied (both resource-wise and easy-to-read code). I've looked around masks and assignment but couldn't manage to find the right way to do so.
Have a nice day
