CODEWITHSUNDEEP
pythonpandasdataframe
Rosa Alejandra
Rosa Alejandra

Reputation: 732

Adding info of dictionary on Python Pandas DataFrame

I have some dictionaries like this:

d1 = {'a':['v1','v2'],'b':['v2','v3']}
d2 = {'a':['v4','v5'],'b':['v4','v6']}

and a DataFrame df like this:

  id    x
  v1  Nan
  v2  Nan
  v3  Nan
  v4  Nan
  v5  Nan
  v6  Nan

and another DataFrame another_dflike this:

id  name
 a  love
 b  hate

I need this as result: 

df =
  id    x
  v1  love
  v2  love,hate
  v3  hate
  v4  love,hate
  v5  love
  v6  hate

I have some ideas on how to do it, like add new columns and then join them with commas, but I thought I should spare the fun of doing so Any ideas to do it properly without giving me headache?

If it works for only one dictionary I'll unify the rest of the dictionaries.

Upvotes: 0

Views: 116

Answers (2)

BENY
BENY

Reputation: 323316

Here is my solution , I break done the steps 

d1 = {'a':['v1','v2'],'b':['v2','v3']}
d2 = {'a':['v4','v5'],'b':['v4','v6']}
anotherdf=anotherdf.set_index('id')
df=pd.DataFrame([d1,d2]).T
df=df.unstack().apply(pd.Series).stack().reset_index()
df['x']=df['level_1'].map(anotherdf['name'])

option 1 to list

df.groupby(0).x.apply(list).reset_index().rename(columns={0:'id'})
Out[296]: 
   id             x
0  v1        [love]
1  v2  [love, hate]
2  v3        [hate]
3  v4  [love, hate]
4  v5        [love]
5  v6        [hate]

option 2 string 

df.groupby(0).x.apply(lambda x : ','.join(x)).reset_index().rename(columns={0:'id'})
Out[295]: 
   id          x
0  v1       love
1  v2  love,hate
2  v3       hate
3  v4  love,hate
4  v5       love
5  v6       hate

Upvotes: 2

su79eu7k
su79eu7k

Reputation: 7326

cf. .items() works only python3.x in python2.x you should use .iteritems() instead.

d1 = pd.DataFrame([(y_, x) for x, y in d1.items() for y_ in y])
d2 = pd.DataFrame([(y_, x) for x, y in d2.items() for y_ in y])
d12 = pd.concat([d1, d2])

df.merge(d12, left_on='id', right_on=0) \
  .merge(another_df, left_on=1, right_on='id')[['id_x', 'name']] \
  .groupby('id_x').agg(lambda x: ', '.join(x.values)) \
  .reset_index()

    name
id_x    
v1  love
v2  love, hate
v3  hate
v4  love, hate
v5  love
v6  hate

Upvotes: 1

Related Questions