Selecting a column value based on the value from another dataframe column

Question

I have a pandas dataframe similar to the one below:

  |   Date  | Value1 | Value2
0 | 2021-01 | 10.90  | 2.34
1 | 2021-02 | 12.85  | 4.65
2 | 2021-03 | 15.56  | 6.11
3 | 2021-04 | 18.23  | 8.62

Sample Code to create dataframe:

import pandas as pd

df = pd.DataFrame({
    "Date": ['2021-01', '2021-02', '2021-03', '2021-04'],
    "Value1": [10.90, 12.85, 15.56, 18.23],
    "Value2": [2.34, 4.65, 6.11, 8.62]
})

I want to extract the value from the Value1 and Value2 columns using a specific Date without making Date as index of the dataframe.

What I have done, works fine but looking for some better approach.

Single line code will be much appreciated for this task.

My approach:

value1_2021_01 = df[df["Date"] == '2021-01'][['Value1']].iat[0,0]

value2_2021_04 = df[df["Date"] == '2021-04'][['Value2']].iat[0,0]

Please note that the Date columns will always have unique values.

df.loc[df["Date"].isin(['2021-01', '2021-04']), ['Value1', 'Value2']] ? — Vivek Kalyanarangan
– Vivek Kalyanarangan, Commented Apr 5, 2021 at 13:39
@VivekKalyanarangan For single values, the return type is a pandas.DataFrame .. I need the float value. Adding .iat[0,0] at the end does the job but it more or less comes down to the same solution as mine. — Amit Pathak
– Amit Pathak, Commented Apr 5, 2021 at 14:16

insulanus · Accepted Answer · 2021-04-05 13:39:59Z

1

Try:

value1_2021_01 = df.loc[list(df["Date"]).index("2021-01"), "Value1"]
values = df.loc[list(df["Date"]).index("2021-01"), ["Value1", "Value2"]]  # for both values

On my machine it required 1/50 of the execution time for value1_2021_01 = df[df["Date"] == '2021-01'][['Value1']].iat[0,0].

answered Apr 5, 2021 at 13:39

insulanus

2711 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amit Pathak Over a year ago

I like the .index() part you used but how about when we have a large sample size ? Also, it does not work in my case, since the Date column is a Timestamp field.

insulanus Over a year ago

The performance gain decreases. However, with 10e3 rows, it still took only half the time compared to df[df["Date"] == '2021-01'][['Value1']].iat[0,0]. I guess, you could either cast the timestamps to strings or specify the index value as a timestamp.

Pygirl · Accepted Answer · 2021-04-05 17:51:24Z

1

I didn't get the question correctly but do you want something like this:

g = lambda x: df.query(f'Date=="{x}"')[['Value1', 'Value2']]
value1,value2 = g('2021-01').values[0]

print(value1, value2)

output:

(10.9, 2.34)

answered Apr 5, 2021 at 17:51

Pygirl

13.4k6 gold badges36 silver badges48 bronze badges

2 Comments

Amit Pathak Over a year ago

Did not think about using query because it is slower in operations (if I am not wrong) . But thanks, this adds some information !

Pygirl Over a year ago

Yes you can use df[col].eq(...) also

Collectives™ on Stack Overflow

Selecting a column value based on the value from another dataframe column

2 Answers 2

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Related