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?
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
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
print (type(df.loc[0, 'ids']))?