Group rows by two columns and filter values by comparison

Question

I am trying to:

create a new dataframe (df2)
this new dataframe will contain rows from df1
to add these rows to df2 I have been grouped the columns in df1 by month and element
I would only select the values that exceed their monthly peers in df (for example if month 1 in df1 has 10 values that exceed the month 1 value in df I would include those 10 values in the new dataframe)

What I am failing to do now is to compare the values of each row grouped by month, against the monthly equivalent in df (which contains the maximum values per month)

I would like to select only the rows from df1 that have values that exceed their monthly peers in df

I got a solution from a stackoverflow user that allows me to get the maximum values for each month. Problem is in some cases there are more than 2 values in df1 that exceed the month equivalent in df.

This is the code I have so far:

df4 = df3[df3['Element'] =='TMAX'].groupby("Month").max()
df3_max = df4[df4.Data_Value > df_max.Data_Value]
df5 = df3[df3['Element'] =='TMIN'].groupby("Month").min()
df4_min = df5[(df5.Data_Value) < (df_min.Data_Value)]

This is df:

        Data_Value
   Month    
    1.0   217.0
    2.0   194.0
    3.0   317.0
    4.0   306.0
    5.0   367.0
    6.0   406.0
    7.0   406.0
    8.0   372.0
    9.0   372.0
    10.0    328.0
    11.0    256.0
    12.0    194.0

df1 (sample- full data has >1000 rows):

    ID             Date   Element  Data_Value  Month
0   USW00094889 2014-11-12  TMAX    220.0   11.0
1   USC00208972 2009-04-29  TMAX    560.0   1.0
2   USC00200032 2008-05-26  TMAX    278.0   5.0
3   USC00205563 2005-11-11  TMAX    239.0   11.0
4   USC00200230 2014-02-27  TMAX    -106.0  2.0
5   USW00014833 2010-10-01  TMAX    194.0   10.0
6   USC00207308 2010-06-29  TMIN    144.0   6.0
7   USC00203712 2005-10-04  TMAX    289.0   10.0
8   USW00004848 2007-12-14  TMIN    -16.0   12.0
9   USC00200220 2011-04-21  TMAX    72.0    4.0
10  USC00205822 2013-01-16  TMAX    411.0   1.0
11  USC00205822 2008-05-29  TMIN    28.0    5.0
12  USC00203712 2008-10-17  TMIN    17.0    10.0
13  USC00205563 2006-05-14  TMAX    183.0   5.0

This is my expected output (df2):

        Data_Value
   Month    
    1.0   560.0
    1.0   411.0

So from the df1 dataframe, only the 2nd and 11th row would be added to df2 because the values of these rows exceed the values from month 1 in df

user_2901002 · Answer

Use map for Series with same size like df2, so possible compare, only necessary unique values of months in df.index:

df3 = df1[df1['Data_Value'] > df1['Month'].map(df['Data_Value'])]
print (df3)
            vID        Date Element  Data_Value  Month
1   USC00208972  2009-04-29    TMAX       560.0    1.0
10  USC00205822  2013-01-16    TMAX       411.0    1.0

Group rows by two columns and filter values by comparison

1 Answers