CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
mattrweaver
mattrweaver

Reputation: 789

Python/Pandas: add value from one df to end of row in another df if there is a match

I need to return the value of one column in df1 and append it to a row in a df2 if a value from the df2 is in the first.

Sample code

df1 = pd.DataFrame(
        {
        'terms' : ['term1','term2'],
        'code1': ['1234x', '4321y'],
        'code2': ['2345x','5432y'],
        'code3': ['3456x','6543y']
        }
        )
df1 = df1[['terms'] + df1.columns[:-1].tolist()]

df2 = pd.DataFrame(
        {
        'name': ['Dan','Sara','Conroy'],
        'rate': ['3','3.5','5.2'],
        'location': ['FL','OH','NM'],
        'code': ['4444g','6543y','2345x']                           
         })
df2 = df2[['name','rate','location','code']]

To merge the "code" columns into a new column, which results in a value I want to add to the rows in the second dataframe where there is a match.

df1['allcodes'] = df1[df1.columns[1:]].apply(lambda x: ','.join(x.dropna().astype(str)),axis=1)

Now df1 looks like:

 terms  code1  code2  code3           allcodes
0  term1  1234x  2345x  3456x  1234x,2345x,3456x
1  term2  4321y  5432y  6543y  4321y,5432y,6543y

What I need to do is, if df2['code'] is in df1['allcodes'], add the corresponding value of allcodes to the end of a row in df2 where there is a match.

The end result should be:

     name rate location   code allcodes
0    Sara  3.5       OH  6543y 4321y,5432y,6543y
1  Conroy  5.2       NM  2345x 1234x,2345x,3456x

Dan shouldn't be in there because his code isn't in df1

I have looked and merge/join/concat, but as the tables are different sizes and the code from df2 can appear in multiple columns in df1, I don't see how to use those functions.

Is this time for a lambda function, maybe with map? Any thoughts appreciated.

Upvotes: 1

Views: 982

Answers (2)

BENY
BENY

Reputation: 323306

Simple solution . 

xx=df1.set_index('terms').values.tolist()
df2['New']=df2.code.apply(lambda x : [y for y in xx if x in y] )
df2=df2[df2.New.apply(len)>0]
df2['New']=df2.New.apply(pd.Series)[0].apply(lambda x : ','.join(x))
df2
Out[524]: 
     name  rate location   code                New
1    Sara   3.5       OH  6543y  4321y,5432y,6543y
2  Conroy   5.2       NM  2345x  1234x,2345x,3456x

Upvotes: 2

cs95
cs95

Reputation: 402633

Setup

df1
   terms  code1  code2  code3
0  term1  1234x  2345x  3456x
1  term2  4321y  5432y  6543y

df2
     name rate location   code
0     Dan    3       FL  4444g
1    Sara  3.5       OH  6543y
2  Conroy  5.2       NM  2345x

At the cost of space, one fast way to do this would be generate two mappings, and then chain two map calls. 

m1 = df1.melt('terms').drop('variable', 1).set_index('value').terms
m2 = df1.set_index('terms').apply(lambda x: \
                      ','.join(x.values.ravel()), 1)


df2['allcodes'] = df2.code.map(m1).map(m2)
df2 = df2.dropna(subset=['allcodes']) 

df2   
     name rate location   code           allcodes
1    Sara  3.5       OH  6543y  4321y,5432y,6543y
2  Conroy  5.2       NM  2345x  1234x,2345x,3456x

Details

m1 
value
1234x    term1
4321y    term2
2345x    term1
5432y    term2
3456x    term1
6543y    term2
Name: terms, dtype: object

m2
terms
term1    1234x,2345x,3456x
term2    4321y,5432y,6543y
dtype: object

m1 will map code to the term, and m2 will map the term to the code group.

Upvotes: 2

Related Questions