Merging Pandas DataFrames¶

This notebook will combine into one dataframe observations from two separate data sources: the NYS Mesonet, and ASOS sites located in or near New York State.

Overview¶

Often, we wish to analyze and visualize datasets from multiple sources. Outwardly, the datasets may seem similar, but they may be formatted differently; they may share some common variables, but differ in others; and/or they may use different physical units.

In this notebook, we will read in two separate sources of surface meteorological observations. One is from the New York State Mesonet; the other is from a worldwide network of surface observation sites; mostly though not always located at airports. These latter observations follow an international standard, known as METAR. While some METAR sites are staffed by humans who add additional information to the observations, they all report observations using Automated Surface Observing Systems (ASOS).

Once we read in an example set of hourly observations from both sources, we will work towards outputting a merged Pandas DataFrame. We will need to standardize different column names, and convert units so they are all in Metric.

Prerequisites¶

• Time to learn: 30 minutes

Imports¶

Read in and examine the NYSM and ASOS-derived datafiles for a particular hour¶

Also read in the NYSM site table so we can extract latitude, longitude, and elevation.

Examine the ASOS data, using a Jupyterlab feature that allows us to execute Linux commands, as if we were typing them in the terminal. Note the following differences in this dataset’s format as compared to how the NYSM data is structured:

1. Its columns are named differently from those of the NYSM
2. Its columns are separated using whitespace, not tabs
3. Missing values are denoted by -9999.0, not null strings
4. The date/time is formatted differently.

Execute a Linux command by prefacing the next code line with an !, and enclosing the Python variable that corresponds to the file we will open.¶

Open the file with Pandas; as part of the call to read_csv, specify that columns are separated by one or more blank spaces, unlike the default (comma-separated values). Treat any cell with certain values as missing.¶

Look at the first couple of rows in all three DataFrames.

Remove (drop) unwanted columns from a DataFrame¶

Our merged data file will contain only a subset of the columns from each data source. First, let’s remove the unwanted columns from the NYSM file.

Use inplace so the Dataframe object is updated

Examine what columns remain post-drop.

Now, drop any unwanted columns from the ASOS data file. The file has 35 columns, so rather than creating a long list of column names to drop, let’s create a list of only the columns we wish to keep.

Redefine the ASOS dataframe, which will now contain only the desired columns.

Examine the columns remaining in the ASOS data file

These columns represent the following:¶

1. Station ID
2. Date and Time
3. Latitude
4. Longitude
5. Elevation
6. 2-meter temperature
7. 2-meter dewpoint
8. Sea-level pressure in hPa (not all stations report this)
9. Altimeter setting in inches of mercury (not all stations report this)
10. Wind speed in knots
11. Peak wind gust in knots
12. Wind direction in degrees

Use a Python dictionary to rename columns¶

Each dataframe has varying column names. Let’s standardize by creating a dictionary that will map current column names to common (and in some cases, much shorter) names.

For each of the three Dataframes, rename the columns according to our dictionary. Then examine each Dataframe to see how they look.

Parse the time strings in the NYSM and ASOS dataframes into their corresponding datetime representations.

For the ASOS data, convert non-Metric variables to Metric and then remove the non-metric columns in the DataFrame¶

Examine the ASOS data again

Merge columns from one DataFrame into another, and then re-index.¶

Use merge to add the latitude, longitude, and elevation from the NYSM Sites dataframe to the NYSM Data one. To do this, we take a subset of the nysm_sites dataframe containing the ones we want to merge, plus the station id column, since merge requires a common column for both dataframes. Note also that merge does not have an inplace option so we just redefine the nysm_data object.

Make the NYSM and ASOS dataframes multi-indexed according to site and time. Convert the date/time from string to Datetime objects.¶

First set the indexes

Add columns to a DataFrame¶

The NYSM data does not contain dewpoint nor sea-level pressure yet. Calculate and create columns for dewpoint and sea-level pressure.

We’ll use MetPy to calculate dewpoint from temperature and relative humidity. Since MetPy’s calculation library typically requires units to be attached to variables used in the calculations, do so next.

Now that we have calculated sea-level pressure and dewpoint, let’s drop station pressure and relative humidity from the NYSM DataFrame since they do not appear in the ASOS one.

Concatenate one DataFrame into another¶

Now, the two dataframes can be merged. We’ll use the concat method to “glue” the rows from the ASOS table on to the NYSM one.

Let’s rearrange the columns into a more logical order, and eliminate any that are not common to both of the original NYSM and ASOS dataframes.

Analyze the merged DataFrame¶

Now, we have what we wanted ... one Dataframe that contains the same variables ... in the same units ... for both datasets. We can make selections on this multi-index Dataframe.

Get some general information about the DataFrame:

Select one station

Select multiple stations and one time

Use the cross-section method to select one index value and then display all values of the other index for selected columns.

Summary¶

• Pandas can efficiently combine datasets with different attributes into a single DataFrame.
• A Pandas DataFrame can have, and benefit from, multiple indexes.

Resources and References¶

1. MetPy Monday Episode 94
2. MetPy Monday Episode 97
3. MetPy Monday Episode 98
4. National Weather Service Automated Surface Observing Systems (ASOS)