Processing data with pandas#

Attention

Finnish university students are encouraged to use the CSC Notebooks platform.
CSC badge

Others can follow the lesson and fill in their student notebooks using Binder.
Binder badge

During the first part of this lesson you learned the basics of pandas data structures (Series and DataFrame) and got familiar with basic methods loading and exploring data. Here, we will continue with basic data manipulation and analysis methods such calculations and selections.

We are now working in a new notebook file and we need to import pandas again.

import pandas as pd

Let’s work with the same input data 'Kumpula-June-2016-w-metadata.txt' and load it using the pd.read_csv() method. Remember, that the first 8 lines contain metadata so we can skip those. This time, let’s store the filepath into a separate variable in order to make the code more readable and easier to change afterwards:

# Define file path:
fp = "Kumpula-June-2016-w-metadata.txt"

# Read in the data from the file (starting at row 9):
data = pd.read_csv(fp, skiprows=8)

Remember to always check the data after reading it in:

data.head()
YEARMODA TEMP MAX MIN
0 20160601 65.5 73.6 54.7
1 20160602 65.8 80.8 55.0
2 20160603 68.4 NaN 55.6
3 20160604 57.5 70.9 47.3
4 20160605 51.4 58.3 43.2

Filepaths

Note, that our input file 'Kumpula-June-2016-w-metadata.txt' is located in the same folder as the notebook we are running. Furthermore, the same folder is the working directory for our Python session (you can check this by running the %pwd magic command). For these two reasons, we are able to pass only the filename to .read_csv() function and pandas is able to find the file and read it in. In fact, we are using a relative filepath when reading in the file.

The absolute filepath to the input data file in the CSC cloud computing environment is /home/jovyan/my-work/notebooks/L5/Kumpula-June-2016-w-metadata.txt, and we could also use this as input when reading in the file. When working with absolute filepaths, it’s good practice to pass the file paths as a raw string using the prefix r in order to avoid problems with escape characters such as "\n".

# Define file path as a raw string:
fp = r'/home/jovyan/my-work/notebooks/L5/Kumpula-June-2016-w-metadata.txt'

# Read in the data from the file (starting at row 9):
data = pd.read_csv(fp, skiprows=8)

Basic calculations#

One of the most common things to do in pandas is to create new columns based on calculations between different variables (columns).

We can create a new column DIFF in our DataFrame by specifying the name of the column and giving it some default value (in this case the decimal number 0.0).

# Define a new column "DIFF"
data["DIFF"] = 0.0

# Check how the dataframe looks like:
data
YEARMODA TEMP MAX MIN DIFF
0 20160601 65.5 73.6 54.7 0.0
1 20160602 65.8 80.8 55.0 0.0
2 20160603 68.4 NaN 55.6 0.0
3 20160604 57.5 70.9 47.3 0.0
4 20160605 51.4 58.3 43.2 0.0
5 20160606 52.2 59.7 42.8 0.0
6 20160607 56.9 65.1 45.9 0.0
7 20160608 54.2 NaN 47.5 0.0
8 20160609 49.4 54.1 45.7 0.0
9 20160610 49.5 55.9 43.0 0.0
10 20160611 54.0 62.1 41.7 0.0
11 20160612 55.4 64.2 46.0 0.0
12 20160613 58.3 68.2 47.3 0.0
13 20160614 59.7 67.8 47.8 0.0
14 20160615 63.4 70.3 49.3 0.0
15 20160616 57.8 67.5 55.6 0.0
16 20160617 60.4 70.7 55.9 0.0
17 20160618 57.3 NaN 54.0 0.0
18 20160619 56.3 59.2 54.1 0.0
19 20160620 59.3 69.1 52.2 0.0
20 20160621 62.6 71.4 50.4 0.0
21 20160622 61.7 70.2 55.4 0.0
22 20160623 60.9 67.1 54.9 0.0
23 20160624 61.1 68.9 56.7 0.0
24 20160625 65.7 75.4 57.9 0.0
25 20160626 69.6 77.7 60.3 0.0
26 20160627 60.7 70.0 NaN 0.0
27 20160628 65.4 73.0 55.8 0.0
28 20160629 65.8 73.2 NaN 0.0
29 20160630 65.7 72.7 59.2 0.0

