CODEWITHSUNDEEP
pythonpandasdataframedictionary
CodingNinja
CodingNinja

Reputation: 109

How to assign dictionary value to dataframe by key matching to the name

dataframe:

Name  class  section
A      5        c
B      3        a
C      4        b

Dictionary:

dict={A:['Singing','dancing','Drawing'],C:['Gamming','Painting'],D:['Football','Basketball']}

Expected output:

Name  Class  Section  Hobby
A      5      c       Singing
                      dancing
                      Drawing
B      3      a      
C      4      b       Gaming
                      Painting
D                     Football
                      Basketball

I need to combine the dataframe and dictionary based on matching the names and, in some cases dataframe will not be having that name in that case I need to add extra row in dataframe, and I need to do this in python.

Upvotes: 1

Views: 1044

Answers (1)

jezrael
jezrael

Reputation: 862561

First dont use dict for dictionary variable, because python code name.

For add missing values form keys in dictionary use Index.union with DataFrame.reindex, then for new column use Series.map and then DataFrame.explode

df = df.set_index('Name')

df = df.reindex(df.index.union(d.keys(), sort=False)).rename_axis('Name').reset_index()

df['Hobby'] = df['Name'].map(d)
df = df.explode('Hobby')
print (df)
  Name  class section       Hobby
0    A    5.0       c     Singing
0    A    5.0       c     dancing
0    A    5.0       c     Drawing
1    B    3.0       a         NaN
2    C    4.0       b     Gamming
2    C    4.0       b    Painting
3    D    NaN     NaN    Football
3    D    NaN     NaN  Basketball

Last if need duplicated set to empty strings and also replace NaNs :

df.loc[df.index.duplicated(), ['Name','class','section']] = ''
df = df.fillna('').reset_index(drop=True)
print (df)
  Name class section       Hobby
0    A     5       c     Singing
1                        dancing
2                        Drawing
3    B     3       a            
4    C     4       b     Gamming
5                       Painting
6    D                  Football
7                     Basketball

Upvotes: 1

Related Questions