In this lesson, you will learn how update, filter and group values in pandas dataframes and how to append new data to pandas dataframes.

Data Wrangling With Pandas

Pandas dataframes offer many ways to update and select data. In this lesson, you will learn about some common data tasks that can be very useful to:

1. update the values in specific columns using functions
2. create new dataframes from selections and from grouping data
3. create new dataframes by combining existing dataframes.

Begin by downloading and importing two pandas dataframes that contain temperature (Fahrenheit) for each month in 2010 to 2013 and 2014 to 2017.

# import necessary packages
import os
import pandas as pd
import urllib.request

# set the working directory to the `earth-analytics-bootcamp` directory
# replace `jpalomino` with your username here and all paths in this lesson
os.chdir("/home/jpalomino/earth-analytics-bootcamp/")

# download .csv containing monthly temperature in 2010 to 2013 for Boulder, CO
urllib.request.urlretrieve(url = "https://ndownloader.figshare.com/files/12815417", 
                           filename = "data/monthly-temp-2010-to-2013.csv")

# download .csv containing monthly temperature in 2010 to 2013 for Boulder, CO
urllib.request.urlretrieve(url = "https://ndownloader.figshare.com/files/12815420", 
                           filename = "data/monthly-temp-2014-to-2017.csv")

# import monthly temperature values for 2010 to 2013 as a pandas dataframe
monthly_temp_2010_to_2013 = pd.read_csv("/home/jpalomino/earth-analytics-bootcamp/data/monthly-temp-2010-to-2013.csv")

# import monthly temperature values for 2014 to 2017 as a pandas dataframe
monthly_temp_2014_to_2017 = pd.read_csv("/home/jpalomino/earth-analytics-bootcamp/data/monthly-temp-2014-to-2017.csv")

Review Structure of Dataframe

Recall that with the .head() method, you can see the structure of the data without having to print the entire dataframe.

Print .head() for each dataframe, and note their structures (i.e. how the data are organized).

# print .head() for 2010 to 2013
monthly_temp_2010_to_2013.head()

# print .head() for 2014 to 2017
monthly_temp_2014_to_2017.head()

Apply Function to Column in Dataframe

Just like you can apply a function to a numpy array, you can apply a function to a specific column in a pandas dataframe using dataframe["column_name"].apply(function_name).

You specify a specific column in a pandas dataframe because you may have some columns for which the function cannot produce output.

For example, review this function to convert input values from Fahrenheit to Celsius.

# define function to convert value(s) from Fahrenheit to Celsius
def fah_to_cel(x):
    
    # convert values from Fahrenheit to Celsius using Celsius = ((Fahrenheit - 32) / 1.8)
    # can take single value, single value variable, or numpy array as input
    x = (x - 32) / 1.8
    
    # returns value(s) converted from Fahrenheit to Celsius
    return(x)    

As this function runs on numeric values, you cannot apply this function to a column with text strings.

For the monthly_temp_2010_to_2013 dataframe, you can run fah_to_cel on the column with temperature values by specifying the column name that contains these values: "Temp".

You can also replace the existing values in Temp column by setting the column equal to the output of the applied function.

# Replace values in `Temp` with the calculated values from the applied function `fah_to_cel`
monthly_temp_2010_to_2013["Temp"] = monthly_temp_2010_to_2013["Temp"].apply(fah_to_cel)

# print .head() for 2010 to 2013 to see updated values
monthly_temp_2010_to_2013.head()

Filter Values in Pandas Dataframe

Another useful data wrangling option is the ability to filter data from an existing pandas dataframe.

Filtering data is easily done using dataframe.column_name == "value". Your output will contain all rows that meet the criteria.

Again, you can also save the output to a new dataframe by creating a new variable and setting it equal to the output of the filter.

For example, you can filter for the values in the Month column that are equal to January in monthly_temp_2010_to_2013 and save the output to a new dataframe.

# # create new dataframe from filter on values in the `Month` column that are equal to `January`
jan_temp_2010_to_2013 = monthly_temp_2010_to_2013[monthly_temp_2010_to_2013.Month == "January"]

# print new dataframe
jan_temp_2010_to_2013 

You can also filter using a comparison operator on numeric values. For example, you can select all rows from the dataframe that have temperature greater than 20 degrees Celsius by filtering on the Temp column.

# create new dataframe from filter on values in the `Temp` column greater than 20 degrees Celsius
gt60_temp_2010_to_2013 = monthly_temp_2010_to_2013[monthly_temp_2010_to_2013.Temp > 20]

# print new dataframe
gt60_temp_2010_to_2013

Group Values in Pandas Dataframe

In addition to filtering data by specific values, you can also group data that share a common value, in order to summarize the grouped data by another column.

For example, in the example dataframes, you could group the data by the Month and then run the describe() method on Temp. This would run .describe() on the temperature values for each month of data as a separate group.

To do this, you can use the following syntax: dataframe.groupby(['label_column'])[["value_column"]].describe().

In this example, the label_column on which you want to group data is Month and the value_column that you want to summarize is Temp.

# create new dataframe from the .describe() output of `Temp` based on the groupby on `Month` column
month_temp_2014_2017_summary = monthly_temp_2014_to_2017.groupby(["Month"])[["Temp"]].describe()

# print new dataframe
month_temp_2014_2017_summary

