0

This is my dataframe

import pandas as pd

data=pd.DataFrame({'vehicle':['car','car','car','car','car','car','bus','bus','bus','bus','bus','bus','car','car','car','car','car','car','bus','bus','bus','bus','bus','bus'],
'expecteddate':['2/24/2022','2/24/2022','3/15/2022','3/15/2022','4/20/2022','4/20/2022','2/24/2022','2/24/2022','3/15/2022','3/15/2022','4/20/2022','4/20/2022','2/24/2022','2/24/2022','3/15/2022','3/15/2022','4/20/2022','4/20/2022','2/24/2022','2/24/2022','3/15/2022','3/15/2022','4/20/2022','4/20/2022'],'range':[240,240,240,240,240,240,300,300,300,300,300,300,240,240,240,240,240,240,300,300,300,300,300,300],'color':['blue','red','blue','red','blue','red','blue','red','blue','red','blue','red','blue','red','blue','red','blue','red','blue','red','blue','red','blue','red'],'discount':[70,80,90,60,40,50,120,110,130,140,80,90,60,40,50,30,70,45,130,100,140,120,90,30],'date':['2/18/2022','2/18/2022','2/18/2022','2/18/2022','2/18/2022','2/18/2022','2/18/2022','2/18/2022','2/18/2022','2/18/2022','2/18/2022','2/18/2022','2/17/2022','2/17/2022','2/17/2022','2/17/2022','2/17/2022','2/17/2022','2/17/2022','2/17/2022','2/17/2022','2/17/2022','2/17/2022','2/17/2022']})
print(data)

data in dataframe:

   vehicle  expecteddate  range color  discount       date
0      car    2/24/2022    240  blue        70  2/18/2022
1      car    2/24/2022    240   red        80  2/18/2022
2      car    3/15/2022    240  blue        90  2/18/2022
3      car    3/15/2022    240   red        60  2/18/2022
4      car    4/20/2022    240  blue        40  2/18/2022
5      car    4/20/2022    240   red        50  2/18/2022
6      bus    2/24/2022    300  blue       120  2/18/2022
7      bus    2/24/2022    300   red       110  2/18/2022
8      bus    3/15/2022    300  blue       130  2/18/2022
9      bus    3/15/2022    300   red       140  2/18/2022
10     bus    4/20/2022    300  blue        80  2/18/2022
11     bus    4/20/2022    300   red        90  2/18/2022
12     car    2/24/2022    240  blue        60  2/17/2022
13     car    2/24/2022    240   red        40  2/17/2022
14     car    3/15/2022    240  blue        50  2/17/2022
15     car    3/15/2022    240   red        30  2/17/2022
16     car    4/20/2022    240  blue        70  2/17/2022
17     car    4/20/2022    240   red        45  2/17/2022
18     bus    2/24/2022    300  blue       130  2/17/2022
19     bus    2/24/2022    300   red       100  2/17/2022
20     bus    3/15/2022    300  blue       140  2/17/2022
21     bus    3/15/2022    300   red       120  2/17/2022
22     bus    4/20/2022    300  blue        90  2/17/2022
23     bus    4/20/2022    300   red        30  2/17/2022

from this dataframe we have two vehicles ,three expecteddates ,range,two colors ,discount and date. we have to find min value in discount and date at which we got min value in discount column ,into seperate two new columns that is mindisc column and mindate and that should be save in new column based on latest date ,this should filtered based on vehicle,expecteddate,range,color and date

we have to find min value in discount column in two dates(all dates as we have many dates not limited to two dates) 2/18/2022,2/17/2022 based on same color,range,expecteddate and vehicle

finally this min to added to mindisc column at latest date and corresponding date at which min date appeared to mindate column at latest date row

output should look like

 country expecteddate  range color  discount       date  mindisc    mindate
