The pandas DataFrame: Make Working With Data Delightful – Real Python (2024)

Creating a pandas DataFrame With Dictionaries

As you’ve already seen, you can create a pandas DataFrame with a Python dictionary:

>>> d = {'x': [1, 2, 3], 'y': np.array([2, 4, 8]), 'z': 100}>>> pd.DataFrame(d) x y z0 1 2 1001 2 4 1002 3 8 100

The keys of the dictionary are the DataFrame’s column labels, and the dictionary values are the data values in the corresponding DataFrame columns. The values can be contained in a tuple, list, one-dimensional NumPy array, pandas Series object, or one of several other data types. You can also provide a single value that will be copied along the entire column.

It’s possible to control the order of the columns with the columns parameter and the row labels with index:

>>> pd.DataFrame(d, index=[100, 200, 300], columns=['z', 'y', 'x']) z y x100 100 2 1200 100 4 2300 100 8 3

As you can see, you’ve specified the row labels 100, 200, and 300. You’ve also forced the order of columns: z, y, x.

Creating a pandas DataFrame With Lists

Another way to create a pandas DataFrame is to use a list of dictionaries:

>>> l = [{'x': 1, 'y': 2, 'z': 100},...  {'x': 2, 'y': 4, 'z': 100},...  {'x': 3, 'y': 8, 'z': 100}]>>> pd.DataFrame(l) x y z0 1 2 1001 2 4 1002 3 8 100

Again, the dictionary keys are the column labels, and the dictionary values are the data values in the DataFrame.

You can also use a nested list, or a list of lists, as the data values. If you do, then it’s wise to explicitly specify the labels of columns, rows, or both when you create the DataFrame:

>>> l = [[1, 2, 100],...  [2, 4, 100],...  [3, 8, 100]]>>> pd.DataFrame(l, columns=['x', 'y', 'z']) x y z0 1 2 1001 2 4 1002 3 8 100

That’s how you can use a nested list to create a pandas DataFrame. You can also use a list of tuples in the same way. To do so, just replace the nested lists in the example above with tuples.

Creating a pandas DataFrame With NumPy Arrays

You can pass a two-dimensional NumPy array to the DataFrame constructor the same way you do with a list:

>>> arr = np.array([[1, 2, 100],...  [2, 4, 100],...  [3, 8, 100]])>>> df_ = pd.DataFrame(arr, columns=['x', 'y', 'z'])>>> df_ x y z0 1 2 1001 2 4 1002 3 8 100

Although this example looks almost the same as the nested list implementation above, it has one advantage: You can specify the optional parameter copy.

When copy is set to False (its default setting), the data from the NumPy array isn’t copied. This means that the original data from the array is assigned to the pandas DataFrame. If you modify the array, then your DataFrame will change too:

>>> arr[0, 0] = 1000>>> df_ x y z0 1000 2 1001 2 4 1002 3 8 100

As you can see, when you change the first item of arr, you also modify df_.

Note: Not copying data values can save you a significant amount of time and processing power when working with large datasets.

If this behavior isn’t what you want, then you should specify copy=True in the DataFrame constructor. That way, df_ will be created with a copy of the values from arr instead of the actual values.

Creating a pandas DataFrame From Files

You can save and load the data and labels from a pandas DataFrame to and from a number of file types, including CSV, Excel, SQL, JSON, and more. This is a very powerful feature.

You can save your job candidate DataFrame to a CSV file with .to_csv():

>>> df.to_csv('data.csv')

The statement above will produce a CSV file called data.csv in your working directory:

,name,city,age,py-score101,Xavier,Mexico City,41,88.0102,Ann,Toronto,28,79.0103,Jana,Prague,33,81.0104,Yi,Shanghai,34,80.0105,Robin,Manchester,38,68.0106,Amal,Cairo,31,61.0107,Nori,Osaka,37,84.0

Now that you have a CSV file with data, you can load it with read_csv():

>>> pd.read_csv('data.csv', index_col=0) name city age py-score101 Xavier Mexico City 41 88.0102 Ann Toronto 28 79.0103 Jana Prague 33 81.0104 Yi Shanghai 34 80.0105 Robin Manchester 38 68.0106 Amal Cairo 31 61.0107 Nori Osaka 37 84.0

