Panda: Working With Date In Pandas

From OnnoWiki
Jump to navigation Jump to search

Sumber: https://galaxydatatech.com/2017/12/23/working-date-pandas/

Pandas provides powerful tools for working with dates and time data sets. Pandas datetime functionality heavily depends on the NumPy datetime64 and timedelta64 dtypes. Working with date in pandas is very important especially in the analysis of time related data sets. Date and time often comes in different format where some format contains numbers, some contain a combination of numbers and text. Before the modeling of data the date must be in the standard format so as to give accurate predictions. In this post we will learn how to work with date in pandas. Working With Date In Pandas

Python provides flexible methods for working with dates. Below, we are going to see how to manipulate date-time data in Pandas. Creating Date Range

To create ranges of dates in Pandas we use date_range() function. The pandas.date_range(start=None, end=None, periods=None, freq=’D’, tz=None, normalize=False, name=None, closed=None, **kwargs) function returns a DateTimeIndex. Below are some of the parameters in the function.

start : This argument specifies the start datetime-like string to be passed. If not specified the default is None.
end : This argument specifies the end datetime-like string to be passed. If not specified the default is None.
periods : This argument specifies the number of periods to be generated.
tz : This argument specifies the string time zone name for returning localized DatetimeIndex.
name :This argument specifies the name of the resulting DatetimeIndex.
freq : This specifies a Date Offset, the default being ‘D’ for calendar daily.

For more information about other parameters in the date_range function you can visit the Pandas date_range documentation here.

import pandas as pd
date_rng=pd.date_range('2017-12-01', periods=5)
print(date_rng)

Output

DatetimeIndex(['2017-12-01', '2017-12-02', '2017-12-03', '2017-12-04',
               '2017-12-05'],
              dtype='datetime64[ns]', freq='D')

Creating a Series From date_range()

import pandas as pd
date_rng=pd.date_range('2017-12-01', periods=5,name='Date')
s=pd.Series(date_rng)
print(s)

Output

0   2017-12-01
1   2017-12-02
2   2017-12-03
3   2017-12-04
4   2017-12-05
Name: Date, dtype: datetime64[ns]

Changing The Frequency

The default frequency is ‘D’ for day of calendar. We can change it to other offsets like M for month, and Y for year. Below example changes the frequency to month.

import pandas as pd
date_rng=pd.date_range('2017-12-01', periods=5,name='Date',freq='M')
s=pd.Series(date_rng)
print(s)

Output

0   2017-12-31
1   2018-01-31
2   2018-02-28
3   2018-03-31
4   2018-04-30
Name: Date, dtype: datetime64[ns]

bdate_range()

This function returns the business date from Monday to Friday. It excludes Saturday and Sunday.

import pandas as pd
date_rng=pd.bdate_range('2017-12-01', periods=7,name='Date')
s=pd.Series(date_rng)
print(s)

Output

0   2017-12-01
1   2017-12-04
2   2017-12-05
3   2017-12-06
4   2017-12-07
5   2017-12-08
6   2017-12-11
Name: Date, dtype: datetime64[ns]

Converting a String to a Date

import pandas as pd
s=pd.Series(['2017/12/01','2017/12/02','2017/12/03','2017/12/04','2017/12/05'])
print("String \n ",s)
date=pd.to_datetime(s)
print("\n\nDate \n ",date)

Output

String 
  0    2017/12/01
1    2017/12/02
2    2017/12/03
3    2017/12/04
4    2017/12/05
dtype: object

Date 
  0   2017-12-01
1   2017-12-02
2   2017-12-03
3   2017-12-04
4   2017-12-05
dtype: datetime64[ns]

Changing The DateFormat

We can easily specify the datetime format using the format argument. Below example convert the date format from ‘Y-m-d’ to ‘Y-d-m’

import pandas as pd
s=pd.Series(['2017/12/01','2017/12/02','2017/12/03','2017/12/04','2017/12/05'])
print("String \n ",s)
date=pd.to_datetime(s, format='%Y/%d/%m')
print("\n\nDate \n ",date)