0      car    2/24/2022    240  blue        70  2/18/2022   60       2/17/2022
1      car    2/24/2022    240   red        80  2/18/2022   40       2/17/2022
2      car    3/15/2022    240  blue        90  2/18/2022   50       2/17/2022
3      car    3/15/2022    240   red        60  2/18/2022   30       2/17/2022
4      car    4/20/2022    240  blue        40  2/18/2022   40       2/18/2022
5      car    4/20/2022    240   red        50  2/18/2022   45       2/17/2022
6      bus    2/24/2022    300  blue       120  2/18/2022   120      2/18/2022
7      bus    2/24/2022    300   red       110  2/18/2022   100      2/17/2022
8      bus    3/15/2022    300  blue       130  2/18/2022   130      2/18/2022
9      bus    3/15/2022    300   red       140  2/18/2022   120      2/17/2022
10     bus    4/20/2022    300  blue        80  2/18/2022   80       2/18/2022
11     bus    4/20/2022    300   red        90  2/18/2022   30       2/17/2022
12     car    2/24/2022    240  blue        60  2/17/2022
13     car    2/24/2022    240   red        40  2/17/2022
14     car    3/15/2022    240  blue        50  2/17/2022
15     car    3/15/2022    240   red        30  2/17/2022
16     car    4/20/2022    240  blue        70  2/17/2022
17     car    4/20/2022    240   red        45  2/17/2022
18     bus    2/24/2022    300  blue       130  2/17/2022
19     bus    2/24/2022    300   red       100  2/17/2022
20     bus    3/15/2022    300  blue       140  2/17/2022
21     bus    3/15/2022    300   red       120  2/17/2022
22     bus    4/20/2022    300  blue        90  2/17/2022
23     bus    4/20/2022    300   red        30  2/17/2022

vehicles are not limited to two like car and bus ,it has many vehicles and data is not always have equal rows in vehicle and range columns and date is not limited to two dates

Krishna Prasad
  • 13
  • 5
  • About both columns "mindisc" and "mindate", what should be the content of the other rows? Do you expect NaN or to repeat the values? – aaossa Feb 20 '22 at 19:15
  • Nan values for remaining rows – Krishna Prasad Feb 21 '22 at 00:55
  • Then the new columns will not have relation with the other columns? I mean, are the new values aligned in some way? If they're not related to their row, might be possible to use a different dataframe? I'm trying to understand how to decide where does each value of the new columns go – aaossa Feb 21 '22 at 01:18
  • 1
    we have 2/18/22022,2/17/2022 dates ,consider 2/18/2022 as latest date ,when we find min in discout column and at which date ,the data should be added to latest date row at particular vehicle,expecteddate,range,color ,pls read i have commented below ur answer as eg., for better understanding of problem – Krishna Prasad Feb 21 '22 at 01:33

2 Answers2

0

This is my approach. Let's begin by treating the "date" column as a datetime object:

df["date"] = pd.to_datetime(df["date"])

Now, we can group the data as you described to find the rows where the minimum discount is reached:

common_groupby = df.groupby(["color", "range", "expecteddate", "vehicle"])

Now, let's find the rows where the minimum discount and the maximum date happens for each group:

source_idx = common_groupby["discount"].idxmin()
target_idx = common_groupby["date"].idxmax()
# Use df.loc[idx] to see the rows where the minimum discount is reached

Now, we can add the values of the minimum discount and the respective date (from source_idx rows) to the correct rows (from target_src) by using .loc:

df.loc[target_idx, "mindisc"] = df.loc[source_idx, "discount"].values
df.loc[target_idx, "mindate"] = df.loc[source_idx, "date"].values

As you can see, we're only changing the values in the rows where the minimum discount was reached (idx). This is the output of these operations:

   vehicle expecteddate  range color  discount       date  mindisc    mindate