Let’s check the datatype of our new column:

data["DIFF"].dtypes
dtype('float64')

Okey, so we see that pandas created a new column and recognized automatically that the data type is float as we passed a 0.0 value to it.

Let’s update the column DIFF by calculating the difference between MAX and MIN columns to get an idea how much the temperatures have been varying during different days:

# Calculate max min difference
data["DIFF"] = data["MAX"] - data["MIN"]

# Check the result
data.head()
YEARMODA TEMP MAX MIN DIFF
0 20160601 65.5 73.6 54.7 18.9
1 20160602 65.8 80.8 55.0 25.8
2 20160603 68.4 NaN 55.6 NaN
3 20160604 57.5 70.9 47.3 23.6
4 20160605 51.4 58.3 43.2 15.1

The calculations were stored into the DIFF column as planned.

You can also create new columns on-the-fly at the same time when doing the calculation (the column does not have to exist before). Furthermore, it is possible to use any kind of math algebra (e.g. subtraction, addition, multiplication, division, exponentiation, etc.) when creating new columns.

We can for example convert the Fahrenheit temperatures in the TEMP column into Celsius using the formula that we have seen already many times. Let’s do that and store it in a new column called TEMP_CELSIUS.

# Create a new column and convert temp fahrenheit to celsius:
data["TEMP_CELSIUS"] = (data["TEMP"] - 32) / (9 / 5)

# Check output
data.head()
YEARMODA TEMP MAX MIN DIFF TEMP_CELSIUS
0 20160601 65.5 73.6 54.7 18.9 18.611111
1 20160602 65.8 80.8 55.0 25.8 18.777778
2 20160603 68.4 NaN 55.6 NaN 20.222222
3 20160604 57.5 70.9 47.3 23.6 14.166667
4 20160605 51.4 58.3 43.2 15.1 10.777778

Check your understanding#

Calculate the temperatures in Kelvins using the Celsius values and store the result a new column calle TEMP_KELVIN in our dataframe.

0 Kelvins is -273.15 degrees Celsius as we learned during Lesson 4.

# Solution
data["TEMP_KELVIN"] = data["TEMP_CELSIUS"] + 273.15
data.head()
YEARMODA TEMP MAX MIN DIFF TEMP_CELSIUS TEMP_KELVIN
0 20160601 65.5 73.6 54.7 18.9 18.611111 291.761111
1 20160602 65.8 80.8 55.0 25.8 18.777778 291.927778
2 20160603 68.4 NaN 55.6 NaN 20.222222 293.372222
3 20160604 57.5 70.9 47.3 23.6 14.166667 287.316667
4 20160605 51.4 58.3 43.2 15.1 10.777778 283.927778

Selecting rows and columns#

We often want to select only specific rows from a DataFrame for further analysis. There are multiple ways of selecting subsets of a pandas DataFrame. In this section we will go through the most useful tricks for selecting specific rows, columns and individual values.

Selecting several rows#

One common way of selecting only specific rows from your DataFrame is done via index slicing to extract part of the DataFrame. Slicing in pandas can be done in a similar manner as with normal Python lists, i.e. you specify the index range you want to select inside the square brackets: dataframe[start_index:stop_index].

Let’s select the first five rows and assign them to a variable called selection:

# Select first five rows of dataframe using row index values
selection = data[0:5]
selection
YEARMODA TEMP MAX MIN DIFF TEMP_CELSIUS TEMP_KELVIN
0 20160601 65.5 73.6 54.7 18.9 18.611111 291.761111
1 20160602 65.8 80.8 55.0 25.8 18.777778 291.927778
2 20160603 68.4 NaN 55.6 NaN 20.222222 293.372222
3 20160604 57.5 70.9 47.3 23.6 14.166667 287.316667
4 20160605 51.4 58.3 43.2 15.1 10.777778 283.927778

