pretty new to python, I have a question regarding dataframes. So currently what I have is two different dataframes. One of them goes like this:

There is a problem regarding the table format so I am posting an image

The other one goes like this:

What I need to get is, to extract the value from the first dataframe that matches the second dataframe's respected rows. For example:The first row of the second dataframe matches 1 to 513, and the value that corresponds this match in the first dataframe is 1.8. For second row in the second dataframe, the match is 2 to 513, and the value that corresponds in the first dataframe is 2. How can I extract these values in a pythonic way?

My current code is like this:

data = first_dataframe    
B = second_dataframe['B'].tolist()

for i in range(954):
    for j in B:
        output = data.iat[i, j]
        print(output)

unfortunately what I'm getting is every single value of the first column of the first dataframe matching with all the values of the first row in the first dataframe. What should I change? Thanks in advance.

CodePudding user response：

DataFrame1:

df1=pd.DataFrame({
    1:[0.5,0.4,0.1],
    2:[0.6,0.2,0.5],
    3:[0.5,0.4,0.7],
    513:[1.8,2,5],
}, index=[1,2,3]).stack().to_frame(name="C")

DataFrame2:

df2=pd.DataFrame({
    "A":[1,2,3],
    "B":[513,513,730],
}, index=[1,2,3])

Merge:

df2.merge(df1,left_on=["A","B"],right_index=True)

Result: | |a|b|c| |--|--|--|--| |1|1|513|1.8| |2|2|513|2.0|

hope this is what you want?