[FIXED] Set python variable based on multiple conditions within a dataframe

Issue

I want to create some variables (to be used in other calculations) based on a dataframe conditional on a number of columns including date. But for the date condition I want to use a datetime variable I have already created.

Dataframe looks like this:

Date         Product  Sales
28/09/2022   apple    10.2
28/09/2022   orange   5.1
28/09/2022   pear     12.3 
27/09/2022   apple    4.1
27/09/2022   orange   3.9
27/09/2022   pear     15.0

My date variable (which is 28/09/2022) date = datetime.today()

I'd like to create a variable called 'last_apple_sales' based on the dataframe where Date = my date variable and where Product = apple. So last_apple_sales = 10.2 in this case. And so on..

Had a go at using np.where with no luck.

Thank you.

Solution

Another possible solution:

mydate = datetime.now().date()
df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y').dt.date
last_apple_sales = df.loc[(df['Date'] == mydate) & (
    df['Product'] == 'apple'), 'Sales'].squeeze()

However, if your goal is to do that repeatedly, the best is to create a function:

def last_sales(df, mydate, product):
    df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y').dt.date
    return df.loc[(df['Date'] == mydate) & (df['Product'] == product), 'Sales'].squeeze()

mydate = datetime.now().date()
last_sales(df, mydate, 'apple')

Complete code:

import pandas as pd
import numpy as np
from io import StringIO
from datetime import datetime

text = """
Date         Product  Sales
28/09/2022   apple    10.2
28/09/2022   orange   5.1
28/09/2022   pear     12.3 
27/09/2022   apple    4.1
27/09/2022   orange   3.9
27/09/2022   pear     15.0
"""

df = pd.read_csv(StringIO(text), sep='\s+')

def last_sales(df, mydate, product):
    df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y').dt.date
    return df.loc[(df['Date'] == mydate) & (df['Product'] == product), 'Sales'].squeeze()

mydate = datetime.now().date()
last_sales(df, mydate, 'apple')

Answered By - PaulS