user2517214 user2517214 - 1 year ago 46
Python Question

add timestamps related fields from existing unix timestamp field dataframe in python

I am new to Python Programing language. For my learning I took sample sales data that contain orderid, product_name, no_items, amount, total_amount, sale_time. I am using pandas to load the data.

import pandas as pd
sale_data = pd.read_csv("test_sale_data.csv")

sale_time field have unix time and I am using to_datetime function to convert into readable datetime format.

sale_data['sale_time_new'] = to_datetime(sale_data['sale_time'],time='s')

# 1284101485 --> 2010-09-10 06:51:25

So I am trying to add timestamps related fields to existing sale_data dataframe like Year, Quarter, YearQuarter, Month, YearMonth, Week, YearWeek, date, day, datehour, hour.

1284101485 --> 2010, Q3, 2010-Q3, 09, 201009, 36, 2010-WW36, 2010-09-10, 10, 2010-09-10 06:00:00 , 06

How can I do in python ?

Answer Source

Use the .dt accessor in pandas; all of these components are available. For your use case:

sale_data['sale_time_new'] = to_datetime(sale_data['sale_time'],time='s')

# Year
# Quarter
# YearQuarter
'{y}-{q}'.format(y=sale_data['sale_time_new'].dt.year, q=sale_data['sale_time_new'].dt.year)
# Month
# YearMonth
sale_data['sale_time_new'].dt.year*100 + sale_data['sale_time_new'].dt.month
# Week
# YearWeek
'{y}-WW{w}'.format(y=sale_data['sale_time_new'].dt.year, w=sale_data['sale_time_new'].dt.week)
# date
# day
# datehour
'{y}-{m}-{d} {h}:00:00'.format(y=sale_data['sale_time_new'].dt.year, m=sale_data['sale_time_new'].dt.month, d=sale_data['sale_time_new'], h=sale_data['sale_time_new'].dt.hour)
# hour