Output

String 
  0    2017/12/01
1    2017/12/02
2    2017/12/03
3    2017/12/04
4    2017/12/05
dtype: object

Date 
  0   2017-01-12
1   2017-02-12
2   2017-03-12
3   2017-04-12
4   2017-05-12
dtype: datetime64[ns]


Converting Epoch Time To Timestamps

Pandas supports the conversion of epoch time to Timestamp or DatetimeIndex. Below example converts an epoch time to a DatetimeIndex.

import pandas as pd
date_epoch=pd.to_datetime([1512086400000000000, 1512172800000000000, 1512259200000000000, 1512345600000000000, 1512432000000000000])
print(date_epoch)

Output

DatetimeIndex(['2017-12-01', '2017-12-02', '2017-12-03', '2017-12-04',
               '2017-12-05'],
              dtype='datetime64[ns]', freq=None)

Converting Timestamp to Epoch Time.

We can convert time from timestamp format to epoch time using the view function.

import pandas as pd
date_rng=pd.date_range('2017-12-01', periods=5,name='Date')
print("DatetimeIndex \n ",date_rng)
epoch=date_rng.view('int64')
print("\n\nEpoch time \n ",epoch)

Output

DatetimeIndex 
  DatetimeIndex(['2017-12-01', '2017-12-02', '2017-12-03', '2017-12-04',
               '2017-12-05'],
              dtype='datetime64[ns]', name='Date', freq='D')
Epoch time 
  [1512086400000000000 1512172800000000000 1512259200000000000
 1512345600000000000 1512432000000000000]

Extracting Month and Year From Date

Below example extracts the month and year from date and creates new columns for them.

import pandas as pd
date_rng=pd.date_range('2017-12-01', periods=5,freq='M')
df=pd.DataFrame(date_rng,columns=['Date'])
print("Original DataFrame\n",df)

#create new columns for month and year
df['month']=df['Date'].dt.month
df['year']=df['Date'].dt.year

print("\n\nNew DataFrame\n",df)

Output

Original DataFrame
         Date
0 2017-12-31
1 2018-01-31
2 2018-02-28
3 2018-03-31
4 2018-04-30

New DataFrame
         Date  month  year
0 2017-12-31     12  2017
1 2018-01-31      1  2018
2 2018-02-28      2  2018
3 2018-03-31      3  2018
4 2018-04-30      4  2018


Let’s use lambda and apply function to extract year from date

import pandas as pd
date_rng=pd.date_range('2017-12-01', periods=5,freq='M')
df=pd.DataFrame(date_rng,columns=['Date'])
print("Original DataFrame\n",df)
df['Date'] = df['Date'].apply(lambda x: x.strftime('%Y'))
print("\nYear\n",df['Date'])

Output

Original DataFrame
         Date
0 2017-12-31
1 2018-01-31
2 2018-02-28
3 2018-03-31
4 2018-04-30

Year
 0    2017
1    2018
2    2018
3    2018
4    2018
Name: Date, dtype: object

Now, let’s extract the month and the year using lambda and apply function

import pandas as pd
date_rng=pd.date_range('2017-12-01', periods=5,freq='M')
df=pd.DataFrame(date_rng,columns=['Date'])
print("Original DataFrame\n",df)
df['Date'] = df['Date'].apply(lambda x: x.strftime('%B-%Y'))

Output

Original DataFrame
         Date
0 2017-12-31
1 2018-01-31
2 2018-02-28
3 2018-03-31
4 2018-04-30 

Month and Year
0    December-2017
1     January-2018
2    February-2018
3       March-2018
4       April-2018
Name: Date, dtype: object

Subtracting Dates in Pandas

In the example below we subtract the two dates and create a new column on the DataFrame. By default the difference is in days.

import pandas as pd
date_1=pd.date_range('2017-12-01', periods=5,freq='M')
date_2=pd.date_range('2018-03-05', periods=5,freq='M')
df=pd.DataFrame([date_1,date_2],index=['A','B']).T
print("Original DataFrame\n",df)
#subtract A from B
df['difference']=df['B']-df['A']
print("\n\nNew DataFrame\n",df)

