How to get values from a DataFrame depending on a predefiend row & column value

Question

I have two DataFrames in python, where I'm trying to return the values in df based on the date and column name, the real DataFrame is very long so this must be done using some kind of loop

df = pd.DataFrame({
    'Avg1': [1, 2, 3, 4],
    'Avg2': [3, 5, 1, 15],
    'Date': ['2021-08-06', '2021-08-07', '2021-08-08', '2021-08-07']
})

	Avg1	Avg2	Date
0	1	3	2021-08-06
1	2	5	2021-08-07
2	3	1	2021-08-08
3	4	15	2021-08-07

df2 = pd.DataFrame({
    'Return Avg': ['Avg1', 'Avg2'],
    'At Date': ['2021-08-08', '2021-08-07'],
    'Returned values (what I want)': [3, 5]
})

	Return Avg	At Date	Returned values (what I want)
0	Avg1	2021-08-08	3
1	Avg2	2021-08-07	5

score 1 · Accepted Answer · edited Sep 09 '21 at 05:15

1

I think melt is what you are looking for.

df = pd.DataFrame({
    'Avg1': [1, 2, 3, 4],
    'Avg2': [3, 5, 1, 15],
    'Date': ['2021-08-06', '2021-08-07', '2021-08-08', '2021-08-07']
})

then :

df.melt(id_vars=['Date'], value_vars=['Avg1', 'Avg2'])

         Date variable  value
0  2021-08-06     Avg1      1
1  2021-08-07     Avg1      2
2  2021-08-08     Avg1      3
3  2021-08-07     Avg1      4
4  2021-08-06     Avg2      3
5  2021-08-07     Avg2      5
6  2021-08-08     Avg2      1
7  2021-08-07     Avg2     15

edited Sep 09 '21 at 05:15

Henry Ecker

34,399
18
41
57

answered Sep 05 '21 at 08:16

Ali Crash

458
2
5
15

That seems correct but the real dataframe is quite large so I'm unsure as to how I would loop from AVG1 to AVGn(value_vars) using melt – hhh hhh Sep 05 '21 at 08:33
Got it to work by just using df.melt(id_vars=['Date']) , thanks <3 – hhh hhh Sep 05 '21 at 10:18

score 0 · Answer 2 · answered Sep 05 '21 at 08:32

let's start with a function

table1 = pd.DataFrame({'Avg1':[1,2,3,4],'Avg2':[3,5,1,15],'Date':['2021-08-06','2021-08-07','2021-08-08','2021-08-09']})

df2 = pd.DataFrame({'Return Avg':['Avg1','Avg2'],'At Date':['2021-08-08','2021-08-07']})    

def get_the_currect_value(date, col_name, ext_table):
        return ext_table.loc[ext_table['Date'] == date, col_name].iloc[0]

now after the function, we will use apply to create the new column you want

df2['rv'] = df2.apply(lambda x: get_the_currect_value(x['At Date'],x['Return Avg'], table1),axis=1)

and that it

How to get values from a DataFrame depending on a predefiend row & column value

2 Answers2