That’s how you get a pandas DataFrame from a file. In this case, index_col=0 specifies that the row labels are located in the first column of the CSV file.

pandas DataFrame Labels as Sequences

You can get the DataFrame’s row labels with .index and its column labels with .columns:

>>> df.indexInt64Index([1, 2, 3, 4, 5, 6, 7], dtype='int64')>>> df.columnsIndex(['name', 'city', 'age', 'py-score'], dtype='object')

Now you have the row and column labels as special kinds of sequences. As you can with any other Python sequence, you can get a single item:

>>> df.columns[1]'city'

In addition to extracting a particular item, you can apply other sequence operations, including iterating through the labels of rows or columns. However, this is rarely necessary since pandas offers other ways to iterate over DataFrames, which you’ll see in a later section.

You can also use this approach to modify the labels:

>>> df.index = np.arange(10, 17)>>> df.indexInt64Index([10, 11, 12, 13, 14, 15, 16], dtype='int64')>>> df name city age py-score10 Xavier Mexico City 41 88.011 Ann Toronto 28 79.012 Jana Prague 33 81.013 Yi Shanghai 34 80.014 Robin Manchester 38 68.015 Amal Cairo 31 61.016 Nori Osaka 37 84.0

In this example, you use numpy.arange() to generate a new sequence of row labels that holds the integers from 10 to 16. To learn more about arange(), check out NumPy arange(): How to Use np.arange().

Keep in mind that if you try to modify a particular item of .index or .columns, then you’ll get a TypeError.

Data as NumPy Arrays

Sometimes you might want to extract data from a pandas DataFrame without its labels. To get a NumPy array with the unlabeled data, you can use either .to_numpy() or .values:

>>> df.to_numpy()array([['Xavier', 'Mexico City', 41, 88.0], ['Ann', 'Toronto', 28, 79.0], ['Jana', 'Prague', 33, 81.0], ['Yi', 'Shanghai', 34, 80.0], ['Robin', 'Manchester', 38, 68.0], ['Amal', 'Cairo', 31, 61.0], ['Nori', 'Osaka', 37, 84.0]], dtype=object)

Both .to_numpy() and .values work similarly, and they both return a NumPy array with the data from the pandas DataFrame:

The pandas documentation suggests using .to_numpy() because of the flexibility offered by two optional parameters:

1. dtype: Use this parameter to specify the data type of the resulting array. It’s set to None by default.
2. copy: Set this parameter to False if you want to use the original data from the DataFrame. Set it to True if you want to make a copy of the data.

However, .values has been around for much longer than .to_numpy(), which was introduced in pandas version 0.24.0. That means you’ll probably see .values more often, especially in older code.

Data Types

The types of the data values, also called data types or dtypes, are important because they determine the amount of memory your DataFrame uses, as well as its calculation speed and level of precision.

pandas relies heavily on NumPy data types. However, pandas 1.0 introduced some additional types:

• BooleanDtype and BooleanArray support missing Boolean values and Kleene three-value logic.
• StringDtype and StringArray represent a dedicated string type.

You can get the data types for each column of a pandas DataFrame with .dtypes:

>>> df.dtypesname objectcity objectage int64py-score float64dtype: object

As you can see, .dtypes returns a Series object with the column names as labels and the corresponding data types as values.

If you want to modify the data type of one or more columns, then you can use .astype():

>>> df_ = df.astype(dtype={'age': np.int32, 'py-score': np.float32})>>> df_.dtypesname objectcity objectage int32py-score float32dtype: object

The most important and only mandatory parameter of .astype() is dtype. It expects a data type or dictionary. If you pass a dictionary, then the keys are the column names and the values are your desired corresponding data types.

As you can see, the data types for the columns age and py-score in the DataFrame df are both int64, which represents 64-bit (or 8-byte) integers. However, df_ also offers a smaller, 32-bit (4-byte) integer data type called int32.

pandas DataFrame Size

The attributes .ndim, .shape, and .size return the number of dimensions, number of data values across each dimension, and total number of data values, respectively:

>>> df_.ndim2>>> df_.shape(7, 4)>>> df_.size28

