CODEWITHSUNDEEP
pythondatetimegroupingdayofweek
OcMaRUS
OcMaRUS

Reputation: 450

grouping DateTime by week of the day

I suppose, it should be easy question for experienced guys. I want to group records by week' day and to have number of records at particular week-day. Here is my DataFrame rent_week.info():

<class 'pandas.core.frame.DataFrame'>
Int64Index: 1689 entries, 3 to 1832
Data columns (total 11 columns):
 #   Column        Non-Null Count  Dtype         
---  ------        --------------  -----         
 0   id            1689 non-null   int64         
 1   createdAt     1689 non-null   datetime64[ns]
 2   updatedAt     1689 non-null   datetime64[ns]
 3   endAt         1689 non-null   datetime64[ns]
 4   timeoutAt     1689 non-null   datetime64[ns]
 5   powerBankId   1689 non-null   int64         
 6   station_id    1689 non-null   int64         
 7   endPlaceId    1686 non-null   float64       
 8   endStatus     1689 non-null   object        
 9   userId        1689 non-null   int64         
 10  station_name  1689 non-null   object        
dtypes: datetime64[ns](4), float64(1), int64(4), object(2)
memory usage: 158.3+ KB

Data in 'createdAt' columns looks like "2020-07-19T18:00:27.190010000" I am trying to add new column:

rent_week['a_day'] = rent_week['createdAt'].strftime('%A')

and receive error back: AttributeError: 'Series' object has no attribute 'strftime'. Meanwhile, if I write:

a_day = datetime.today()
print(a_day.strftime('%A'))

it shows expected result. In my understanding, a_day and rent_week['a_day'] have similar type datetime. Even request through:

rent_week['a_day'] = pd.to_datetime(rent_week['createdAt']).strftime('%A')

shows me the same error: no strftime attribute. I even didn't start grouping my data. What I am expecting in result is a DataFrame with information like:

 a_day       number_of_records
Monday       101
Tuesday      55
...

Upvotes: 1

Views: 402

Answers (3)

OcMaRUS
OcMaRUS

Reputation: 450

Thank you Quamar and Ojdo for your contribution. I found the problem: it is in index

<ipython-input-41-a42a82727cdd>:8: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  rent_week['a_day'] = rent_week['createdAt'].dt.strftime('%A')

as soon as I reset index

rent_week.reset_index()

both variants are working as expected!

Upvotes: 1

Qamar Abbas
Qamar Abbas

Reputation: 176

You can make a lambda function for conversion and apply that function to the column of "createdAt" Columns. After this step you can groupby based on your requirement. You can take help from this code:

rent_week['a_day'] = rent_week['createdAt'].apply(lambda x: x.strftime('%A'))

Upvotes: 1

ojdo
ojdo

Reputation: 8900

Try a_day.dt.strftime('%A') - note the additional .dt on your DataFrame column/Series object.

Background: the "similar" type assumption you make is almost correct. However, as a column could be of many types (numeric, string, datetime, geographic, ...), the methods of the underlying values are typically stored in a namespace to not clutter the already broad API (method count) of the Series type itself. That's why string functions are available only through .str, and datetime functions only available through .dt.

Upvotes: 1

Related Questions