can someone turn columns from a different dataset as values in another dataset by matching values from a column in the first dataset with the second

Question

sorry if I ain't clear, but got a challenge,

[this is the sample data I have generated to try to make my challenge clear] 1

sample data 1

B	V	S	F	K
0.32	10.32	11.32	12.32	13.32
1.32	11.32	12.32	13.32	14.32
2.32	12.32	13.32	14.32	15.32
3.32	13.32	14.32	15.32	16.32
4.32	14.32	15.32	16.32	17.32
5.32	15.32	16.32	17.32	18.32
6.32	16.32	17.32	18.32	19.32
7.32	17.32	18.32	19.32	20.32
8.32	18.32	19.32	20.32	21.32
9.32	19.32	20.32	21.32	22.32
10.32	20.32	21.32	22.32	23.32

my expected output

K	L	M
1	2.32
2	3.32
3	4.32
4	5.32
5	6.32
6	13.32
7	14.32
8	15.32
9	16.32
10	17.32

the second image explains the outcome

I would like to know how I would create another column M in dataset 2 that will return the name of the column from dataset 1 contains the values in column L (which is in dataset 2) I have tried the code below but it wasn't adding up to it since I had got this error and I thought someone here will help with this, thanks in advance!

spike_cols = [col for col in stata.columns if df['IMAGE NUMBER']  in col]```

returned the following error

```~\AppData\Local\Temp/ipykernel_25368/552331776.py in <module>
----> 1 spike_cols = [col for col in stata.columns if df['IMAGE NUMBER']  in col]

~\AppData\Local\Temp/ipykernel_25368/552331776.py in <listcomp>(.0)
----> 1 spike_cols = [col for col in stata.columns if df['IMAGE NUMBER']  in col]

TypeError: 'in <string>' requires string as left operand, not Series```

Answer 1

I created two dataframes here.For loop searches for matches and records column names for each row. You can also take dataframes from my message: df, df1 and place them in your message.

import pandas as pd
import numpy as np

df = pd.DataFrame({'B':[0.32,1.32,2.32,3.32,4.32,5.32,6.32,7.32,8.32,9.32,10.32],
                   'V':[10.32,11.32,12.32,13.32,14.32,15.32,16.32,17.32,18.32,19.32,20.32],
                   'S':[11.32,12.32,13.32,14.32,15.32,16.32,17.32,18.32,19.32,20.32,21.32],
                   'F':[12.32,13.32,14.32,15.32,16.32,17.32,18.32,19.32,20.32,21.32,22.32],
                   'K':[13.32,14.32,15.32,16.32,17.32,18.32,19.32,20.32,21.32,22.32,23.32]})

print(df)

df1 = pd.DataFrame({'K':[1,2,3,4,5,6,7,8,9,10],
                    'L':[2.32,3.32,4.32,5.32,6.32,13.32,14.32,15.32,16.32,17.32]})

M = []
for k in range(0, len(df1)):
    i, c = np.where(df == df1['L'][k])#Get the indexes of the columns where there was a match
    ttt = df.columns[c]#Get the name of the columns
    M.append(','.join(list(ttt)))#Output values to a comma-separated string if there are more than one values

df1['M'] = M #Adding a column with the received values

print(df1)