DataFrame instances have two dimensions (rows and columns), so .ndim returns 2. A Series object, on the other hand, has only a single dimension, so in that case, .ndim would return 1.

The .shape attribute returns a tuple with the number of rows (in this case 7) and the number of columns (4). Finally, .size returns an integer equal to the number of values in the DataFrame (28).

You can even check the amount of memory used by each column with .memory_usage():

>>> df_.memory_usage()Index 56name 56city 56age 28py-score 28dtype: int64

As you can see, .memory_usage() returns a Series with the column names as labels and the memory usage in bytes as data values. If you want to exclude the memory usage of the column that holds the row labels, then pass the optional argument index=False.

In the example above, the last two columns, age and py-score, use 28 bytes of memory each. That’s because these columns have seven values, each of which is an integer that takes 32 bits, or 4 bytes. Seven integers times 4 bytes each equals a total of 28 bytes of memory usage.

Getting Data With Accessors

In addition to the accessor .loc[], which you can use to get rows or columns by their labels, pandas offers the accessor .iloc[], which retrieves a row or column by its integer index. In most cases, you can use either of the two:

>>> df.loc[10]name Xaviercity Mexico Cityage 41py-score 88Name: 10, dtype: object>>> df.iloc[0]name Xaviercity Mexico Cityage 41py-score 88Name: 10, dtype: object

df.loc[10] returns the row with the label 10. Similarly, df.iloc[0] returns the row with the zero-based index 0, which is the first row. As you can see, both statements return the same row as a Series object.

pandas has four accessors in total:

1. .loc[] accepts the labels of rows and columns and returns Series or DataFrames. You can use it to get entire rows or columns, as well as their parts.

2. .iloc[] accepts the zero-based indices of rows and columns and returns Series or DataFrames. You can use it to get entire rows or columns, or their parts.

3. .at[] accepts the labels of rows and columns and returns a single data value.

4. .iat[] accepts the zero-based indices of rows and columns and returns a single data value.

Of these, .loc[] and .iloc[] are particularly powerful. They support slicing and NumPy-style indexing. You can use them to access a column:

>>> df.loc[:, 'city']10 Mexico City11 Toronto12 Prague13 Shanghai14 Manchester15 Cairo16 OsakaName: city, dtype: object>>> df.iloc[:, 1]10 Mexico City11 Toronto12 Prague13 Shanghai14 Manchester15 Cairo16 OsakaName: city, dtype: object

df.loc[:, 'city'] returns the column city. The slice construct (:) in the row label place means that all the rows should be included. df.iloc[:, 1] returns the same column because the zero-based index 1 refers to the second column, city.

Just as you can with NumPy, you can provide slices along with lists or arrays instead of indices to get multiple rows or columns:

>>> df.loc[11:15, ['name', 'city']] name city11 Ann Toronto12 Jana Prague13 Yi Shanghai14 Robin Manchester15 Amal Cairo>>> df.iloc[1:6, [0, 1]] name city11 Ann Toronto12 Jana Prague13 Yi Shanghai14 Robin Manchester15 Amal Cairo

In this example, you use:

• Slices to get the rows with the labels 11 through 15, which are equivalent to the indices 1 through 5
• Lists to get the columns name and city, which are equivalent to the indices 0 and 1

Both statements return a pandas DataFrame with the intersection of the desired five rows and two columns.

This brings up a very important difference between .loc[] and .iloc[]. As you can see from the previous example, when you pass the row labels 11:15 to .loc[], you get the rows 11 through 15. However, when you pass the row indices 1:6 to .iloc[], you only get the rows with the indices 1 through 5.

The reason you only get indices 1 through 5 is that, with .iloc[], the stop index of a slice is exclusive, meaning it is excluded from the returned values. This is consistent with Python sequences and NumPy arrays. With .loc[], however, both start and stop indices are inclusive, meaning they are included with the returned values.

You can skip rows and columns with .iloc[] the same way you can with slicing tuples, lists, and NumPy arrays:

>>> df.iloc[1:6:2, 0]11 Ann13 Yi15 AmalName: name, dtype: object

In this example, you specify the desired row indices with the slice 1:6:2. This means that you start with the row that has the index 1 (the second row), stop before the row with the index 6 (the seventh row), and skip every second row.