Note

Here we have selected the first five rows (index 0-4) using the integer index.

Selecting several rows and columns#

It is also possible to control which columns are chosen when selecting a subset of rows. In this case we will use pandas.DataFrame.loc which selects data based on axis labels (row labels and column labels).

Let’s select temperature values (column TEMP) from rows 0-5:

# Select temp column values on rows 0-5
selection = data.loc[0:5, "TEMP"]
selection
0    65.5
1    65.8
2    68.4
3    57.5
4    51.4
5    52.2
Name: TEMP, dtype: float64

Note

In this case, we get six rows of data (index 0-5)! We are now doing the selection based on axis labels instead of the integer index.

It is also possible to select multiple columns when using loc. Here, we select the TEMP and TEMP_CELSIUS columns from a set of rows by passing them inside a list (.loc[start_index:stop_index, list_of_columns]):

# Select columns temp and temp_celsius on rows 0-5
selection = data.loc[0:5, ["TEMP", "TEMP_CELSIUS"]]
selection
TEMP TEMP_CELSIUS
0 65.5 18.611111
1 65.8 18.777778
2 68.4 20.222222
3 57.5 14.166667
4 51.4 10.777778
5 52.2 11.222222

Check your understanding#

Find the mean temperatures (in Celsius) for the last seven days of June. Do the selection using the row index values.

# Here is the solution
data.loc[23:29, "TEMP_CELSIUS"].mean()
18.253968253968257

Selecting a single row#

You can also select an individual row from a specific position using the .loc[] indexing. Here we select all the data values using index 4 (the 5th row):

# Select one row using index
row = data.loc[4]
row
YEARMODA        2.016060e+07
TEMP            5.140000e+01
MAX             5.830000e+01
MIN             4.320000e+01
DIFF            1.510000e+01
TEMP_CELSIUS    1.077778e+01
TEMP_KELVIN     2.839278e+02
Name: 4, dtype: float64

.loc[] indexing returns the values from that position as a pd.Series where the indices are actually the column names of those variables. Hence, you can access the value of an individual column by referring to its index using the following format (both should work):

# Print one attribute from the selected row
row["TEMP"]
51.4

Selecting a single value based on row and column#

Sometimes it is enough to access a single value in a DataFrame. In this case, we can use DataFrame.at instead of Data.Frame.loc.

Let’s select the temperature (column TEMP) on the first row (index 0) of our DataFrame.

data.at[0, "TEMP"]
65.5

EXTRA: Selections by integer position#

.iloc

.loc and .at are based on the axis labels - the names of columns and rows. Axis labels can be also something else than “traditional” index values. For example, datetime is commonly used as the row index. .iloc is another indexing operator which is based on integer value indices. Using .iloc, it is possible to refer also to the columns based on their index value. For example, data.iloc[0,0] would return 20160601 in our example data frame.

See the pandas documentation for more information about indexing and selecting data.

For example, we could select TEMP and the TEMP_CELSIUS columns from a set of rows based on their index.

data.iloc[0:5:, 0:2]
YEARMODA TEMP
0 20160601 65.5
1 20160602 65.8
2 20160603 68.4
3 20160604 57.5
4 20160605 51.4

To access the value on the first row and second column (TEMP), the syntax for iloc would be:

data.iloc[0, 1]
65.5

We can also access individual rows using iloc. Let’s check out the last row of data:

data.iloc[-1]
YEARMODA        2.016063e+07
TEMP            6.570000e+01
MAX             7.270000e+01
MIN             5.920000e+01
DIFF            1.350000e+01
TEMP_CELSIUS    1.872222e+01
TEMP_KELVIN     2.918722e+02
Name: 29, dtype: float64

Filtering and updating data#

One really useful feature in pandas is the ability to easily filter and select rows based on a conditional statement. The following example shows how to select rows when the Celsius temperature has been higher than 15 degrees into variable warm_temps (warm temperatures). pandas checks if the condition is True or False for each row, and returns those rows where the condition is True:

