I have a dataset that shows which community belongs to parent community. It looks like this:
| COMMUNITY | PARENT COMMUNITY |
|---|---|
| Community 1 | Community A |
| Community 2 | Community A |
| Community 3 | NaN |
| Community 4 | Community B |
| Community 5 | Community C |
I have a second dataset with raw values, that looks like this:
| ID | COMMUNITY |
|---|---|
| 1 | Community 4 |
| 2 | Community 5 |
| 3 | Community 1 |
| 4 | Community 1 |
| 5 | Community 1 |
| 6 | Community 3 |
| 7 | Community 7 |
I need to transform second dataset using first dataset, so in the end it looks like this:
| ID | COMMUNITY | PARENT COMMUNITY |
|---|---|---|
| 1 | Community 4 | Community B |
| 2 | Community 5 | Community C |
| 3 | Community 1 | Community A |
| 4 | Community 1 | Community A |
| 5 | Community 1 | Community A |
| 6 | Community 3 | Community 3 |
| 7 | Community 7 | NaN |
Also, Please pay attention to PARENT COMMUNITY for Community 3 and 7.
CodePudding user response：
using pandas you can merge the two dataframes like so:
df3 = df2.merge(df1, on='COMMUNITY', how='left')
If you want to read more you can check out the documentaion