Pandas Mapping 2 dfs

Question

I want to perform a mapping from 2 fields in df to 1 another df1. However, the field name for df1 is empty.
df:

Name1  Name2 Value
x      x-y      1
x      x-z      2
y      y-z      3
y      y-x      5
z      z-y      7

df1: (Is it possible to give a name to this empty slot?)

      Num     
x     1
y     2
z     3
a     4
a-b   5
x-y   0
x-z   5
y-z   0
y-x   5
z-y   0

The output would be:

Name1  Name2 Value  Num1   Num2
x      x-y      1     1     0
x      x-z      2     1     5
y      y-z      3     2     0
y      y-x      5     2     5
z      z-y      7     3     0

Thank you and happy holidays!

Answer 1

Option 1
You could use loc + values -

df.assign(
    Num1=df2.loc[df.Name1].values, Num2=df2.loc[df.Name2].values
)

  Name1 Name2  Value  Num1  Num2
0     x   x-y      1     1     0
1     x   x-z      2     1     5
2     y   y-z      3     2     0
3     y   y-x      5     2     5
4     z   z-y      7     3     0

---

Option 2
Another interesting alternative with loc + concat -

i = df2.loc[df.values[:, :2].ravel()].values
j = pd.DataFrame(i.reshape(len(df), -1), columns=['Num1', 'Num2'])

pd.concat([df, j], 1)


  Name1 Name2  Value  Num1  Num2
0     x   x-y      1     1     0
1     x   x-z      2     1     5
2     y   y-z      3     2     0
3     y   y-x      5     2     5
4     z   z-y      7     3     0

Answer 2

Perfect answers are already given, but I just have a note. If you haven't renamed the unnamed column (which is called an index), the posted solutions will work perfectly. I'll show how I do the renaming, and answer how to do the join after the renaming.

import pandas as pd
import sys
if sys.version_info[0] < 3:
    from StringIO import StringIO
else:
    from io import StringIO

# Create df
rawText = StringIO("""
Name1  Name2 Value
x      x-y      1
x      x-z      2
y      y-z      3
y      y-x      5
z      z-y      7
""")
df = pd.read_csv(rawText, sep = "\s+")

#Create df1 by renaming the unnamed index as Name3
rawText = StringIO("""
      Num     
x     1
y     2
z     3
a     4
a-b   5
x-y   0
x-z   5
y-z   0
y-x   5
z-y   0
""")
df1 = pd.read_csv(rawText, sep = "\s+")
df1 = df1.reset_index() # This works when you have multiple unnamed indices too
df1.rename(columns={'index':'Name3'}, inplace=True)

# Here is the solution
df.merge(df1, left_on='Name1', right_on='Name3')

Very fruitful discussion, happy holidays!

Answer 3

Moving this out of the comments just for visibility:

      Num     
x     1
y     2
z     3
a     4
a-b   5
x-y   0
x-z   5
y-z   0
y-x   5
z-y   0

df1 = pd.read_clipboard()

Output:

     Num
x      1
y      2
z      3
a      4
a-b    5
x-y    0
x-z    5
y-z    0
y-x    5
z-y    0

The column of letter(s) is the index. To rename it, you must use @cᴏʟᴅsᴘᴇᴇᴅ's solution:

df.index.name = 'name'

If you attempt to rename the index as you would a column, it does not work (with or without inplace = True):

df1.rename(columns = {'': 'Name'}, inplace = True)
print(df1)

returns:

     Num
x      1
y      2
z      3
a      4
a-b    5
x-y    0
x-z    5
y-z    0
y-x    5
z-y    0

Thanks for the discussion.

pandas rename index values

Answer 4

I am choosing map , you can also try merge and join, try look at index, in merge should be df1.merge(df2,left_on='Name1',right_index=True)

df['Num1']=df.Name1.map(df1.Num)
df['Num2']=df.Name2.map(df1.Num)
df
Out[1150]: 
  Name1 Name2  Value  Num1  Num2
0     x   x-y      1     1     0
1     x   x-z      2     1     5
2     y   y-z      3     2     0
3     y   y-x      5     2     5
4     z   z-y      7     3     0

Another way

df=df.set_index('Value').stack()
df.map(df1.Num)

pd.concat([df,df.map(df1.Num)],1).unstack()
Out[1169]: 
          0           1      
      Name1 Name2 Name1 Name2
Value                        
1         x   x-y     1     0
2         x   x-z     1     5
3         y   y-z     2     0
5         y   y-x     2     5
7         z   z-y     3     0