# Check the condition
data["TEMP_CELSIUS"] > 15
0      True
1      True
2      True
3     False
4     False
5     False
6     False
7     False
8     False
9     False
10    False
11    False
12    False
13     True
14     True
15    False
16     True
17    False
18    False
19     True
20     True
21     True
22     True
23     True
24     True
25     True
26     True
27     True
28     True
29     True
Name: TEMP_CELSIUS, dtype: bool
# Select rows with temp celsius higher than 15 degrees
warm_temps = data.loc[data["TEMP_CELSIUS"] > 15]
warm_temps
YEARMODA TEMP MAX MIN DIFF TEMP_CELSIUS TEMP_KELVIN
0 20160601 65.5 73.6 54.7 18.9 18.611111 291.761111
1 20160602 65.8 80.8 55.0 25.8 18.777778 291.927778
2 20160603 68.4 NaN 55.6 NaN 20.222222 293.372222
13 20160614 59.7 67.8 47.8 20.0 15.388889 288.538889
14 20160615 63.4 70.3 49.3 21.0 17.444444 290.594444
16 20160617 60.4 70.7 55.9 14.8 15.777778 288.927778
19 20160620 59.3 69.1 52.2 16.9 15.166667 288.316667
20 20160621 62.6 71.4 50.4 21.0 17.000000 290.150000
21 20160622 61.7 70.2 55.4 14.8 16.500000 289.650000
22 20160623 60.9 67.1 54.9 12.2 16.055556 289.205556
23 20160624 61.1 68.9 56.7 12.2 16.166667 289.316667
24 20160625 65.7 75.4 57.9 17.5 18.722222 291.872222
25 20160626 69.6 77.7 60.3 17.4 20.888889 294.038889
26 20160627 60.7 70.0 NaN NaN 15.944444 289.094444
27 20160628 65.4 73.0 55.8 17.2 18.555556 291.705556
28 20160629 65.8 73.2 NaN NaN 18.777778 291.927778
29 20160630 65.7 72.7 59.2 13.5 18.722222 291.872222

It is also possible to combine multiple criteria at the same time. Here, we select temperatures above 15 degrees that were recorded on the second half of June in 2016 (i.e. YEARMODA >= 20160615). Combining multiple criteria can be done with the & operator (AND) or the | operator (OR). Notice, that it is often useful to separate the different clauses inside the parentheses ().

# Select rows with temp celsius higher than 15 degrees from late June 2016
warm_temps = data.loc[(data["TEMP_CELSIUS"] > 15) & (data["YEARMODA"] >= 20160615)]
warm_temps
YEARMODA TEMP MAX MIN DIFF TEMP_CELSIUS TEMP_KELVIN
14 20160615 63.4 70.3 49.3 21.0 17.444444 290.594444
16 20160617 60.4 70.7 55.9 14.8 15.777778 288.927778
19 20160620 59.3 69.1 52.2 16.9 15.166667 288.316667
20 20160621 62.6 71.4 50.4 21.0 17.000000 290.150000
21 20160622 61.7 70.2 55.4 14.8 16.500000 289.650000
22 20160623 60.9 67.1 54.9 12.2 16.055556 289.205556
23 20160624 61.1 68.9 56.7 12.2 16.166667 289.316667
24 20160625 65.7 75.4 57.9 17.5 18.722222 291.872222
25 20160626 69.6 77.7 60.3 17.4 20.888889 294.038889
26 20160627 60.7 70.0 NaN NaN 15.944444 289.094444
27 20160628 65.4 73.0 55.8 17.2 18.555556 291.705556
28 20160629 65.8 73.2 NaN NaN 18.777778 291.927778
29 20160630 65.7 72.7 59.2 13.5 18.722222 291.872222

Now we have a subset of our DataFrame with only rows where the TEMP_CELSIUS is above 15 and the dates in YEARMODA column start from 15th of June.

