2

let's suppose I have one dataframe with at least two columns col1 and col2. Also I have another dataframe whose column names are values in col 1 and whose indices are values in col2. 

import pandas as pd df1 = pd.DataFrame( {'col1': ['x1', 'x2', 'x2'], 'col2': ['y0', 'y1', 'y0']}) print(df1) col1 col2 0 x1 y0 1 x2 y1 2 x2 y0 print(df2) y0 y1 x1 1 4 x2 2 5 x3 3 6

Now I wish to add col3 that gives me the value of the second dataframe at index of col1 and in column of col2. The result should look like this:

 col1 col2 col3 0 x1 y0 1 1 x2 y1 5 2 x2 y0 2

Thank you all!

Improve this question

2 Answers 2

Reset to default
1

You can use stack for new df with merge:

df2 = df2.stack().reset_index() df2.columns = ['col1','col2','col3'] print (df2) col1 col2 col3 0 x1 y0 1 1 x1 y1 4 2 x2 y0 2 3 x2 y1 5 4 x3 y0 3 5 x3 y1 6 print (pd.merge(df1, df2, on=['col1','col2'], how='left')) col1 col2 col3 0 x1 y0 1 1 x2 y1 5 2 x2 y0 2

Another solution is create new Series with join:

s = df2.stack().rename('col3') print (s) col1 col2 0 x1 y0 1 x2 y1 2 x2 y0 x1 y0 1 y1 4 x2 y0 2 y1 5 x3 y0 3 y1 6 Name: col3, dtype: int64 print (df1.join(s, on=['col1','col2'])) col1 col2 col3 0 x1 y0 1 1 x2 y1 5 2 x2 y0 2
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

second one's classy.
@NickilMaveli - Thank you.
1

Simple join

Pandas supports the join operation both on indexes and on columns, meaning you can do this:

df1.merge(df2, left_on='col1', right_index=True)

Produces

 col1 col2 y0 y1 0 x1 y0 1 4 1 x2 y1 2 5 2 x2 y0 2 5

Getting the proper value into col3 is the next step

Apply

This is a bit inefficient, but it is a way to get the correct data into one column

df['col3'] = df[['col2', 'y0', 'y1']].apply(lambda x: x[int(x[0][1]) + 1], axis=1)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.