0      car    2/24/2022    240  blue        70 2022-02-18     60.0 2022-02-17
1      car    2/24/2022    240   red        80 2022-02-18     40.0 2022-02-17
2      car    3/15/2022    240  blue        90 2022-02-18     50.0 2022-02-17
3      car    3/15/2022    240   red        60 2022-02-18     30.0 2022-02-17
4      car    4/20/2022    240  blue        40 2022-02-18     40.0 2022-02-18
5      car    4/20/2022    240   red        50 2022-02-18     45.0 2022-02-17
6      bus    2/24/2022    300  blue       120 2022-02-18    120.0 2022-02-18
7      bus    2/24/2022    300   red       110 2022-02-18    100.0 2022-02-17
8      bus    3/15/2022    300  blue       130 2022-02-18    130.0 2022-02-18
9      bus    3/15/2022    300   red       140 2022-02-18    120.0 2022-02-17
10     bus    4/20/2022    300  blue        80 2022-02-18     80.0 2022-02-18
11     bus    4/20/2022    300   red        90 2022-02-18     30.0 2022-02-17
12     car    2/24/2022    240  blue        60 2022-02-17      NaN        NaT
13     car    2/24/2022    240   red        40 2022-02-17      NaN        NaT
14     car    3/15/2022    240  blue        50 2022-02-17      NaN        NaT
15     car    3/15/2022    240   red        30 2022-02-17      NaN        NaT
16     car    4/20/2022    240  blue        70 2022-02-17      NaN        NaT
17     car    4/20/2022    240   red        45 2022-02-17      NaN        NaT
18     bus    2/24/2022    300  blue       130 2022-02-17      NaN        NaT
19     bus    2/24/2022    300   red       100 2022-02-17      NaN        NaT
20     bus    3/15/2022    300  blue       140 2022-02-17      NaN        NaT
21     bus    3/15/2022    300   red       120 2022-02-17      NaN        NaT
22     bus    4/20/2022    300  blue        90 2022-02-17      NaN        NaT
23     bus    4/20/2022    300   red        30 2022-02-17      NaN        NaT

Another possible solution trying to improve perormance by sorting and then droping duplicates twice (inspired by this question: Select the max row per group - pandas performance issue):

# Min discount rows
source_df = df.sort_values(by=["discount"], ascending=True, kind='mergesort').drop_duplicates(["color", "range", "expecteddate", "vehicle"])
source_df = source_df.rename(columns={"date": "mindate", "discount": "mindisc"})

# Max date rows
target_df = df.reset_index().sort_values(by=["date"], ascending=False, kind="mergesort").drop_duplicates(["color", "range", "expecteddate", "vehicle"])

# Put min discount values into max date rows
df.loc[target_df["index"], ["mindisc", "mindate"]] = source_df[["mindisc", "mindate"]].values
aaossa
  • 3,763
  • 2
  • 21
  • 34
  • Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/242351/discussion-on-answer-by-aaossa-filtering-data-from-data-frame-with-complex-crite). – Samuel Liew Feb 24 '22 at 07:56
  • @aaossa https://stackoverflow.com/questions/71792701/how-to-find-minimum-negative-change-among-values-in-column-in-pandas-dataframe The above question link is of same this question just need to add new column minnegchg and minnegchgdate , from finding min negative number from chgorders column . – Krishna Prasad Apr 08 '22 at 06:46
  • Suppose we have negative values in column then how to min or max negative value from that column regarding this question like finding max negative value and corresponding max negative value date – Krishna Prasad Apr 23 '22 at 17:26
-1

I didn't get the problem very well, but you could try something like:

new_df = df[(df['date'] == '2/18/2022') & (df['color'] == 'blue') & (df['vehicle'] == 'car')]

And then:

new_df['discount'].min()

luka1156
  • 181
  • 1
  • 8
  • consider, vehicle car and expected date 2/24/2021 and range 240 and color blue and date 2/18/2022,2/17/2022 and in this discount column we have to find min value and at which date min value came ,like we have 70,60 at dates 2/18/2022,2/17/2022 ,so min value is 60 at 2/17/2022 this should be added to new columns at latest date row of particular vehicle ,expecteddate,range,color like car 2/24/2022 240 blue 70 2/18/2022 60 2/17/2022 .this is for one expected date, but we should check sameas above for three expected dates based on vehicle,expected,range,color i hope you understood – Krishna Prasad Feb 21 '22 at 01:21