Notice, that the index values (numbers on the left) are still showing the positions from the original DataFrame. It is possible to reset the index using reset_index() function that might be useful in some cases to be able to slice the data in a similar manner as above. By default the reset_index() would make a new column called index to keep track of the previous index which might be useful in some cases but not here, so we can omit that by passing parameter drop=True.

# Reset index
warm_temps = warm_temps.reset_index(drop=True)
warm_temps
YEARMODA TEMP MAX MIN DIFF TEMP_CELSIUS TEMP_KELVIN
0 20160615 63.4 70.3 49.3 21.0 17.444444 290.594444
1 20160617 60.4 70.7 55.9 14.8 15.777778 288.927778
2 20160620 59.3 69.1 52.2 16.9 15.166667 288.316667
3 20160621 62.6 71.4 50.4 21.0 17.000000 290.150000
4 20160622 61.7 70.2 55.4 14.8 16.500000 289.650000
5 20160623 60.9 67.1 54.9 12.2 16.055556 289.205556
6 20160624 61.1 68.9 56.7 12.2 16.166667 289.316667
7 20160625 65.7 75.4 57.9 17.5 18.722222 291.872222
8 20160626 69.6 77.7 60.3 17.4 20.888889 294.038889
9 20160627 60.7 70.0 NaN NaN 15.944444 289.094444
10 20160628 65.4 73.0 55.8 17.2 18.555556 291.705556
11 20160629 65.8 73.2 NaN NaN 18.777778 291.927778
12 20160630 65.7 72.7 59.2 13.5 18.722222 291.872222

As can be seen, now the index values goes from 0 to 12.

Check your understanding#

Find the mean temperatures (in Celsius) for the last seven days of June again. This time you should select the rows based on a condition for the YEARMODA column!

# Here's the solution
data["TEMP_CELSIUS"].loc[data["YEARMODA"] >= 20160624].mean()
18.253968253968257

Deep copy

In this lesson, we have stored subsets of a DataFrame as a new variable. In some cases, we are still referring to the original data and any modifications made to the new variable might influence the original DataFrame.

If you want to be extra careful to not modify the original DataFrame, then you should take a proper copy of the data before proceeding using the .copy() method. You can read more about indexing, selecting data and deep and shallow copies in pandas documentation and in this excellent blog post.

Dealing with missing data#

As you may have noticed by now, we have several missing values for the temperature minimum, maximum, and difference columns (MIN, MAX, DIFF, and DIFF_MIN). These missing values are indicated as NaN (not a number). Having missing data in your datafile is really common situation and typically you want to deal with it somehow. Common procedures to deal with NaN values are to either remove them from the DataFrame or fill them with some value. In pandas both of these options are really easy to do.

Let’s first see how we can remove the NoData values (i.e. clean the data) using the .dropna() function. Inside the function you can pass a list of column(s) from which the NaN values should found using the subset parameter.

# Drop no data values based on the MIN column
warm_temps_clean = warm_temps.dropna(subset=["MIN"])
warm_temps_clean
YEARMODA TEMP MAX MIN DIFF TEMP_CELSIUS TEMP_KELVIN
0 20160615 63.4 70.3 49.3 21.0 17.444444 290.594444
1 20160617 60.4 70.7 55.9 14.8 15.777778 288.927778
2 20160620 59.3 69.1 52.2 16.9 15.166667 288.316667
3 20160621 62.6 71.4 50.4 21.0 17.000000 290.150000
4 20160622 61.7 70.2 55.4 14.8 16.500000 289.650000
5 20160623 60.9 67.1 54.9 12.2 16.055556 289.205556
6 20160624 61.1 68.9 56.7 12.2 16.166667 289.316667
7 20160625 65.7 75.4 57.9 17.5 18.722222 291.872222
8 20160626 69.6 77.7 60.3 17.4 20.888889 294.038889
10 20160628 65.4 73.0 55.8 17.2 18.555556 291.705556
12 20160630 65.7 72.7 59.2 13.5 18.722222 291.872222

