0

I have the below dataframe for mapping sheet:

mapping_df

    ids     true_id
    [1,2,3] abc1
    [4,7,8] def1

another df as

id name address
1  tnu  a123 
2  tn   a23
3  tnu  a1234
4  mnu  dd34
7  mnuu dd34
8  mna  dd3

output df as:

id name address true_id
1  tnu  a123      abc1
2  tn   a23       abc1
3  tnu  a1234     abc1
4  mnu  dd34      def1
7  mnuu dd34      def1
8  mna  dd3       def1

Any suggestions?

user3222101
  • 1,270
  • 2
  • 24
  • 43

2 Answers2

1

Create dictionary in comprehension and Series.map:

d = {k:v for x, v in mapping_df[['ids','true_id']].values for k in x}
print (d)
{1: 'abc1', 2: 'abc1', 3: 'abc1', 4: 'def1', 7: 'def1', 8: 'def1'}

df['true_id'] = df['id'].map(d)
print (df)
   id  name address true_id
0   1   tnu    a123    abc1
1   2    tn     a23    abc1
2   3   tnu   a1234    abc1
3   4   mnu    dd34    def1
4   7  mnuu    dd34    def1
5   8   mna     dd3    def1
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
0

unnesting df using np.repeat

df1 = pd.DataFrame(np.concatenate(df.ids), index=np.repeat(df.true_id, df.ids.str.len()), columns=['id']).reset_index()

Out[575]:
  true_id  id
0    abc1   1
1    abc1   2
2    abc1   3
3    def1   4
4    def1   7
5    def1   8

Merge df1 with another_df on id (I assume another_df has index on id. If it doesn't, just take out the reset_index)

df1.merge(another_df.reset_index(), on='id')

Out[578]:
  true_id  id  name address
0    abc1   1   tnu    a123
1    abc1   2    tn     a23
2    abc1   3   tnu   a1234
3    def1   4   mnu    dd34
4    def1   7  mnuu    dd34
5    def1   8   mna     dd3
Andy L.
  • 24,909
  • 4
  • 17
  • 29