Output

Original DataFrame
            A          B
0 2017-12-31 2018-03-31
1 2018-01-31 2018-04-30
2 2018-02-28 2018-05-31
3 2018-03-31 2018-06-30
4 2018-04-30 2018-07-31

New DataFrame
            A          B  difference
0 2017-12-31 2018-03-31     90 days
1 2018-01-31 2018-04-30     89 days
2 2018-02-28 2018-05-31     92 days
3 2018-03-31 2018-06-30     91 days
4 2018-04-30 2018-07-31     92 days

Pandas Timedelta

Timedelta are differences in times, expressed in days, hours, minutes, or seconds. The timedelta can be either positive or negative. Creating Timedelta

Using String

We can use string to create a timedelta as shown below.

import pandas as pd
time_delta=pd.Timedelta('15 days 30 minutes 45 seconds')
print(time_delta)

Output

15 days 00:30:45

Using Integer

We can create a timedelta by passing an integer in the timedelta function as shown below.

import pandas as pd
time_delta=pd.Timedelta(15,unit='d')
print(time_delta)

Output

15 days 00:00:00

Converting Text to Timedelta

We can easily convert any valid timedelta text to a timedelta format by using to_timedelta() function. The example below demonstrates how to convert a valid timedelta text DataFrame to a timedelta column and appending it to the original DataFrame.

import pandas as pd
df=pd.DataFrame(['15 days 30 minutes 45 seconds','20 days 10 minutes 55 seconds','29 days 50 minutes 03 seconds','30 days 24 minutes 07 seconds','31 days 59 minutes 59 seconds'],columns=['Timedelta Text'])
print("Original DataFrame\n",df)
df['Timedelta']=pd.to_timedelta(df['Timedelta Text'])
print("\n\nTimedelta DataFrame\n",df)

Output

Original DataFrame
                   Timedelta Text
0  15 days 30 minutes 45 seconds
1  20 days 10 minutes 55 seconds
2  29 days 50 minutes 03 seconds
3  30 days 24 minutes 07 seconds
4  31 days 59 minutes 59 seconds
Timedelta DataFrame
                   Timedelta Text        Timedelta
0  15 days 30 minutes 45 seconds 15 days 00:30:45
1  20 days 10 minutes 55 seconds 20 days 00:10:55
2  29 days 50 minutes 03 seconds 29 days 00:50:03
3  30 days 24 minutes 07 seconds 30 days 00:24:07
4  31 days 59 minutes 59 seconds 31 days 00:59:59

Working With Missing Date In Pandas

Sometimes we are presented with data that has missing datetime values. Most machine learning and data analysis models don’t work well with missing values. Handling missing datetime values is very important when cleaning data. Missing values in datetime data type is represented as NAT. Handling NAT is similar to handling of the NaN.

Creating a DataFrame with Missing Datetime Values.

import pandas as pd
import numpy as np
date_rng=pd.date_range('2017-11-01', periods=10,freq='M')
df=pd.DataFrame(date_rng,columns=['Date'])
df.loc[2,3,7,8]=np.nan
print(df)

Output

        Date
0 2017-11-30
1 2017-12-31
2        NaT
3        NaT
4 2018-03-31
5 2018-04-30
6 2018-05-31
7        NaT
8        NaT
9 2018-08-31

We can perform all the missing values operations like dropna, and fillna on NaN as well as on NAT .

Conclusion

Time is an important variable in research and statistical inference. Working with datetime data in Pandas is an important task as most of the data are analyzed in relation to time. There are two approaches of working with dates in data analysis, one of the option is to use Python datetime functionality and the other option is to use the Pandas date functionality. We have covered the basics of working with date in Pandas in this post. More advanced date functionality will be covered in other posts. What’s Next

Now that you have basics of working with date in Pandas, in the next post we will learn about the Pandas Group By functionality for splitting, applying and combining data.



Referensi