As you can see by looking at the table above (and the change in index values), we now have a DataFrame without the NoData values.

Note

Note that we replaced the original warm_temps variable with version where no data are removed. The .dropna() function, among other pandas functions can also be applied “inplace” which means that the function updates the DataFrame object and returns None:

warm_temps.dropna(subset=['MIN'], inplace=True)

Another option is to fill the NoData with some value using the fillna() function. Here we can fill the missing values in the with value -9999. Note that we are not giving the subset parameter this time.

# Fill na values
warm_temps.fillna(-9999)
YEARMODA TEMP MAX MIN DIFF TEMP_CELSIUS TEMP_KELVIN
0 20160615 63.4 70.3 49.3 21.0 17.444444 290.594444
1 20160617 60.4 70.7 55.9 14.8 15.777778 288.927778
2 20160620 59.3 69.1 52.2 16.9 15.166667 288.316667
3 20160621 62.6 71.4 50.4 21.0 17.000000 290.150000
4 20160622 61.7 70.2 55.4 14.8 16.500000 289.650000
5 20160623 60.9 67.1 54.9 12.2 16.055556 289.205556
6 20160624 61.1 68.9 56.7 12.2 16.166667 289.316667
7 20160625 65.7 75.4 57.9 17.5 18.722222 291.872222
8 20160626 69.6 77.7 60.3 17.4 20.888889 294.038889
9 20160627 60.7 70.0 -9999.0 -9999.0 15.944444 289.094444
10 20160628 65.4 73.0 55.8 17.2 18.555556 291.705556
11 20160629 65.8 73.2 -9999.0 -9999.0 18.777778 291.927778
12 20160630 65.7 72.7 59.2 13.5 18.722222 291.872222

As a result we now have a DataFrame where NoData values are filled with the value -9999.

Warning

In many cases filling the data with a specific value is dangerous because you end up modifying the actual data, which might affect the results of your analysis. For example, in the case above we would have dramatically changed the temperature difference columns because the -9999 values not an actual temperature difference! Hence, use caution when filling missing values.

You might have to fill in no data values for the purposes of saving the data to file in a specific format. For example, some GIS software does not accept missing values. Always pay attention to potential no data values when reading in data files and doing further analysis!

Data type conversions#

There are occasions where you’ll need to convert data stored within a Series to another data type, for example, from floating point to integer.

Remember, that we already did data type conversions using the built-in Python functions such as int() or str().

For values in pandas DataFrames and Series, we can use the astype() method.

Truncating versus rounding up

Be careful with type conversions from floating point values to integers. The conversion simply drops the stuff to the right of the decimal point, so all values are rounded down to the nearest whole number. For example, 99.99 will be truncated to 99 as an integer, when it should be rounded up to 100.

Chaining the round and type conversion functions solves this issue as the .round(0).astype(int) command first rounds the values with zero decimals and then converts those values into integers.

print("Original values:")
data["TEMP"].head()
Original values:
0    65.5
1    65.8
2    68.4
3    57.5
4    51.4
Name: TEMP, dtype: float64
print("Truncated integer values:")
data["TEMP"].astype(int).head()
Truncated integer values:
0    65
1    65
2    68
3    57
4    51
Name: TEMP, dtype: int64
print("Rounded integer values:")
data["TEMP"].round(0).astype(int).head()
Rounded integer values:
0    66
1    66
2    68
3    58
4    51
Name: TEMP, dtype: int64

Looks correct now.

Unique values#

Sometimes it is useful to extract the unique values that you have in your column. We can do that by using unique() method:

# Get unique celsius values
unique = data["TEMP"].unique()
unique
array([65.5, 65.8, 68.4, 57.5, 51.4, 52.2, 56.9, 54.2, 49.4, 49.5, 54. ,
       55.4, 58.3, 59.7, 63.4, 57.8, 60.4, 57.3, 56.3, 59.3, 62.6, 61.7,
       60.9, 61.1, 65.7, 69.6, 60.7, 65.4])

