CODEWITHSUNDEEP
pythonpandasdatetime
tinker
tinker

Reputation: 3424

Using GroupBy with DateTime in Pandas (Python)

I have a data that looks like this that I get from an API (of course in JSON form):

0,1500843600,8872
1,1500807600,18890
2,1500811200,2902
.
.
.

where the second column is the date/time in ticks, and the third column is some value. I basically have the data for every hour of the day, for every day for a couple of months. Now, what I want to achieve is that I want to get the minimum value for the third column for every week. I have the code segment below, which correctly returns the minimum value for me, but apart from returning the minimum value, I also want to return the specific Timestamp as which date/time the lowest that week occurred. How can I modify my code below, so I can get also the Timestamp along with the minimum value.

df = pandas.DataFrame(columns=['Timestamp', 'Value'])

# dic holds the data that I get from my API.
for i in range(len(dic)):
    df.loc[i] = [dic[i][1], dic[i][2]]

df['Timestamp'] = pandas.to_datetime(df['Timestamp'], unit='s')
df.sort_values(by=['Timestamp'])
df.set_index(df['Timestamp'], inplace=True)

df.groupby([pandas.Grouper(key='Timestamp', freq='W-MON')])['Value'].min()

Upvotes: 1

Views: 56

Answers (1)

jezrael
jezrael

Reputation: 862641

I think you need DataFrameGroupBy.idxmin for index of min value of column Timestamp and then select by loc:

df['Timestamp'] = pd.to_datetime(df['Timestamp'], unit='s')

df = df.loc[df.groupby([pd.Grouper(key='Timestamp', freq='W-MON')])['Value'].idxmin()]
print (df)
            Timestamp  Value
2 2017-07-23 12:00:00   2902

Detail:

print (df.groupby([pd.Grouper(key='Timestamp', freq='W-MON')])['Value'].idxmin())
Timestamp
2017-07-24    2
Freq: W-MON, Name: Value, dtype: int64

Upvotes: 1

Related Questions