Finding specific hour minimum value using Pandas

Viewed 89

I have a dataframe that looks like this,

Date/Time               Volt        Current
2011-01-01 11:30:00     NaN         NaN
2011-01-01 11:35:00     NaN         NaN
2011-01-01 11:40:00     NaN         NaN
...
2011-01-01 12:30:00     NaN         NaN
2011-01-02 11:30:00     45          23
2011-01-02 11:35:00     31          34
2011-01-02 11:40:00     23          15
...
2011-01-02 12:30:00     13          1
2011-01-03 11:30:00     41          51
...
2011-01-03 12:25:00     14          5
2011-01-03 12:30:00     54          45
...
2011-01-04 11:30:00     45          -
2011-01-04 11:35:00     41          -
2011-01-04 11:40:00     -           4
...
2011-01-04 12:30:00     -           14

The dataframe has a date and time between 11:30:00 to 12:30:00 with a 5 minutes interval. I am trying to figure out how to find the minimum value based on the "Current" column for each day, and copy the entire row. My expected output should be something like this,

Date/Time               Volt        Current
2011-01-01              NaN         NaN
2011-01-02 12:30:00     13          1
2011-01-03 12:25:00     14          5
2011-01-04 11:40:00     NaN         4

For rows with a value in current, it will copy the entire minimum value row. For rows with "NaN" in current, it will copy the row still with NaN.

Do note that some data in the volt/current are something empty or with a dash.

Is this possible?

Thank you.

1 Answers

Please try,

df=df[df['Current'] != '-']
df.groupby(df['Date/Time'].dt.day).apply(lambda x:x.loc[x['Current'].astype(float).fillna(0).argmin(),:])
Related