As a result we get an array of unique values in that column.

Note

Sometimes if you have a long list of unique values, you don’t necessarily see all the unique values directly as IPython/Jupyter may hide them with an ellipsis .... It is, however, possible to see all those values by printing them as a list

# unique values as list
list(unique)
[65.5,
 65.8,
 68.4,
 57.5,
 51.4,
 52.2,
 56.9,
 54.2,
 49.4,
 49.5,
 54.0,
 55.4,
 58.3,
 59.7,
 63.4,
 57.8,
 60.4,
 57.3,
 56.3,
 59.3,
 62.6,
 61.7,
 60.9,
 61.1,
 65.7,
 69.6,
 60.7,
 65.4]

How many days with unique mean temperature did we have in June 2016? We can check that!

# Number of unique values
unique_temps = len(unique)
print(f"There were {unique_temps} days with unique mean temperatures in June 2016.")
There were 28 days with unique mean temperatures in June 2016.

Sorting data#

Quite often it is useful to be able to sort your data (descending/ascending) based on values in some column This can be easily done with pandas using the sort_values(by='YourColumnName') function.

Let’s first sort the values on ascending order based on the TEMP column:

# Sort dataframe, ascending
data.sort_values(by="TEMP")
YEARMODA TEMP MAX MIN DIFF TEMP_CELSIUS TEMP_KELVIN
8 20160609 49.4 54.1 45.7 8.4 9.666667 282.816667
9 20160610 49.5 55.9 43.0 12.9 9.722222 282.872222
4 20160605 51.4 58.3 43.2 15.1 10.777778 283.927778
5 20160606 52.2 59.7 42.8 16.9 11.222222 284.372222
10 20160611 54.0 62.1 41.7 20.4 12.222222 285.372222
7 20160608 54.2 NaN 47.5 NaN 12.333333 285.483333
11 20160612 55.4 64.2 46.0 18.2 13.000000 286.150000
18 20160619 56.3 59.2 54.1 5.1 13.500000 286.650000
6 20160607 56.9 65.1 45.9 19.2 13.833333 286.983333
17 20160618 57.3 NaN 54.0 NaN 14.055556 287.205556
3 20160604 57.5 70.9 47.3 23.6 14.166667 287.316667
15 20160616 57.8 67.5 55.6 11.9 14.333333 287.483333
12 20160613 58.3 68.2 47.3 20.9 14.611111 287.761111
19 20160620 59.3 69.1 52.2 16.9 15.166667 288.316667
13 20160614 59.7 67.8 47.8 20.0 15.388889 288.538889
16 20160617 60.4 70.7 55.9 14.8 15.777778 288.927778
26 20160627 60.7 70.0 NaN NaN 15.944444 289.094444
22 20160623 60.9 67.1 54.9 12.2 16.055556 289.205556
23 20160624 61.1 68.9 56.7 12.2 16.166667 289.316667
21 20160622 61.7 70.2 55.4 14.8 16.500000 289.650000
20 20160621 62.6 71.4 50.4 21.0 17.000000 290.150000
14 20160615 63.4 70.3 49.3 21.0 17.444444 290.594444
27 20160628 65.4 73.0 55.8 17.2 18.555556 291.705556
0 20160601 65.5 73.6 54.7 18.9 18.611111 291.761111
24 20160625 65.7 75.4 57.9 17.5 18.722222 291.872222
29 20160630 65.7 72.7 59.2 13.5 18.722222 291.872222
1 20160602 65.8 80.8 55.0 25.8 18.777778 291.927778
28 20160629 65.8 73.2 NaN NaN 18.777778 291.927778
2 20160603 68.4 NaN 55.6 NaN 20.222222 293.372222
25 20160626 69.6 77.7 60.3 17.4 20.888889 294.038889

Of course, it is also possible to sort them in descending order with ascending=False parameter:

