Finding rows of dataframe with minimum values with same index,python

Question

I have a Dataframe with the index set to be the dates. I need to find the row (set of parameters for each month) where the value of the sensor is the minimum. The codes I tried are as following:

1.optimization = r.loc[[r.index.month == month]]  
  optimization=r.loc[r['Sensors'].idxmin()]
2.optimization=DataFrame(r.resample('M').Sensors.min()) 
3.optimization=r.loc[r['Sensors'].idxmin()]
4.optimization= r.loc[r.groupby([r.index.month == month])["Sensors"].idxmin()]

My datat farme:

Index          ACH_Base        Am          Energy                  Sensors  

2017_01_31       0.3           0.9         2.2989e+11          39087428391.1

2017_01_31       0.5           0.8         2.29892e+11         37142574944.9

....
2017_02_28       0.7           0.9         2.40001e+11         38568420286.9

2017_02_28       0.3           0.7         2.7136+11           36945759284.8

....

Thanks!

score 0 · Accepted Answer · answered Apr 06 '18 at 09:47

0

You can use Resampler.transform for get minimal values per month, compare with Sensors and filter by boolean indexing:

df.index = pd.to_datetime(df.index, format='%Y_%m_%d')

df = df[df['Sensors'] == df.resample('M')['Sensors'].transform('min')]
print (df)
            ACH_Base   Am       Energy       Sensors
2017-01-31       0.5  0.8  2.29892e+11  3.714257e+10
2017-02-28       0.3  0.7    2.7136+11  3.694576e+10

answered Apr 06 '18 at 09:47

jezrael

822,522
95
1,334
1,252

@ezrael thanks for the solution. I still get the whole dataframe! But one question, I did not use pd.to_datetime. Can be that the reason? – Maryam Nahid Apr 06 '18 at 09:57
@MaryamNahid - what is `print (df.index)` ? – jezrael Apr 06 '18 at 09:58
@MaryamNahid - need datetimeindex if want month values – jezrael Apr 06 '18 at 10:00
its the dates: 2017-01-31', '2017-01-31', '2017-01-31', '2017-01-31', '2017-01-31', '2017-01-31', '2017-01-31', '2017-01-31', '2017-01-31', '2017-01-31', ... '2017-06-30', '2017-06-30', '2017-06-30', '2017-06-30', '2017-06-30', '2017-06-30', '2017-06-30', '2017-06-30', '2017-06-30', '2017-06-30'] – Maryam Nahid Apr 06 '18 at 10:01
@MaryamNahid and what is `print (df.index.dtype)` ? – jezrael Apr 06 '18 at 10:02
it is : datetime64[ns] – Maryam Nahid Apr 06 '18 at 10:08
@MaryamNahid - OK, so it is good. But still dont understand why get all rows. with sample it working nice. Are data confidental? – jezrael Apr 06 '18 at 10:11
@MaryamNahid - so can you send me your code and mainly data file to my email in my profile? – jezrael Apr 06 '18 at 10:16

Finding rows of dataframe with minimum values with same index,python

1 Answers1