Merging columns in dataframe pandas

Question

I have 2 dataframes :

df1   folder_name   name
    0   a1          aa
    1   b1          bb


df2    name        icon
    0   aa          aa
    1   bb          bb
    3   aadoq       aadoq
    4   bbaddd      bbaddd

Now i want to match the pattern of column name and then merge

desired output:

df   folder_name    name    icon
    0   a1          aa       aa
    1   b1          bb       bb
    3   a1         aadoq    aadoq
    4   b1         bbaddd   bbaddd

actual output:

df   folder_name    name    icon
        0   a1          aa       aa
        1   b1          bb       bb
        3   NAN         aadoq    aadoq
        4   NAN         bbaddd   bbaddd

So if name column of df1 matches name column of df2 then also my merge should work . Is there any way for getting this

I tried doing pd.merge(df1,df2,on='name',how='right') but it gives me NAN in icon where name is aadoq and bbaddd . I want if name is aadoq which contains aa so it should pick aa as icon

You could merge and drop duplicates? You just need to marry up the column names — Umar.H, Dec 19 '18 at 14:48
my requirement is like if aadoq matches withh aa so icon should not be NAN — ayushgupta, Dec 19 '18 at 14:54
How do you know that `aadoq` has `folder_name` `aaa`? Or that `bbaddd` has `folder_name` `bbb`? The `name` implies `folder_name` just based off the first two letters? Or is it something else? — Matt Messersmith, Dec 19 '18 at 14:55
yes , name column matches in df2 if name column string contains any string of name column in df1 then it should take that folder name — ayushgupta, Dec 19 '18 at 15:00
Is there any reason to not merge on 'icon' and 'name' columns ? Works on your example. — Corentin Limier, Dec 19 '18 at 15:04
Then how do you know that `bbaddd` maps to `bbb`? It contains both `a` and `b`. — Matt Messersmith, Dec 19 '18 at 15:04
yes icon can be different value ....i just wrote it as example — ayushgupta, Dec 19 '18 at 15:04
This is a basic merge problem. See the duplicate for info on how to call df.merge. — cs95, Dec 19 '18 at 15:15
@coldspeed I don't think it is. I don't think the OP even needs to merge. He/she simply wants to derive `folder_name` directly from `name` (as far as I can tell). — Matt Messersmith, Dec 19 '18 at 15:16
@MattMessersmith Perhaps, since it is a single column, more performant alternatives with map exist (also in the linked dupe ...;-)) — cs95, Dec 19 '18 at 15:19
@ayushgupta Click on the linked dupe and search for "Merging only a single column from one of the DataFrames". — cs95, Dec 19 '18 at 15:19
@Thanks coldspeed really enjoyed your post, taught me a lot. — Umar.H, Dec 19 '18 at 15:21
Or...just do `df2['folder_name'] = df2['name'].apply(lambda x: x[0]*3)`... — Matt Messersmith, Dec 19 '18 at 15:21
@MattMessersmith it actually works somehow. Thanks . Let me test that — ayushgupta, Dec 19 '18 at 15:24
no it does not . Folder name can be anything else as well that was just an exampe — ayushgupta, Dec 19 '18 at 15:27
It works because what you're trying to do is fundamentally not a merge. `x[0]` is the first character of the `name`. Then, `x[0]*3` is that character repeated 3 times. That's how you're asking to generate the `folder_name` as far as I can tell. Again, it's not a merge, and `df1` appears to be irrelevant... — Matt Messersmith, Dec 19 '18 at 15:30
There's a better way you could do it by saying if `name` starts with "blah" then map that to the `folder_name` in `df1` that also starts with "blah" (which is a bit more general and probably what you want). — Matt Messersmith, Dec 19 '18 at 15:32
how should i do that . Folder name is just an example it can be anything not related to name column. see i have modified my question for better understanding — ayushgupta, Dec 19 '18 at 15:33
You'll have to appeal to @coldspeed to unlock the question, but I think his link does contain a (roundabout) answer to your question. I'm not going to spend time with a closed question though, sorry. — Matt Messersmith, Dec 19 '18 at 15:44

Merging columns in dataframe pandas

0 Answers0