Instead of using the slicing construct, you could also use the built-in Python class slice(), as well as numpy.s_[] or pd.IndexSlice[]:

>>> df.iloc[slice(1, 6, 2), 0]11 Ann13 Yi15 AmalName: name, dtype: object>>> df.iloc[np.s_[1:6:2], 0]11 Ann13 Yi15 AmalName: name, dtype: object>>> df.iloc[pd.IndexSlice[1:6:2], 0]11 Ann13 Yi15 AmalName: name, dtype: object

You might find one of these approaches more convenient than others depending on your situation.

It’s possible to use .loc[] and .iloc[] to get particular data values. However, when you need only a single value, pandas recommends using the specialized accessors .at[] and .iat[]:

>>> df.at[12, 'name']'Jana'>>> df.iat[2, 0]'Jana'

Here, you used .at[] to get the name of a single candidate using its corresponding column and row labels. You also used .iat[] to retrieve the same name using its column and row indices.

Setting Data With Accessors

You can use accessors to modify parts of a pandas DataFrame by passing a Python sequence, NumPy array, or single value:

>>> df.loc[:, 'py-score']10 88.011 79.012 81.013 80.014 68.015 61.016 84.0Name: py-score, dtype: float64>>> df.loc[:13, 'py-score'] = [40, 50, 60, 70]>>> df.loc[14:, 'py-score'] = 0>>> df['py-score']10 40.011 50.012 60.013 70.014 0.015 0.016 0.0Name: py-score, dtype: float64

The statement df.loc[:13, 'py-score'] = [40, 50, 60, 70] modifies the first four items (rows 10 through 13) in the column py-score using the values from your supplied list. Using df.loc[14:, 'py-score'] = 0 sets the remaining values in this column to 0.

The following example shows that you can use negative indices with .iloc[] to access or modify data:

>>> df.iloc[:, -1] = np.array([88.0, 79.0, 81.0, 80.0, 68.0, 61.0, 84.0])>>> df['py-score']10 88.011 79.012 81.013 80.014 68.015 61.016 84.0Name: py-score, dtype: float64

In this example, you’ve accessed and modified the last column ('py-score'), which corresponds to the integer column index -1. This behavior is consistent with Python sequences and NumPy arrays.

Inserting and Deleting Rows

Imagine you want to add a new person to your list of job candidates. You can start by creating a new Series object that represents this new candidate:

>>> john = pd.Series(data=['John', 'Boston', 34, 79],...  index=df.columns, name=17)>>> johnname Johncity Bostonage 34py-score 79Name: 17, dtype: object>>> john.name17

The new object has labels that correspond to the column labels from df. That’s why you need index=df.columns.

You can add john as a new row to the end of df with .append():

>>> df = df.append(john)>>> df name city age py-score10 Xavier Mexico City 41 88.011 Ann Toronto 28 79.012 Jana Prague 33 81.013 Yi Shanghai 34 80.014 Robin Manchester 38 68.015 Amal Cairo 31 61.016 Nori Osaka 37 84.017 John Boston 34 79.0

Here, .append() returns the pandas DataFrame with the new row appended. Notice how pandas uses the attribute john.name, which is the value 17, to specify the label for the new row.

You’ve appended a new row with a single call to .append(), and you can delete it with a single call to .drop():

>>> df = df.drop(labels=[17])>>> df name city age py-score10 Xavier Mexico City 41 88.011 Ann Toronto 28 79.012 Jana Prague 33 81.013 Yi Shanghai 34 80.014 Robin Manchester 38 68.015 Amal Cairo 31 61.016 Nori Osaka 37 84.0

Here, .drop() removes the rows specified with the parameter labels. By default, it returns the pandas DataFrame with the specified rows removed. If you pass inplace=True, then the original DataFrame will be modified and you’ll get None as the return value.

Inserting and Deleting Columns

The most straightforward way to insert a column in a pandas DataFrame is to follow the same procedure that you use when you add an item to a dictionary. Here’s how you can append a column containing your candidates’ scores on a JavaScript test:

