<jupyter_start><jupyter_text>Data Wrangling with Pandas**Author**: <NAME> - Missouri University of Science and TechnologyThis notebook provides an overview of data manipulation using Pandas, a Python package that provides similar functionality to spreadsheet programs like Excel or Google Sheets.You can read more details about Pandas __[here](https://pandas.pydata.org/pandas-docs/stable/getting_started/index.html)__In this notebook we will briefly demonstrate the following capabilities of pandas:- Reading data from comma and space-delimited files into pandas dataframes- Manipulating data in a dataframe- Writing dataframes to files    Terminology:    - *dataframe*: The equivalent of a spreadsheet in Python.    - *Series*: A single column of a Pandas dataframe; equivalent to a column in a spreadsheet  - *tropospheric zenith delay*: The precise atmospheric delay satellite signals experience when propagating through the troposphere.  Estimated time to run notebook: 15 minutes  Table of Contents: [**Overview of the pandas package**](overview)  [1. Reading data from files](reading-data)  [2. Manipulating data in dataframes](manip-data)  [3. Writing data to files](write-data)    Prep: Initial setup of the notebook Below we set up the directory structure for this notebook exercise. In addition, we load the required modules into our python environment using the **`import`** command.    You can customize the location of your home and working directory when running this notebook by modifying the cell below.<jupyter_code>import numpy as np
import os
import matplotlib.pyplot as plt
import pandas as pd

## Defining the home and data directories
tutorial_home_dir = os.path.abspath(os.getcwd())
work_dir = os.path.abspath(os.getcwd())
print("Tutorial directory: ", tutorial_home_dir)
print("Work directory: ", work_dir)<jupyter_output>Tutorial directory:  /Users/jeremym/software/RAiDER-docs/notebooks/Pandas_tutorial
Work directory:  /Users/jeremym/software/RAiDER-docs/notebooks/Pandas_tutorial<jupyter_text>Overview of the Pandas Package  Reading data from files<jupyter_code># Let's start by loading a simple .csv dataset into a pandas dataframe 
df = pd.read_csv('data/sample_data.csv')
df.head()
# It's also possible to read space-delimited and excel files using pandas 
# df = pd.read_csv('space_delimited_file.txt', delim_whitespace=True)
# df = pd.read_excel('excel_file.xlsx') # You may need to install xlrd or openpyxl to read excel files<jupyter_output><empty_output><jupyter_text>Manipulating data in pandas<jupyter_code># Pandas uses an "index" to keep track of rows. By default it uses integers
print(df.index)
# You can change the index to a column in the dataframe, for example a datetime
df = df.set_index('Datetime')
df.head()
# You can reset the index as well
df = df.reset_index()
df.head()
# By default Pandas reads datetimes from files as strings.
# we can convert them to actual Python datetimes 
df['Datetime'] = pd.to_datetime(df['Datetime'])
df = df.set_index('Datetime')
df.head()
# We can get a subset of the data using the column name
df_jme2 = df[df['ID'] == 'JME2']
df_jme2.head()
# It's possible to plot data directly using Pandas
df_jme2['ZTD'].plot()
# We can perform operations on columns:
'Station_' + df['ID'] 
# Or mathematical operations:
noisy = np.nanmean(df['ZTD']) + np.nanstd(df['ZTD'])*np.random.randn(len(df))
print(noisy)
# We can assign the output of an operation to a new column
df['ZTD_noisy'] = noisy
# And we can take operations of several columns
df['ZTD_diff'] = df['ZTD'] - df['ZTD_noisy']
# We can define functions and then call them as operators on a dataframe column or index
def dt2fracYear(date):
    import datetime as dt
    import time

    def sinceEpoch(date): # returns seconds since epoch
        return time.mktime(date.timetuple())
    s = sinceEpoch

    # check that the object is a datetime
    try:
        year = date.year
    except AttributeError:
        date = numpyDT64ToDatetime(date)
        year = date.year

    startOfThisYear = dt.datetime(year=year, month=1, day=1)
    startOfNextYear = dt.datetime(year=year+1, month=1, day=1)

    yearElapsed = s(date) - s(startOfThisYear)
    yearDuration = s(startOfNextYear) - s(startOfThisYear)
    fraction = yearElapsed/yearDuration
    date_frac = date.year + fraction

    return date_frac

def numpyDT64ToDatetime(dt64):
    '''
    Convert a numpy datetime64 object to a python datetime object
    '''
    import datetime
    import numpy as np

    unix_epoch = np.datetime64(0, 's')
    one_second = np.timedelta64(1, 's')
    seconds_since_epoch = (dt64 - unix_epoch) / one_second
    dt = datetime.datetime.utcfromtimestamp(seconds_since_epoch)
    return dt
# We can assign the index to be a column, operate on it, and then drop the added column
df['dt'] = df.index
df['fracYear'] = df['dt'].apply(lambda x: dt2fracYear(x))
df.drop('dt', axis=1)
# We can look at summary statistics
df.describe()
# We can group variables as needed
station_stats = df.value_counts('ID')
station_stats.head()
# We can create different plots, depending on the type of variable we are interested in
df['ZTD'].plot.hist(bins=100)
plt.xlabel('ZTD (m)')
# See the API documentation for keyword arguments, etc.
df.plot.scatter(x='ZTD', y='ZTD_noisy', s=1, c='k')<jupyter_output><empty_output><jupyter_text>Writing dataframes to a file Pandas can write to various file formats, including xcel, JSON, HTML, HDF5, STATA, SQL, and pickle formats. Using the __[Scipy.io](https://docs.scipy.org/doc/scipy/reference/io.html)__ module, you can also export data from Python to a .mat file that can be read in Matlab. You can the Pandas I/O documentation __[here](https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html)__.<jupyter_code># We can export a dataframe to a .csv file
df_jme2.to_csv(os.path.join(work_dir, 'Station_JME2_ZTD.csv'), index = False)
# export to a .mat file by first converting the dataframe to a dictionary
import scipy.io as sio
sio.savemat('Station_JME2_ZTD.mat', {'data': df_jme2.to_dict()})<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09616334595751688, 'fraction_numerical': 0.006915857072287173, 'mean_word_length': 4.81244019138756, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '492742', 'license': 'Apache-2.0', 'n_tokens_mistral': 1938, 'n_tokens_neox': 1868, 'n_words': 668, 'path': 'notebooks/Pandas_tutorial/Pandas_tutorial.ipynb', 'repo_name': 'sssangha/RAiDER-docs', 'size': '6080'}