CODEWITHSUNDEEP
pythonpandas
user2110417
user2110417

Reputation:

How replace the comma separated values in a column with values in another dataframe with perfect match?

I have two dataframes as follows:

df1 = DataFrame([{'var1': 'A', 'var2': '10,20,30'},
               {'var1': 'B', 'var2': '40,5,2'},
               {'var1': 'D', 'var2': '10,2'}])

df1

   var1 var2
0   A   10,20,30
1   B   40,5,2
2   D   10,2

df2 = DataFrame([{'var1': 'a', 'var2': 10},
               {'var1': 'b', 'var2': 20},
               {'var1': 'c', 'var2': 30},
               {'var1': 'd', 'var2': 40},
               {'var1': 'e', 'var2': 5},
               {'var1': 'f', 'var2': 2}])

df2

  var1  var2
0   a   10
1   b   20
2   c   30
3   d   40
4   e   5
5   f   2

I want to replace the terms in df1['Var2'] with matching values in the df2['Var1']. So that the output look as follows:

  var1    var2
0  A      a,b,c   
1  B      d,e,f
2  D      a,f

Upvotes: 1

Views: 299

Answers (2)

mozway
mozway

Reputation: 261860

You can also use a simple list comprehension:

sub = df2.set_index('var2')['var1']
df1['var2'] = df1['var2'].apply(lambda s: ','.join([sub[int(x)] for x in s.split(',')]))

output:

  var1   var2
0    A  a,b,c
1    B  d,e,f
2    D    a,f

Upvotes: 0

Anurag Dabas
Anurag Dabas

Reputation: 24324

try spliting values then explode then map then groupby and finally aggregrate:

df1['var2']=(df1['var2'].str.split(',').explode()
                        .astype(int).map(dict(df2[['var2','var1']].values))
                        .groupby(level=0).agg(','.join))

Output of df1:

  var1  var2
0   A   a,b,c
1   B   d,e,f
2   D   a,f

Note: you can also use replace() in place of map() and df2.set_index('var1')['var2'] in place of dict(df2[['var2','var1']].values)

Upvotes: 1

Related Questions