Warning: This document is for the development version of Geo-Python. The main version is master.

This page was generated from source/notebooks/L5/processing-data-with-pandas.ipynb.

Processing data with Pandas

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

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

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:

fp = 'Kumpula-June-2016-w-metadata.txt'
data = pd.read_csv(fp, sep=',', skiprows=8)

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

Here, we also added the sep parameter to indicate that we are dealing with a comma-delimited text file. Comma is also the default value for the sep-parameter. Depending on your operating system and the input data, you might need to specify other separators such as ;.

Remember to always check the data after reading it in:

data.head()

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-3-304fa4ce4ebd> in <module>()
----> 1 data.head()

NameError: name 'data' is not defined

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 into our DataFrame by specifying the name of the column and giving it some default value (in this case decimal number 0.0).

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

# Print the dataframe
print(data)

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

Let’s check the datatype of our new column

# Check datatypes
data['DIFF'].dtypes

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-5-9bf175841817> in <module>()
      1 # Check datatypes
----> 2 data['DIFF'].dtypes

NameError: name 'data' is not defined

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
print(data.head())

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

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. Let’s test this by calculating the difference between minimum temperature (MIN) and the mean temperature of the day (TEMP) into a new column DIFF_MIN:

# Calculate difference between temp and min column values
data['DIFF_MIN'] = data['TEMP'] - data['MIN']

# Print the dataframe
print(data)

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

As you can see, now we created directly a new column with the calculation. In a similar manner, you can do calculations using as many columns as you need and using any kind of math algebra (e.g. subtracttion, addition, multiplication, division, exponentiation, etc.).

We can for example convert the Fahrenheit temperatures in TEMP column into Celsius using the formula that we have seen already many times:

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

#Check output
print(data.head())

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

TASK

Calculate Kelvin values based on the Celsius values and store the result a new column TEMP_KELVIN in our dataframe.

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

# Add column "TEMP_KELVIN"

Selecting rows and columns

One quite common procedure in programming that you want to select only specific rows from your data and possibly apply some operations into those rows only.

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 index range you want to select inside the square brackets selection = 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 index values
selection = data[0:5]

print(selection)

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

Note: here selected the first five rows (index 0-4) using 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) on rows 0-5:

# Select temp column values on rows 0-5
selection = data.loc[0:5, 'TEMP']

print(selection)

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

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

It is also possible to select multiple columns when using loc. Here, we select TEMP and the 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']]

print(selection)

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

Selecting a single row:

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

# Select one row using index
selection = data.loc[4]

print(selection)

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

.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 following format (both should work):

#Print one attribute from the selected row
print(selection['TEMP'])

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-14-84f922a0e730> in <module>()
      1 #Print one attribute from the selected row
----> 2 print(selection['TEMP'])

NameError: name 'selection' is not defined

TASK

Find the mean temperatures (in Celsius) for the last seven days of June. For now, you should do the selection using the row index values.

# Mean temperature for the last seven days of June (use loc indexing to select the correct rows):

Selecting a single value based on row and column:

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

Let’s select the temprerature (column TEMP) on the first fow (index 0) of our DataFrame:

data.at[0, "TEMP"]

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-16-29d49179901e> in <module>()
----> 1 data.at[0, "TEMP"]

NameError: name 'data' is not defined

EXTRA: Selections by integer position

.iloc

.loc and .at are based on the axis labels - the names of columns and rows. .iloc is another indexing operator which is based on integer values.

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

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

data.iloc[0:5:,0:2]

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-17-7394ea37e436> in <module>()
----> 1 data.iloc[0:5:,0:2]

NameError: name 'data' is not defined

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

data.iloc[0,1]

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-18-7ad24eab008e> in <module>()
----> 1 data.iloc[0,1]

NameError: name 'data' is not defined

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

data.iloc[-1]

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-19-9f8eff24926e> in <module>()
----> 1 data.iloc[-1]

NameError: name 'data' is not defined

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:

# Select rows with temp celsius higher than 15 degrees
warm_temps = data.loc[data['TEMP_CELSIUS'] > 15]
print(warm_temps)

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

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 & operator (AND) or | 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)]
print(warm_temps)

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

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 on the previous index which might be useful in some cases but here not, so we can omit that by passing parameter drop=True.

# Reset index
warm_temps = warm_temps.reset_index(drop=True)
print(warm_temps)

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

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

TASK

Find the mean temperatures (in Celsius) for the last seven days of June. Now, select the rows based on a condition for the YEARMODA column!

# Mean temperature for the last seven days of June (use a conditional statement to select the correct rows):

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 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.dropna(subset=['MIN'])

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-24-57260f9934a4> in <module>()
      1 # Drop no data values based on the MIN column
----> 2 warm_temps.dropna(subset=['MIN'])

NameError: name 'warm_temps' is not defined

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

Be aware that we have not modified the warm_temps DataFrame in the example above. The table that is displayed is the returned value when the dropna() method is used. In order to update the values in the warm_temps DataFrame you would need to use code like the example below.

warm_temps = warm_temps.dropna(subset=['MIN'])

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)

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-25-705550ea4297> in <module>()
      1 # Fill na values
----> 2 warm_temps.fillna(-9999)

NameError: name 'warm_temps' is not defined

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 example, when working with GIS data. 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:")
print(data['TEMP'].head())

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

print("Truncated integer values:")
print(data['TEMP'].astype(int).head())

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

print("Rounded integer values:")
print(data['TEMP'].round(0).astype(int).head())

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

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

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

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 elipsis .... It is, however, possible to see all those values by printing them as a list:

# unique values as list
print(list(unique))

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-30-2dbb597f68a8> in <module>()
      1 # unique values as list
----> 2 print(list(unique))

NameError: name 'unique' is not defined

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("There were", unique_temps, "days with unique mean temperatures in June 2016.")

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

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 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')

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-32-468b6dc84d32> in <module>()
      1 # Sort dataframe, ascending
----> 2 data.sort_values(by='TEMP')

NameError: name 'data' is not defined

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)

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-33-7d330a2f642c> in <module>()
      1 # Sort dataframe, descending
----> 2 data.sort_values(by='TEMP', ascending=False)

NameError: name 'data' is not defined

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 CSV file. 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=',')

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

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

As you can see, the first value in the datafile contains now the index value of the rows. There are also quite many 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.

output_fp2 = "Kumpula_temps_above15_June_2016.csv"
warm_temps.to_csv(output_fp2, sep=',', index=False, float_format="%.1f")

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/opt/python/3.8.0/lib/python3.8/codeop.py in __call__(self, source, filename, symbol)
    131
    132     def __call__(self, source, filename, symbol):
--> 133         codeob = compile(source, filename, symbol, self.flags, 1)
    134         for feature in _features:
    135             if codeob.co_flags & feature.compiler_flag:

TypeError: required field "type_ignores" missing from Module

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

As a results 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. Next, you can continue with Exercise 5 (for Pandas).