How to join and apply within the same dataframe?

user_10072838 0
Viewed 77

I have a dataframe which looks like this:

|--------|---------|----------------|
|   ID   |  Value  |  AssociatedID  |
|--------|---------|----------------|
|    1   |   Val 1 |      3         |
|--------|---------|----------------|
|    2   |   Val 2 |      4         |
|--------|---------|----------------|
|    3   |         |                |
|--------|---------|----------------|
|    4   |         |                |
|--------|---------|----------------|

The Output that I want is

|--------|---------|----------------|
|   ID   |  Value  |  AssociatedID  |
|--------|---------|----------------|
|    1   |   Val 1 |      3         |
|--------|---------|----------------|
|    2   |   Val 2 |      4         |
|--------|---------|----------------|
|    3   |   Val 1 |                |
|--------|---------|----------------|
|    4   |   Val 2 |                |
|--------|---------|----------------|

So Basically I want to do a join between the Associated ID and ID field and apply the Value.

The basic way in which I could do this is

for id in df['AssociatedID'].unique.tolist():
    valueToApply = df.loc[df['AssociatedID'] == id]["ID"].values
    df.loc[(df['ID'] == id) , "Value"] = valueToApply

I was hoping I could use some sort of pandas one liner to achieve this. What is the best way to do it with pandas? any one line code?

pandas dataframe join python
1 Answers

We can do np.where with map

df['Value'] = np.where(df['Value'].isnull(), 
                       df['ID'].map(dict(zip(df['AssociatedID'],df['Value']))), 
                       df['Value'])
user_79645270
Related