>>> df['js-score'] = np.array([71.0, 95.0, 88.0, 79.0, 91.0, 91.0, 80.0])>>> df name city age py-score js-score10 Xavier Mexico City 41 88.0 71.011 Ann Toronto 28 79.0 95.012 Jana Prague 33 81.0 88.013 Yi Shanghai 34 80.0 79.014 Robin Manchester 38 68.0 91.015 Amal Cairo 31 61.0 91.016 Nori Osaka 37 84.0 80.0

Now the original DataFrame has one more column, js-score, at its end.

You don’t have to provide a full sequence of values. You can add a new column with a single value:

>>> df['total-score'] = 0.0>>> df name city age py-score js-score total-score10 Xavier Mexico City 41 88.0 71.0 0.011 Ann Toronto 28 79.0 95.0 0.012 Jana Prague 33 81.0 88.0 0.013 Yi Shanghai 34 80.0 79.0 0.014 Robin Manchester 38 68.0 91.0 0.015 Amal Cairo 31 61.0 91.0 0.016 Nori Osaka 37 84.0 80.0 0.0

The DataFrame df now has an additional column filled with zeros.

If you’ve used dictionaries in the past, then this way of inserting columns might be familiar to you. However, it doesn’t allow you to specify the location of the new column. If the location of the new column is important, then you can use .insert() instead:

>>> df.insert(loc=4, column='django-score',...  value=np.array([86.0, 81.0, 78.0, 88.0, 74.0, 70.0, 81.0]))>>> df name city age py-score django-score js-score total-score10 Xavier Mexico City 41 88.0 86.0 71.0 0.011 Ann Toronto 28 79.0 81.0 95.0 0.012 Jana Prague 33 81.0 78.0 88.0 0.013 Yi Shanghai 34 80.0 88.0 79.0 0.014 Robin Manchester 38 68.0 74.0 91.0 0.015 Amal Cairo 31 61.0 70.0 91.0 0.016 Nori Osaka 37 84.0 81.0 80.0 0.0

You’ve just inserted another column with the score of the Django test. The parameter loc determines the location, or the zero-based index, of the new column in the pandas DataFrame. column sets the label of the new column, and value specifies the data values to insert.

You can delete one or more columns from a pandas DataFrame just as you would with a regular Python dictionary, by using the del statement:

>>> del df['total-score']>>> df name city age py-score django-score js-score10 Xavier Mexico City 41 88.0 86.0 71.011 Ann Toronto 28 79.0 81.0 95.012 Jana Prague 33 81.0 78.0 88.013 Yi Shanghai 34 80.0 88.0 79.014 Robin Manchester 38 68.0 74.0 91.015 Amal Cairo 31 61.0 70.0 91.016 Nori Osaka 37 84.0 81.0 80.0

Now you have df without the column total-score. Another similarity to dictionaries is the ability to use .pop(), which removes the specified column and returns it. That means you could do something like df.pop('total-score') instead of using del.

You can also remove one or more columns with .drop() as you did previously with the rows. Again, you need to specify the labels of the desired columns with labels. In addition, when you want to remove columns, you need to provide the argument axis=1:

>>> df = df.drop(labels='age', axis=1)>>> df name city py-score django-score js-score10 Xavier Mexico City 88.0 86.0 71.011 Ann Toronto 79.0 81.0 95.012 Jana Prague 81.0 78.0 88.013 Yi Shanghai 80.0 88.0 79.014 Robin Manchester 68.0 74.0 91.015 Amal Cairo 61.0 70.0 91.016 Nori Osaka 84.0 81.0 80.0

You’ve removed the column age from your DataFrame.

By default, .drop() returns the DataFrame without the specified columns unless you pass inplace=True.

Calculating With Missing Data

Many pandas methods omit nan values when performing calculations unless they are explicitly instructed not to:

>>> df_.mean()x 2.333333dtype: float64>>> df_.mean(skipna=False)x NaNdtype: float64

In the first example, df_.mean() calculates the mean without taking NaN (the third value) into account. It just takes 1.0, 2.0, and 4.0 and returns their average, which is 2.33.

However, if you instruct .mean() not to skip nan values with skipna=False, then it will consider them and return nan if there’s any missing value among the data.

Filling Missing Data

pandas has several options for filling, or replacing, missing values with other values. One of the most convenient methods is .fillna(). You can use it to replace missing values with:

• Specified values
• The values above the missing value
• The values below the missing value

