Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Python - How to Group Pandas DataFrame by Days?
We will group Pandas DataFrame using the groupby(). Select the column to be used using the grouper function. We will group day-wise and calculate sum of Registration Price with day interval for our example shown below for Car Sale Records.
Set the frequency as an interval of days in the groupby() grouper method, that means, if the freq is 7D, that would mean data grouped by interval of 7 days of every month till the last date given in the date column.
At first, let’s say the following is our Pandas DataFrame with three columns −
import pandas as pd
# dataframe with one of the columns as Date_of_Purchase
dataFrame = pd.DataFrame(
{
"Car": ["Audi", "Lexus", "Tesla", "Mercedes", "BMW", "Toyota", "Nissan", "Bentley", "Mustang"],
"Date_of_Purchase": [
pd.Timestamp("2021-06-10"),
pd.Timestamp("2021-07-11"),
pd.Timestamp("2021-06-25"),
pd.Timestamp("2021-06-29"),
pd.Timestamp("2021-03-20"),
pd.Timestamp("2021-01-22"),
pd.Timestamp("2021-01-06"),
pd.Timestamp("2021-01-04"),
pd.Timestamp("2021-05-09")
],
"Reg_Price": [1000, 1400, 1100, 900, 1700, 1800, 1300, 1150, 1350]
}
)Next, use the Grouper to select Date_of_Purchase column within groupby function. The frequency is set 7D i.e. interval of 7 Days grouped till the last date mentioned in the column −
print"\nGroup Dataframe by 7 days...\n",dataFrame.groupby(pd.Grouper(key='Date_of_Purchase', axis=0, freq='7D')).sum()
Example
Following is the code −
import pandas as pd
# dataframe with one of the columns as Date_of_Purchase
dataFrame = pd.DataFrame(
{
"Car": ["Audi", "Lexus", "Tesla", "Mercedes", "BMW", "Toyota", "Nissan", "Bentley", "Mustang"],
"Date_of_Purchase": [
pd.Timestamp("2021-06-10"),
pd.Timestamp("2021-07-11"),
pd.Timestamp("2021-06-25"),
pd.Timestamp("2021-06-29"),
pd.Timestamp("2021-03-20"),
pd.Timestamp("2021-01-22"),
pd.Timestamp("2021-01-06"),
pd.Timestamp("2021-01-04"),
pd.Timestamp("2021-05-09")
],
"Reg_Price": [1000, 1400, 1100, 900, 1700, 1800, 1300, 1150, 1350]
}
)
print"DataFrame...\n",dataFrame
# Grouper to select Date_of_Purchase column within groupby function
print("\nGroup Dataframe by 7 days...\n",dataFrame.groupby(pd.Grouper(key='Date_of_Purchase', axis=0, freq='7D')).sum()
)Output
This will produce the following output −
DataFrame... Car Date_of_Purchase Reg_Price 0 Audi 2021-06-10 1000 1 Lexus 2021-07-11 1400 2 Tesla 2021-06-25 1100 3 Mercedes 2021-06-29 900 4 BMW 2021-03-20 1700 5 Toyota 2021-01-22 1800 6 Nissan 2021-01-06 1300 7 Bentley 2021-01-04 1150 8 Mustang 2021-05-09 1350 Group Dataframe by 7 days... Reg_Price Date_of_Purchase 2021-01-04 2450.0 2021-01-11 NaN 2021-01-18 1800.0 2021-01-25 NaN 2021-02-01 NaN 2021-02-08 NaN 2021-02-15 NaN 2021-02-22 NaN 2021-03-01 NaN 2021-03-08 NaN 2021-03-15 1700.0 2021-03-22 NaN 2021-03-29 NaN 2021-04-05 NaN 2021-04-12 NaN 2021-04-19 NaN 2021-04-26 NaN 2021-05-03 1350.0 2021-05-10 NaN 2021-05-17 NaN 2021-05-24 NaN 2021-05-31 NaN 2021-06-07 1000.0 2021-06-14 NaN 2021-06-21 1100.0 2021-06-28 900.0 2021-07-05 1400.0
1K+ Views
