I have a Dataframe name df_mtrs:
I want to extract data from the data frame where customer_name presents in trxyear (2019 to 2022) continuously.
trxyear => Years e.g. 2019
customer_name => Company name e.g. ABC.ltd
Total_meters => meters(cloths) 20.0 meters
Does anyone think this question has already answer somewhere just send a link then I will gladly close this question.
Here is an example using query() and isin()
import pandas as pd
data = [["2019", "Customer1", 20.0],
["2020", "Customer1", 25.0],
["2021", "Customer1", 27.5],
["2019", "Customer2", 10.0],
["2020", "Customer2", 50.0],
["2021", "Customer2", 70.0],
["2019", "Customer3", 20.0],
["2020", "Customer3", 50.0],
["2021", "Customer3", 40.0],
["2022", "Customer3", 210.0]]
df = pd.DataFrame(data, columns=["trxyear", "customer_name", "Total_meters"])
# You can customize the filtering criteria
years_filter = ["2019", "2020", "2021", "2022"]
customer_filter = ["Customer2"]
# This returns the df with the filtered entries that match your criteria
df_filtered = df.query("trxyear in @years_filter and customer_name in @customer_filter")
# Sum up all the meters across the years you are searching for - if you plan on searching by multiple customer_name values, you would have to group_by customer_name
total_meters = df_filtered["Total_meters"].sum()
print(df_filtered)
print (f"Total meters: {total_meters}")
Assuming that the trxyear column is of type int, you can also run the following query with a continuous range for the year criteria:
df_filtered = df.query(f"trxyear >={start_year} and trxyear <={end_year} and customer_name in @customer_filter")