Here’s how you can apply the options mentioned above:

>>> df_.fillna(value=0) x0 1.01 2.02 0.03 4.0>>> df_.fillna(method='ffill') x0 1.01 2.02 2.03 4.0>>> df_.fillna(method='bfill') x0 1.01 2.02 4.03 4.0

In the first example, .fillna(value=0) replaces the missing value with 0.0, which you specified with value. In the second example, .fillna(method='ffill') replaces the missing value with the value above it, which is 2.0. In the third example, .fillna(method='bfill') uses the value below the missing value, which is 4.0.

Another popular option is to apply interpolation and replace missing values with interpolated values. You can do this with .interpolate():

>>> df_.interpolate() x0 1.01 2.02 3.03 4.0

As you can see, .interpolate() replaces the missing value with an interpolated value.

You can also use the optional parameter inplace with .fillna(). Doing so will:

• Create and return a new DataFrame when inplace=False
• Modify the existing DataFrame and return None when inplace=True

The default setting for inplace is False. However, inplace=True can be very useful when you’re working with large amounts of data and want to prevent unnecessary and inefficient copying.

Deleting Rows and Columns With Missing Data

In certain situations, you might want to delete rows or even columns that have missing values. You can do this with .dropna():

>>> df_.dropna() x0 1.01 2.03 4.0

In this case, .dropna() simply deletes the row with nan, including its label. It also has the optional parameter inplace, which behaves the same as it does with .fillna() and .interpolate().

Creating DataFrames With Time-Series Labels

In this section, you’ll create a pandas DataFrame using the hourly temperature data from a single day.

You can start by creating a list (or tuple, NumPy array, or other data type) with the data values, which will be hourly temperatures given in degrees Celsius:

>>> temp_c = [ 8.0, 7.1, 6.8, 6.4, 6.0, 5.4, 4.8, 5.0,...  9.1, 12.8, 15.3, 19.1, 21.2, 22.1, 22.4, 23.1,...  21.0, 17.9, 15.5, 14.4, 11.9, 11.0, 10.2, 9.1]

Now you have the variable temp_c, which refers to the list of temperature values.

The next step is to create a sequence of dates and times. pandas provides a very convenient function, date_range(), for this purpose:

>>> dt = pd.date_range(start='2019-10-27 00:00:00.0', periods=24,...  freq='H')>>> dtDatetimeIndex(['2019-10-27 00:00:00', '2019-10-27 01:00:00', '2019-10-27 02:00:00', '2019-10-27 03:00:00', '2019-10-27 04:00:00', '2019-10-27 05:00:00', '2019-10-27 06:00:00', '2019-10-27 07:00:00', '2019-10-27 08:00:00', '2019-10-27 09:00:00', '2019-10-27 10:00:00', '2019-10-27 11:00:00', '2019-10-27 12:00:00', '2019-10-27 13:00:00', '2019-10-27 14:00:00', '2019-10-27 15:00:00', '2019-10-27 16:00:00', '2019-10-27 17:00:00', '2019-10-27 18:00:00', '2019-10-27 19:00:00', '2019-10-27 20:00:00', '2019-10-27 21:00:00', '2019-10-27 22:00:00', '2019-10-27 23:00:00'], dtype='datetime64[ns]', freq='H')

date_range() accepts the arguments that you use to specify the start or end of the range, number of periods, frequency, time zone, and more.

Now that you have the temperature values and the corresponding dates and times, you can create the DataFrame. In many cases, it’s convenient to use date-time values as the row labels:

>>> temp = pd.DataFrame(data={'temp_c': temp_c}, index=dt)>>> temp temp_c2019-10-27 00:00:00 8.02019-10-27 01:00:00 7.12019-10-27 02:00:00 6.82019-10-27 03:00:00 6.42019-10-27 04:00:00 6.02019-10-27 05:00:00 5.42019-10-27 06:00:00 4.82019-10-27 07:00:00 5.02019-10-27 08:00:00 9.12019-10-27 09:00:00 12.82019-10-27 10:00:00 15.32019-10-27 11:00:00 19.12019-10-27 12:00:00 21.22019-10-27 13:00:00 22.12019-10-27 14:00:00 22.42019-10-27 15:00:00 23.12019-10-27 16:00:00 21.02019-10-27 17:00:00 17.92019-10-27 18:00:00 15.52019-10-27 19:00:00 14.42019-10-27 20:00:00 11.92019-10-27 21:00:00 11.02019-10-27 22:00:00 10.22019-10-27 23:00:00 9.1

