{"id":543,"date":"2018-10-28T19:08:50","date_gmt":"2018-10-28T18:08:50","guid":{"rendered":"http:\/\/35.180.88.53\/?p=543"},"modified":"2018-10-28T19:48:41","modified_gmt":"2018-10-28T18:48:41","slug":"tips-on-working-with-datetime-index-in-pandas","status":"publish","type":"post","link":"https:\/\/www.sergilehkyi.com\/es\/2018\/10\/tips-on-working-with-datetime-index-in-pandas\/","title":{"rendered":"Tips on Working with Datetime Index in pandas"},"content":{"rendered":"\n

As you may understand from the title it is not a complete guide on Time Series or Datetime data type in Python. So if you expect to get in-depth explanation from A to Z it’s a wrong place. Seriously. There is a fantastic article<\/a> on this topic, well explained, detailed and quite straightforward. Don’t waste your time on this one.<\/p>\n\n\n\n

For those who have reached this part I will tell that you will find something useful here for sure. Again, seriously. I found my notes on Time Series and decided to organize it into a little article with general tips, which are aplicable, I guess, in 80 to 90% of times you work with dates. So it’s worth sharing, isn’t it?<\/p>\n\n\n\n

I have a dataset with air pollutants measurements for every hour since 2016 in Madrid, so I will use it as an example.\u00a0<\/p>\n\n\n\n

Importing data<\/h3>\n\n\n\n

By default pandas will use the first column as index while importing csv file with read_csv()<\/code>, so if your datetime column isn’t first you will need to specify it explicitly index_col='date'.<\/code><\/p>\n\n\n\n

The beauty of pandas is that it can preprocess your datetime data during import. By\u00a0specifying parse_dates=True<\/code> pandas will try parsing the index, if we pass list of ints or names e.g. if [1, 2, 3] – it will try parsing columns 1, 2, 3 each as a separate date column,\u00a0list of lists e.g. if [[1, 3]] – combine columns 1 and 3 and parse as a single date column,\u00a0dict, e.g. {\u2018foo\u2019 : [1, 3]} – parse columns 1, 3 as date and call result \u2018foo\u2019. If you are using other method to import data you can always use\u00a0pd.to_datetime<\/code>\u00a0after it.<\/p>\n\n\n\n

I have imported my data using the following code:<\/p>\n\n\n\n

import pandas as pd
import glob

pattern = 'data\/madrid*.csv'
csv_files = glob.glob(pattern)

frames = []

for csv in csv_files:
    df = pd.read_csv(csv, index_col='date', parse_dates=True)
    frames.append(df)

df = pd.concat(frames)
df.head()

Out[4]: 
                     BEN  CH4   CO  EBE  NMHC     NO   NO_2  NOx  O_3  PM10  
date                                                                          
2016-11-01 01:00:00  NaN  NaN  0.7  NaN   NaN  153.0   77.0  NaN  NaN   NaN   
2016-11-01 01:00:00  3.1  NaN  1.1  2.0  0.53  260.0  144.0  NaN  4.0  46.0   
2016-11-01 01:00:00  5.9  NaN  NaN  7.5   NaN  297.0  139.0  NaN  NaN   NaN   
2016-11-01 01:00:00  NaN  NaN  1.0  NaN   NaN  154.0  113.0  NaN  2.0   NaN   
2016-11-01 01:00:00  NaN  NaN  NaN  NaN   NaN  275.0  127.0  NaN  2.0   NaN   <\/pre>\n\n\n\n
The data is gathered from 24 different stations about 14 different pollutants. We are not going to analyze this data, and to make it little bit simpler we will choose only one station, two pollutants and remove all NaN values (DANGER! please, do not repeat it at home).<\/p>\n\n\n\n