2.1 Changing a dataframe’s index

We have seen that by default every dataframe has an integer index for its rows which starts from 0.

The dataframe we’ve been using, london , has an index that goes from 0 to 364 . The row indexed by 0 holds data for the first day of the year and the row indexed by 364 holds data for the last day of the year. However, the column 'GMT' holds datetime64 values which would make a more intuitive index.

Changing the index to datetime64 values is as easy as assigning to the dataframe’s index attribute the contents of the 'GMT' column, like this:

In []:

Out[]:

Figure 12

Show description|Hide description

First 2 rows of the london dataframe showing that the index has been changed to the datetime64 values from the GMT column. Note that only the first few columns are shown due to the limitation of page width.

Figure 12

Note that the right of the table has been cropped to fit on the page.

Notice that the 'GMT' column still remains and that the index has been labelled to show that it has been derived from the 'GMT' column.

You can still access a row using the iloc attribute, so to get the first line in the dataframe you can simply execute:

In []:

london.iloc[0]

Out[]:

But now you can now also use the datetime64 index to get a row using the dataframe’s loc attribute, like this:

In []:

london.loc[datetime(2014, 1, 1)]

Out[]:

A query such as ‘Return all the rows where the date is between 8 December and 12 December’ which you did before (and can still do) with:

In []:

can now be done more succinctly like this:

In []:

Out[]:

Figure 13

Show description|Hide description

Rows from the london dataframe where the index is between 2014-12-08 and 2014-12-12 (inclusive). Note that only the first few columns are shown due to the limitation of page width.

Figure 13

Note that the right of the table has been cropped to fit on the page.

Because the table is in date order, we can be confident that only the rows with dates between 8 December 2014 and 12 December 2014 (inclusive) will be returned. However if the table had not been in date order, we would have needed to sort it first, like this:

london = london.sort_index()

Now there is a datetime64 index, let’s plot ' Max Wind SpeedKm/h 'again:

In []:

london['Max Wind SpeedKm/h'].plot(grid=True, figsize=(10,5))

Out[]:

Figure 14

Show description|Hide description

Chart of the values in the Max Wind SpeedKm/h column of the london dataframe. Note that the legend for the x-axis has changed from numbers to month names.

Figure 14

Now it is much clearer that the worst winds were in mid-February.