# Sort dataframe, descending
data.sort_values(by="TEMP", ascending=False)
YEARMODA TEMP MAX MIN DIFF TEMP_CELSIUS TEMP_KELVIN
25 20160626 69.6 77.7 60.3 17.4 20.888889 294.038889
2 20160603 68.4 NaN 55.6 NaN 20.222222 293.372222
1 20160602 65.8 80.8 55.0 25.8 18.777778 291.927778
28 20160629 65.8 73.2 NaN NaN 18.777778 291.927778
29 20160630 65.7 72.7 59.2 13.5 18.722222 291.872222
24 20160625 65.7 75.4 57.9 17.5 18.722222 291.872222
0 20160601 65.5 73.6 54.7 18.9 18.611111 291.761111
27 20160628 65.4 73.0 55.8 17.2 18.555556 291.705556
14 20160615 63.4 70.3 49.3 21.0 17.444444 290.594444
20 20160621 62.6 71.4 50.4 21.0 17.000000 290.150000
21 20160622 61.7 70.2 55.4 14.8 16.500000 289.650000
23 20160624 61.1 68.9 56.7 12.2 16.166667 289.316667
22 20160623 60.9 67.1 54.9 12.2 16.055556 289.205556
26 20160627 60.7 70.0 NaN NaN 15.944444 289.094444
16 20160617 60.4 70.7 55.9 14.8 15.777778 288.927778
13 20160614 59.7 67.8 47.8 20.0 15.388889 288.538889
19 20160620 59.3 69.1 52.2 16.9 15.166667 288.316667
12 20160613 58.3 68.2 47.3 20.9 14.611111 287.761111
15 20160616 57.8 67.5 55.6 11.9 14.333333 287.483333
3 20160604 57.5 70.9 47.3 23.6 14.166667 287.316667
17 20160618 57.3 NaN 54.0 NaN 14.055556 287.205556
6 20160607 56.9 65.1 45.9 19.2 13.833333 286.983333
18 20160619 56.3 59.2 54.1 5.1 13.500000 286.650000
11 20160612 55.4 64.2 46.0 18.2 13.000000 286.150000
7 20160608 54.2 NaN 47.5 NaN 12.333333 285.483333
10 20160611 54.0 62.1 41.7 20.4 12.222222 285.372222
5 20160606 52.2 59.7 42.8 16.9 11.222222 284.372222
4 20160605 51.4 58.3 43.2 15.1 10.777778 283.927778
9 20160610 49.5 55.9 43.0 12.9 9.722222 282.872222
8 20160609 49.4 54.1 45.7 8.4 9.666667 282.816667

Writing data to a file#

Lastly, it is of course important to be able to write the data that you have analyzed into your computer. This is really handy in pandas as it supports many different data formats by default.

The most typical output format by far is a CSV file. The function to_csv() can be used to easily save your data in CSV format. Let’s first save the data from our data DataFrame into a file called Kumpula_temp_results_June_2016.csv.

# define output filename
output_fp = "Kumpula_temps_June_2016.csv"

# Save dataframe to csv
data.to_csv(output_fp, sep=",")

Now we have the data from our DataFrame saved to a file: Text file output1

As you can see, the first value in the datafile now contains the index value of the rows. There are also quite a lot of decimals present in the new columns that we created. Let’s deal with these and save the temperature values from warm_temps DataFrame without the index and with only 1 decimal in the floating point numbers.

# define output filename
output_fp2 = "Kumpula_temps_above15_June_2016.csv"

# Save dataframe to csv
warm_temps.to_csv(output_fp2, sep=",", index=False, float_format="%.1f")

Omitting the index can be done with the index=False parameter. Specifying how many decimals should be written can be done with the float_format parameter where the text %.1f instructs pandas to use 1 decimal in all columns when writing the data to a file (changing the value 1 to 2 would write 2 decimals, etc.)

Output after float fomatting

As a result you have a “cleaner” output file without the index column, and with only 1 decimal for floating point numbers.

That’s it for this week. We will dive deeper into data analysis with pandas in the following Lesson.