Pandas - How To Organised Dataframe Based On Date And Assign New Values To Column
I have a dataframe of a month excluding Saturday and Sunday, which was logged every 1 minute. v1 v2 2017-04-03 09:15:00 35.7 35.4
Solution 1:
You can groupby on index and use groupby.agg
with a custom function.
df1 = res.groupby(res.index.date).agg({'v1': lambda x: x[min(x.index)], 'v2':lambda x: x[max(x.index)]})
print (df1)
v1 v2
2017-04-03 35.782.62017-04-04 24.370.62017-04-2831.733.7
An alternative to resample dataframe to get 1st and last value from each day.
res=df.reset_index().groupby(df.index.date).agg(['first','last']).stack().set_index('index')Out[123]:v1v2index2017-04-03 09:15:00 35.735.42017-04-03 16:30:00 82.782.62017-04-04 09:15:00 24.324.22017-04-04 16:30:00 70.270.62017-04-28 09:15:00 31.731.42017-04-28 16:30:00 33.033.7
Solution 2:
Try this:
df_result = pd.DataFrame()
df_result['v1'] = res.groupby(res.index)['v1'].min()
df_result['v2'] = res.groupby(res.index)['v2'].max()
Solution 3:
There is a very interesting fonction in pandas to work with the datetime index. It is the resampling fonction. In your Case try this :
deffirst_last(entry):
returnentry['v1'][0],entry['v2'][1]yourdataframe.resample('D').apply(first_last)
the 'D' stands for Daily resampling.
result :
Dates2017-04-03 35.782.62017-04-04 24.370.6
Solution 4:
You can reset_index
and then GroupBy
+ apply
with a custom function:
deffirst_second(x):
return pd.Series({'v1': x['v1'].iat[0], 'v2': x['v2'].iat[-1]})
res2 = res.reset_index()
res2 = res2.groupby(res2['index'].dt.date).apply(first_second)
print(res2)
v1 v2
index
2017-04-03 35.782.62017-04-04 24.370.62017-04-2831.733.7
Post a Comment for "Pandas - How To Organised Dataframe Based On Date And Assign New Values To Column"