CODEWITHSUNDEEP
pythonpandasdictionarydataframe
Abalan Musk
Abalan Musk

Reputation: 57

Loading the dictionary values in to the row values of a dataframe in pandas

I have a python dictionary

{1:cat,
  2:dog,
  3:sheep,
  4:foo,
  5:bar,
  6:fish,
  7:lion,
  8:shark,
  9:zebra,
  10:snake}

Also I have pandas dataframe as following df:

ID    col1    col2    col2   col4
18938   1       Nan     5      Nan
17839  Nan      2      Nan     8
72902   3       5       9      Nan
78298   7       Nan    Nan     6

Now I am trying to replace or map the values of each cell in each column the dictionary values and trying to concat all the column values to a new column.

The new df should look like:

ID    col1    col2    col2   col4     new_col
18938  cat      Nan    bar     Nan     cat|bar
17839  Nan      dog    Nan    shark    dog|shark
72902  sheep    bar   zebra    Nan    sheep|bar|zebra
78298  lion     Nan    Nan    fish     lion|fish

I am trying to achieve the 2nd step which is concat all the columns using the code

df['new_col'] = df.drop('ID',1).agg(lambda x: '|'.join(x.dropna().astype(str).values), axis=1)

but I am unable to get the first step working

I used 

df = df.columns.map(dict)

but it is not giving me the expected answer I need.

Upvotes: 1

Views: 65

Answers (2)

Scott Boston
Scott Boston

Reputation: 153500

You could try this:

df = df.set_index('ID')
d1 = pd.concat([df[i].replace('Nan',pd.np.nan).dropna().astype(int).map(d) for i in df.columns], axis=1)
d1['new_col'] = d1.apply(lambda x: '|'.join(x.dropna()), axis=1)
print(d1)

Or if you want a little slower but more concise code:

d1 = df.apply(lambda x: x.replace('Nan',pd.np.nan).dropna().astype(int).map(d))
d1['new_col'] = d1.apply(lambda x: '|'.join(x.dropna()), axis=1)
d1

Output:

        col1 col2 col2.1   col4          new_col
ID                                              
17839    NaN  dog    NaN  shark        dog|shark
18938    cat  NaN    bar    NaN          cat|bar
72902  sheep  bar  zebra    NaN  sheep|bar|zebra
78298   lion  NaN    NaN   fish        lion|fish

Upvotes: 2

Primusa
Primusa

Reputation: 13498

Use df.replace():

df = df.replace(dict)

Note that if the keys in your dictionary are strings you may need regex=True:

df = df.replace(dict, regex=True)

Example:

import pandas as pd

d = {1:"cat",
  2:"dog",
  3:"sheep",
  4:"foo",
  5:"bar",
  6:"fish",
  7:"lion",
  8:"shark",
  9:"zebra",
  10:"snake"}

df = pd.DataFrame({'ID': [123, 456], 'col1': [1, 2], 'col2': [5, 6]})
df = df.replace(d)

print(df)

Output:

    ID col1  col2
0  123  cat   bar
1  456  dog  fish

Upvotes: 2

Related Questions