In addition to running .describe() using a groupby, you can also run individual statistics such as .count() to get the number of rows belonging to a specific group (i.e. month) and other summary statistics such as .median(), .sum(), .mean(), etc, to calculate these summary statistics by a chosen group.

In the example below, .median() is being executed on Temp using a groupby on Month.

# create new dataframe from groupby calculation of .median() on `Temp`
month_temp_2014_2017_median = monthly_temp_2014_to_2017.groupby(["Month"])[["Temp"]].median()

# print new dataframe
month_temp_2014_2017_median

Note the structure of the new dataframe. Does it look different than other dataframes you have seen?

Run the .info() to see that the index is now the month names, which you recall means that Month is no longer a column.

# print .info() on new dataframe
month_temp_2014_2017_median.info()

<class 'pandas.core.frame.DataFrame'>
Index: 12 entries, April to September
Data columns (total 1 columns):
Temp    12 non-null float64
dtypes: float64(1)
memory usage: 192.0+ bytes

Reset Index After Group-by

You can easily reset the index after running a groupby using the syntax: dataframe.reset_index().

For example, running this syntax on month_temp_2014_2017_median will remove the month names as the index and recreate the column called Month.

Now the data is back to a familiar format but retains the values calculated with the groupby.

# create new dataframe with reset of index
month_temp_2014_2017_median_reset = month_temp_2014_2017_median.reset_index()

# print new dataframe
month_temp_2014_2017_median_reset

Append More Data to Pandas Dataframe

You may also want to combine multiple pandas dataframes into one pandas dataframe, which is easily accomplished using .append().

To combine dataframes, you first want to make sure that your dataframes are compatible. You want to make sure that the column and row structures match and that the values are in the same units, etc.

Check Compatibility of Dataframes

Two easy ways to check compatibility are to review the output of .info() and .head() for each dataframe.

For example, check monthly_temp_2010_to_2013 and monthly_temp_2014_to_2017 to see if you can combine them into one dataframe for 2010 to 2017.

# print .info()
monthly_temp_2010_to_2013.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 48 entries, 0 to 47
Data columns (total 6 columns):
Month             48 non-null object
Year              48 non-null int64
Temp              48 non-null float64
Season            48 non-null object
Year_Avg          48 non-null float64
Year_Avg_Label    48 non-null object
dtypes: float64(2), int64(1), object(3)
memory usage: 2.3+ KB

# print .info()
monthly_temp_2014_to_2017.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 48 entries, 0 to 47
Data columns (total 6 columns):
Month             48 non-null object
Year              48 non-null int64
Temp              48 non-null float64
Season            48 non-null object
Year_Avg          48 non-null float64
Year_Avg_Label    48 non-null object
dtypes: float64(2), int64(1), object(3)
memory usage: 2.3+ KB

# print .head()
monthly_temp_2010_to_2013.head()

# print .head()
monthly_temp_2014_to_2017.head()

Notice that while the row and column structures match, the units of monthly_temp_2014_to_2017 have not yet been converted to Celsius.

Run the the function fah_to_cel on the Temp column of monthly_temp_2014_to_2017 to convert the units.

# Replace values in `Temp` with the calculated values from the applied function `fah_to_cel`
monthly_temp_2014_to_2017["Temp"] = monthly_temp_2014_to_2017["Temp"].apply(fah_to_cel)

# print .head() to see updated values
monthly_temp_2014_to_2017.head()

Append Dataframe

Now, that the dataframes have compatible units, you can to append monthly_temp_2014_to_2017 to the bottom of monthly_temp_2010_to_2013, so that the data is in order.

To do this, use the following syntax: combined_dataframe = dataframe_1.append(dataframe_2, ignore_index=True).

You are adding the parameter ignore_index=True to automatically assign the index of the new values to match the dataframe to which they are appended.

In this example, the index of monthly_temp_2010_to_2013 is the row index, starting at 0 and ending with 47. Thus, each row appended from monthly_temp_2014_to_2017 will be assigned a new row index, starting at 48 to 95.

# append monthly_temp_2014_to_2017 to the bottom of monthly_temp_2010_to_2013
monthly_temp_2010_to_2017 = monthly_temp_2010_to_2013.append(monthly_temp_2014_to_2017, ignore_index=True)

# print .info() to see new number of rows
monthly_temp_2010_to_2017.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 96 entries, 0 to 95
Data columns (total 6 columns):
Month             96 non-null object
Year              96 non-null int64
Temp              96 non-null float64
Season            96 non-null object
Year_Avg          96 non-null float64
Year_Avg_Label    96 non-null object
dtypes: float64(2), int64(1), object(3)
memory usage: 4.6+ KB

The new combined dataframe now contains 96 entries (i.e. rows), as each input dataframe contained 48 entries.

Again, you can check that the original monthly_temp_2010_to_2013 dataframe was not changed.

# print .info() to see number of rows
monthly_temp_2010_to_2013.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 48 entries, 0 to 47
Data columns (total 6 columns):
Month             48 non-null object
Year              48 non-null int64
Temp              48 non-null float64
Season            48 non-null object
Year_Avg          48 non-null float64
Year_Avg_Label    48 non-null object
dtypes: float64(2), int64(1), object(3)
memory usage: 2.3+ KB

You have now learned the basics of data wrangling with pandas dataframes to update, filter and group values in pandas dataframes and to combine multiple pandas dataframes.