That’s it! You’ve created a DataFrame with time-series data and date-time row indices.

Indexing and Slicing

Once you have a pandas DataFrame with time-series data, you can conveniently apply slicing to get just a part of the information:

>>> temp['2019-10-27 05':'2019-10-27 14'] temp_c2019-10-27 05:00:00 5.42019-10-27 06:00:00 4.82019-10-27 07:00:00 5.02019-10-27 08:00:00 9.12019-10-27 09:00:00 12.82019-10-27 10:00:00 15.32019-10-27 11:00:00 19.12019-10-27 12:00:00 21.22019-10-27 13:00:00 22.12019-10-27 14:00:00 22.4

This example shows how to extract the temperatures between 05:00 and 14:00 (5 a.m. and 2 p.m.). Although you’ve provided strings, pandas knows that your row labels are date-time values and interprets the strings as dates and times.

Resampling and Rolling

You’ve just seen how to combine date-time row labels and use slicing to get the information you need from the time-series data. This is just the beginning. It gets better!

If you want to split a day into four six-hour intervals and get the mean temperature for each interval, then you’re just one statement away from doing so. pandas provides the method .resample(), which you can combine with other methods such as .mean():

>>> temp.resample(rule='6h').mean() temp_c2019-10-27 00:00:00 6.6166672019-10-27 06:00:00 11.0166672019-10-27 12:00:00 21.2833332019-10-27 18:00:00 12.016667

You now have a new pandas DataFrame with four rows. Each row corresponds to a single six-hour interval. For example, the value 6.616667 is the mean of the first six temperatures from the DataFrame temp, whereas 12.016667 is the mean of the last six temperatures.

Instead of .mean(), you can apply .min() or .max() to get the minimum and maximum temperatures for each interval. You can also use .sum() to get the sums of data values, although this information probably isn’t useful when you’re working with temperatures.

You might also need to do some rolling-window analysis. This involves calculating a statistic for a specified number of adjacent rows, which make up your window of data. You can “roll” the window by selecting a different set of adjacent rows to perform your calculations on.

Your first window starts with the first row in your DataFrame and includes as many adjacent rows as you specify. You then move your window down one row, dropping the first row and adding the row that comes immediately after the last row, and calculate the same statistic again. You repeat this process until you reach the last row of the DataFrame.

pandas provides the method .rolling() for this purpose:

>>> temp.rolling(window=3).mean() temp_c2019-10-27 00:00:00 NaN2019-10-27 01:00:00 NaN2019-10-27 02:00:00 7.3000002019-10-27 03:00:00 6.7666672019-10-27 04:00:00 6.4000002019-10-27 05:00:00 5.9333332019-10-27 06:00:00 5.4000002019-10-27 07:00:00 5.0666672019-10-27 08:00:00 6.3000002019-10-27 09:00:00 8.9666672019-10-27 10:00:00 12.4000002019-10-27 11:00:00 15.7333332019-10-27 12:00:00 18.5333332019-10-27 13:00:00 20.8000002019-10-27 14:00:00 21.9000002019-10-27 15:00:00 22.5333332019-10-27 16:00:00 22.1666672019-10-27 17:00:00 20.6666672019-10-27 18:00:00 18.1333332019-10-27 19:00:00 15.9333332019-10-27 20:00:00 13.9333332019-10-27 21:00:00 12.4333332019-10-27 22:00:00 11.0333332019-10-27 23:00:00 10.100000

Now you have a DataFrame with mean temperatures calculated for several three-hour windows. The parameter window specifies the size of the moving time window.

In the example above, the third value (7.3) is the mean temperature for the first three hours (00:00:00, 01:00:00, and 02:00:00). The fourth value is the mean temperature for the hours 02:00:00, 03:00:00, and 04:00:00. The last value is the mean temperature for the last three hours, 21:00:00, 22:00:00, and 23:00:00. The first two values are missing because there isn’t enough data to calculate them.