3

I have these two dataFrames

data1 = [[1,'A'],[2,'B'],[3,'C'],[4,'D'],[5,'E']]
data2 = [1,1,1,1,2,5,4,3]
df1 = pd.DataFrame(data1,columns = ['one','two'])
df2 = pd.DataFrame(data2,columns = ['one'])

I want to map all values of df2 of column one with df1 of column two. In simple terms i want to use df1 as a dictionary. I want output like this for df2

   one
0    A
1    A
2    A
3    A
4    B
5    E
6    D
7    C

I am doing this

df2['one']= df2['one'].apply(lambda x: df1.two[df1.one == x])

Which gives me output

   one
0    A
1    A
2    A
3    A
4  NaN
5  NaN
6  NaN
7  NaN

All A is correct but why latter all are NaN?

Improve this question
1
  • 1
    Basically, you are facing some index alignment issues generated by usign pd.Series.apply... my solution below for more details. Commented Jul 12, 2020 at 21:46

4 Answers 4

Reset to default
3

Try this, much better syntax and functionality over using apply with a lambda function:

df2['one'].map(df1.set_index('one')['two'])

Output:

0    A
1    A
2    A
3    A
4    B
5    E
6    D
7    C
Name: one, dtype: object

Why your method doesn't work.... Look at the output of :

df2['one'].apply(lambda x: df1.two[df1.one == x])

Output:

     0    1    2    3    4
0    A  NaN  NaN  NaN  NaN
1    A  NaN  NaN  NaN  NaN
2    A  NaN  NaN  NaN  NaN
3    A  NaN  NaN  NaN  NaN
4  NaN    B  NaN  NaN  NaN
5  NaN  NaN  NaN  NaN    E
6  NaN  NaN  NaN    D  NaN
7  NaN  NaN    C  NaN  NaN

Because of index alignment in pandas only the first column, 0. get assigned. Here, you are using pd.Series.apply where you are applying the lambda function over the elements of a series and assigning it back to a dataFrame causing your mal-alignment issues.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is exactly what i want but why didn't apply work in my case
2

dict df1 columns and map to df2.

df2.one=df2.one.map(dict(zip(df1.one,df1.two)))

  one
0   A
1   A
2   A
3   A
4   B
5   E
6   D
7   C
Improve this answer

Comments

1

you can achieve that by performing a join.

import pandas as pd

data1 = [[1,'A'],[2,'B'],[3,'C'],[4,'D'],[5,'E']]
data2 = [1,1,1,1,2,5,4,3]
df1 = pd.DataFrame(data1,columns = ['one','two'])
df2 = pd.DataFrame(data2,columns = ['one'])

print(df1)

print(df2)

merge_df = pd.merge(df1,df2, on=['one'])[['two']]
print(merge_df)

output

two 0 A 1 A 2 A 3 A 4 B 5 C 6 D 7 E

Improve this answer

Comments

0 
df2.one = df2.one.map(dict(zip(df1.one,df1.two)))

I have tried this solution and it works for me:

enter image description here

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.