Fetch data from another dataframe

Question

I had asked a question earlier but it was closed as the initial question was considered as duplicate.

But I am trying to something different from that thread as well as duplicate thread.

String Replacement From Another DataFrame.

So please don't close this thread. Thanks.

I need to replace my column depending upon data from another dataframe. Have to search for respective "name" from the other dataframe and include the "change_name" in the df1

DataFrame 1:

ID  name
1   cat
2   jack
3   snake
4   monkey

DataFrame 2:

name    change_name
cat     meow
jack    oooo 
snake   ssss
monkey 
note    money
pencil pen

Expected Output:

ID  name
1   cat      meow
2   jack     oooo
3   snake    ssss
4   monkey   nan
5   note    money
6   pencil pen

I had to do like below:

def map_name(name):
    elif name == 'cat':
        return 'meow'
    elif name == 'jack':
        return 'oooo'
    elif name == 'snake':
        return 'ssss'
    elif name == 'monkey ':
        return None
    else
        return name

df1['name'] = df1['name'].apply(map_name)

As the list is small, I have hardcoded here but the list might grow. Can someone tell how to do the same functionality using dataframes? Thanks.

score 0 · Accepted Answer · answered Feb 19 '20 at 13:36

0

A simple merge would do it.
And then since you want the rows in df2 which are not in df1 concat them.

pd.concat([df1.merge(df2),df2[~df2['name'].isin(df1['name'])]]

Result:

In [23]: pd.concat([df1.merge(df2),df2[~df2['name'].isin(df1['name'])]])
Out[23]: 
    ID    name change_name
0  1.0     cat        meow
1  2.0    jack        oooo
2  3.0   snake        ssss
3  4.0  monkey        None
4  NaN    note       money
5  NaN  pencil         pen

answered Feb 19 '20 at 13:36

mechanical_meat

163,903
24
228
223

Thanks. I get the following error. `finalDF=pd.concat([df1.merge(df2),df2[~df2['name'].isin(df1['name'])]]` ```Sorting because non-concatenation axis is not aligned. A future version of pandas will change to not sort by default. To accept the future behavior, pass 'sort=False'. To retain the current behavior and silence the warning, pass 'sort=True'``` – Lilly Feb 19 '20 at 14:48
1

You're welcome. What version of pandas are you on? You should have 1.0.1. Check with `pd.__version__`... – mechanical_meat Feb 19 '20 at 14:49
`0.25.3` is the version. – Lilly Feb 19 '20 at 14:51
You should upgrade your pandas. Something like `pip install -U pandas`... – mechanical_meat Feb 19 '20 at 14:53
Yes, I upgraded pandas and it resolved the issue. But I am not able to see the contents when df2 doesn't have the 'names', essentially the last condition in my earlier code ```else return name``` – Lilly Feb 19 '20 at 15:14
Can you rephrase the concern? I'm not following. – mechanical_meat Feb 19 '20 at 15:16
```note money pencil pen ``` are not getting displayed in my final concatenated dataframe. Thanks. – Lilly Feb 20 '20 at 01:17
Could you share what command you're running and the result you're getting? Thanks. – mechanical_meat Feb 20 '20 at 03:09
Output is like tthe following........................... 0 1.0 cat meow 1 2.0 jack oooo 2 3.0 snake ssss 3 4.0 monkey None – Lilly Feb 20 '20 at 06:23
Commnadline ```finalDF=pd.concat([df1.merge(df2),df2[~df2['name'].isin(df1['name'])]]``` – Lilly Feb 20 '20 at 06:24

Fetch data from another dataframe

1 Answers1