getting mean values of dates in pandas dataframe

Question

I can't seem to understand what the difference is between <M8[ns] and date time formats on how these operations relate to why this does or doesn't work.

import pandas as pd 
import datetime as dt 
import numpy as np 

my_dates = ['2021-02-03','2021-02-05','2020-12-25', '2021-12-27','2021-12-12']
my_numbers = [100,200,0,400,500]
df = pd.DataFrame({'a':my_dates, 'b':my_numbers})

df['a']=pd.to_datetime(df['a')

# ultimate goal is to be able to go. * df.mean() * and be able to see mean DATE
# but this doesn't seem to work so...

df['a'].mean().strftime('%Y-%m-%d') ### ok this works... I can mess around and concat stuff...

# But why won't this work?  

df2 = df.select_dtypes('datetime')
df2.mean() # WONT WORK
df2['a'].mean() # WILL WORK?

What I seem to be running into unless I am missing something is the difference between 'datetime' and '<M8[ns]' and how that works when I'm trying to get the mean date.

Isn't there a warning you get? – 9769953 Jul 17 '21 at 08:06 — 9769953, Jul 17 '21 at 08:06
Will adding a line `df2 = df2['a']` solve your problem? – Ricardo Jul 17 '21 at 08:13 — Ricardo, Jul 17 '21 at 08:13

Anurag Dabas · Answer 1 · 2021-07-17T08:24:03.297

0

You can try passing numeric_only parameter in mean() method:

out=df.select_dtypes('datetime').mean(numeric_only=False)

output of out:

a   2021-06-03 04:48:00
dtype: datetime64[ns]

Note: It will throw you an error If the dtype is string

edited Jul 17 '21 at 08:24

answered Jul 17 '21 at 08:11

Anurag Dabas

23,866
9
21
41

Does not work. After I do any select_dtypes, the operation returns a null output. This is what I am confused about. – runningbirds Jul 18 '21 at 04:05
@runningbirds there is no column with dtype datetime exists that's why you are getting none from `select_dtypes` so convert datetime like values to datetime first then try – Anurag Dabas Jul 18 '21 at 04:18
I went and did a work around – runningbirds Oct 07 '21 at 02:06

score 0 · Answer 2 · answered Jul 17 '21 at 14:08

mean function you apply is different in each case.

import pandas as pd 
import datetime as dt 
import numpy as np 

my_dates = ['2021-02-03','2021-02-05','2020-12-25', '2021-12-27','2021-12-12']
my_numbers = [100,200,0,400,500]
df = pd.DataFrame({'a':my_dates, 'b':my_numbers})

df['a']=pd.to_datetime(df['a'])
df.mean()

This mean function its the DataFrame mean function, and it works on numeric data. To see who is numeric, do:

df._get_numeric_data()

    b
0   100
1   200
2   0
3   400
4   500

But df['a'] is a datetime series.

df['a'].dtype, type(df)
(dtype('<M8[ns]'), pandas.core.frame.DataFrame)

So df['a'].mean() apply different mean function that works on datetime values. That's why df['a'].mean() output the mean of datetime values.

df['a'].mean()
Timestamp('2021-06-03 04:48:00')

read more here: difference-between-data-type-datetime64ns-and-m8ns DataFrame.mean() ignores datetime series #28108

Yes, I get this, but this isn't answering the question I was trying to ask. WHY does df['a'].mean() work just fine, but if I do df.select_dtypes('datetime').mean() NOT WORK? After I **select_dtypes**, the format changes from — runningbirds, Jul 18 '21 at 04:04

getting mean values of dates in pandas dataframe

2 Answers2