> ## Documentation Index
> Fetch the complete documentation index at: https://anaconda.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Data preparation

Data Science & AI Workbench supports data preparation using numeric libraries such as
NumPy, SciPy, and Pandas.

These examples use this small data file `vendors.csv`:

```
Vendor Number,Vendor Name,Month,Day,Year,Active,Open Orders,2015,2016,Percent Growth
"104.0",ACME Inc,2,15,2014,"Y",200,"$45,000.00",$54000.00,20.00%
205,Apogee LTD,8,12,2015,"Y",150,"$29,000.00","$30,450.00",5.00%
143,Zenith Co,4,5,2014,"Y",290,"$18,000.00",$23400.00,30.00%
166,Hollerith Propulsion,9,25,2015,"Y",180,"$48,000.00",$48960.00,2.00%
180,Airtek Industrial,8,2,2014,"N",Closed,"$23,000.00",$17250.00,-25.00%
```

The columns are the vendor ID number, vendor name, month day and year of first
purchase from the vendor, whether the account is currently active, the number of
open orders, purchases in 2015 and 2016, and percent growth in orders from 2015
to 2016.

## Converting data types

Computers handle many types of data, including integer numbers such as 365,
floating point numbers such as 365.2425, strings such as “ACME Inc”, and more.

An operation such as division may work for integers and floating point numbers,
but produce an error if used on strings.

Often data libraries such as pandas will automatically use the correct types,
but they do provide ways to correct and change the types when needed. For
example, you might want to convert between an integer such as 25, the floating
point number 25.0, and strings such as “25”, “25.0”, or “\$25.00”.

Pandas data types or `dtypes` correspond to similar Python types.

Strings are called `str` in Python and `object` in pandas.

Integers are called `int` in Python and `int64` in pandas, indicating that
pandas stores integers as 64-bit numbers.

Floating point numbers are called `float` in Python and `float64` in pandas,
also indicating that they are stored with 64 bits.

A boolean value, named for logician George Boole, can be either True or False.
These are called `bool` in Python and `bool` in pandas.

Pandas includes some data types with no corresponding native Python type:
`datetime64` for date and time values, `timedelta[ns]` for storing the
difference between two times as a number of nanoseconds, and `category` where
each item is one of a list of strings.

Here we import the vendor data file and show the dtypes:

```py theme={null}
import pandas as pd
import numpy as np
df = pd.read_csv('vendors.csv')
df.dtypes
```

```
Vendor Number     float64
Vendor Name        object
Month               int64
Day                 int64
Year                int64
Active             object
Open Orders        object
2015               object
2016               object
Percent Growth     object
dtype: object
```

Try adding the 2015 and 2016 sales:

```py theme={null}
df['2015']+df['2016']
```

```
0     $45,000.00$54000.00
1    $29,000.00$30,450.00
2     $18,000.00$23400.00
3     $48,000.00$48960.00
4     $23,000.00$17250.00
dtype: object
```

These columns were stored as the type “object”, and concatenated as strings, not
added as numbers.

Examine more information about the DataFrame:

```py theme={null}
df.info()
```

```
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5 entries, 0 to 4
Data columns (total 10 columns):
Vendor Number     5 non-null float64
Vendor Name       5 non-null object
Month             5 non-null int64
Day               5 non-null int64
Year              5 non-null int64
Active            5 non-null object
Open Orders       5 non-null object
2015              5 non-null object
2016              5 non-null object
Percent Growth    5 non-null object
dtypes: float64(1), int64(3), object(6)
memory usage: 480.0+ bytes
```

Vendor Number is a `float` and not an `int`. 2015 and 2016 sales, percent
growth, and open orders are stored as objects and not numbers. The month, day,
and year values should be converted to `datetime64`, and the active column
should be converted to a boolean.

The data can be converted with the `astype()` function, custom functions, or
pandas functions such as `to_numeric()` or `to_datetime()`.

### astype()

The `astype()` function can convert the Vendor Number column to `int`:

```py theme={null}
df['Vendor Number'].astype('int')
```

```
0    104
1    205
2    143
3    166
4    180
Name: Vendor Number, dtype: int64
```

`astype()` returns a copy, so an assignment statement will convert the
original data. This can be checked by showing the `dtypes`.

```py theme={null}
df['Vendor Number'] = df['Vendor Number'].astype('int')
df.dtypes
```

```
Vendor Number      int64
Vendor Name       object
Month              int64
Day                int64
Year               int64
Active            object
Open Orders       object
2015              object
2016              object
Percent Growth    object
dtype: object
```

However, trying to convert the 2015 column to a `float` or the Open Orders
column to an `int` returns an error.

```py theme={null}
df['2015'].astype('float')
```

```
ValueError: could not convert string to float: '$23,000.00'
```

```py theme={null}
df['Open Orders'].astype('int')
```

```py theme={null}
ValueError: invalid literal for int() with base 10: 'Closed'
```

Even worse, trying to convert the Active column to a `bool` completes with no
errors, but converts both Y and N values to `True`.

```py theme={null}
df['Active'].astype('bool')
```

```py theme={null}
0    True
1    True
2    True
3    True
4    True
Name: Active, dtype: bool
```

`astype()` works if the data is clean and can be interpreted simply as a
number, or if you want to convert a number to a string. Other conversions
require custom functions or pandas functions such as `to_numeric()` or
`to_datetime()`.

### Custom conversion functions

This small custom function converts a currency string like the ones in the 2015
column to a `float` by first removing the comma (`,`) and dollar sign (`$`)
characters.

```py theme={null}
def currency_to_float(a):
    return float(a.replace(',','').replace('$',''))
```

Test the function on the 2015 column with the `apply()` function:

```py theme={null}
df['2015'].apply(currency_to_float)
```

```
0    45000.0
1    29000.0
2    18000.0
3    48000.0
4    23000.0
Name: 2015, dtype: float64
```

Convert the 2015 and 2016 columns and show the `dtypes`:

```py theme={null}
df['2015'] = df['2015'].apply(currency_to_float)
df['2016'] = df['2016'].apply(currency_to_float)
df.dtypes
```

```
Vendor Number       int64
Vendor Name        object
Month               int64
Day                 int64
Year                int64
Active             object
Open Orders        object
2015              float64
2016              float64
Percent Growth     object
dtype: object
```

Convert the Percent Growth column:

```py theme={null}
def percent_to_float(a):
    return float(a.replace('%',''))/100
df['Percent Growth'].apply(percent_to_float)
```

```
0    0.20
1    0.05
2    0.30
3    0.02
4   -0.25
Name: Percent Growth, dtype: float64
```

```py theme={null}
df['Percent Growth'] = df['Percent Growth'].apply(percent_to_float)
df.dtypes
```

```
Vendor Number       int64
Vendor Name        object
Month               int64
Day                 int64
Year                int64
Active             object
Open Orders        object
2015              float64
2016              float64
Percent Growth    float64
dtype: object
```

NumPy’s `np.where()` function is a good way to convert the Active column to
`bool`. This code converts “Y” values to `True` and all other values to
`False`, then shows the `dtypes`:

```py theme={null}
np.where(df["Active"] == "Y", True, False)
```

```py theme={null}
array([ True,  True,  True,  True, False])
```

```py theme={null}
df["Active"] = np.where(df["Active"] == "Y", True, False)
df.dtypes
```

```
Vendor Number       int64
Vendor Name        object
Month               int64
Day                 int64
Year                int64
Active               bool
Open Orders        object
2015              float64
2016              float64
Percent Growth    float64
dtype: object
```

### Pandas helper functions

The Open Orders column has several integers, but one string. Using `astype()`
on this column would produce an error, but the `pd.to_numeric()` function
built in to pandas will convert the numeric values to numbers and any other
values to the “not a number” or “NaN” value built in to the floating point
number standard:

```py theme={null}
pd.to_numeric(df['Open Orders'], errors='coerce')
```

```
0    200.0
1    150.0
2    290.0
3    180.0
4      NaN
Name: Open Orders, dtype: float64
```

In this case, a non-numeric value in this field indicates that there are zero
open orders, so we can convert NaN values to zero with the function
`fillna()`:

```py theme={null}
pd.to_numeric(df['Open Orders'], errors='coerce').fillna(0)
```

```
0    200.0
1    150.0
2    290.0
3    180.0
4      0.0
Name: Open Orders, dtype: float64
```

Similarly, the `pd.to_datetime()` function built in to pandas can convert the
Month Day and Year columns to `datetime64[ns]`:

```py theme={null}
pd.to_datetime(df[['Month', 'Day', 'Year']])
```

```
0   2014-02-15
1   2015-08-12
2   2014-04-05
3   2015-09-25
4   2014-08-02
dtype: datetime64[ns]
```

Use these functions to change the DataFrame, then show the `dtypes`:

```py theme={null}
df['Open Orders'] = pd.to_numeric(df['Open Orders'], errors='coerce').fillna(0)
df['First Purchase Date'] = pd.to_datetime(df[['Month', 'Day', 'Year']])
df.dtypes
```

```
Vendor Number                   int64
Vendor Name                    object
Month                           int64
Day                             int64
Year                            int64
Active                           bool
Open Orders                   float64
2015                          float64
2016                          float64
Percent Growth                float64
First Purchase Date    datetime64[ns]
dtype: object
```

### Converting data as it is read

You can apply `dtype` and `converters` in the `pd.read_csv()` function.
Defining `dtype` is like performing `astype()` on the data.

A `dtype` or a `converter` can only be applied once to a specified column.
If you try to apply both to the same column, the `dtype` is skipped.

After converting as much of the data as possible in `pd.read_csv()`, use code
similar to the previous examples to convert the rest.

```py theme={null}
df2 = pd.read_csv('vendors.csv',
                  dtype={'Vendor Number': 'int'},
                  converters={'2015': currency_to_float,
                              '2016': currency_to_float,
                              'Percent Growth': percent_to_float})
df2["Active"] = np.where(df2["Active"] == "Y", True, False)
df2['Open Orders'] = pd.to_numeric(df2['Open Orders'], errors='coerce').fillna(0)
df2['First Purchase Date'] = pd.to_datetime(df2[['Month', 'Day', 'Year']])
df2

```

```
   Vendor Number           Vendor Name  Month  Day  Year  Active  Open Orders     2015     2016  Percent Growth First Purchase Date
0            104              ACME Inc      2   15  2014    True        200.0  45000.0  54000.0            0.20          2014-02-15
1            205            Apogee LTD      8   12  2015    True        150.0  29000.0  30450.0            0.05          2015-08-12
2            143             Zenith Co      4    5  2014    True        290.0  18000.0  23400.0            0.30          2014-04-05
3            166  Hollerith Propulsion      9   25  2015    True        180.0  48000.0  48960.0            0.02          2015-09-25
4            180     Airtek Industrial      8    2  2014   False          0.0  23000.0  17250.0           -0.25          2014-08-02

```

```py theme={null}
df2.dtypes

```

```
Vendor Number                   int64
Vendor Name                    object
Month                           int64
Day                             int64
Year                            int64
Active                           bool
Open Orders                   float64
2015                          float64
2016                          float64
Percent Growth                float64
First Purchase Date    datetime64[ns]
dtype: object
```

We thank [https://pbpython.com/](https://pbpython.com/) for providing data preparation
examples that inspired these examples.

## Merging and joining data sets

You can use pandas to merge DataFrames:

```py theme={null}
left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                     'A': ['A0', 'A1', 'A2', 'A3'],
                     'B': ['B0', 'B1', 'B2', 'B3']})
right = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                      'C': ['C0', 'C1', 'C2', 'C3'],
                      'D': ['D0', 'D1', 'D2', 'D3']})
pd.merge(left, right, on='key')
```

```
  key   A   B   C   D
0  K0  A0  B0  C0  D0
1  K1  A1  B1  C1  D1
2  K2  A2  B2  C2  D2
3  K3  A3  B3  C3  D3
```

The available merge methods are `left` to use keys from the left frame only,
`right` to use keys from the right frame only, `outer` to use the union of
keys from both frames, and the default `inner` to use the intersection of keys
from both frames.

This merge using the default inner join omits key combinations found
in only one of the source DataFrames:

```py theme={null}
left = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],
                     'key2': ['K0', 'K1', 'K0', 'K1'],
                     'A': ['A0', 'A1', 'A2', 'A3'],
                     'B': ['B0', 'B1', 'B2', 'B3']})
right = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],
                      'key2': ['K0', 'K0', 'K0', 'K0'],
                      'C': ['C0', 'C1', 'C2', 'C3'],
                      'D': ['D0', 'D1', 'D2', 'D3']})
pd.merge(left, right, on=['key1', 'key2'])
```

```
  key1 key2   A   B   C   D
0   K0   K0  A0  B0  C0  D0
1   K1   K0  A2  B2  C1  D1
2   K1   K0  A2  B2  C2  D2
```

This example omits the rows with `key1` and `key2` set to `K0, K1`, `K2,
K1`, or `K2, K0`.

Joins also copy information when necessary. The left DataFrame had one row with
the keys set to `K1, K0` and the right DataFrame had two. The output DataFrame
has two, with the information from the left DataFrame copied into both rows.

The next example shows the results of a left, right, and outer merge on the same
inputs. Empty cells are filled in with NaN values.

```py theme={null}
pd.merge(left, right, how='left', on=['key1', 'key2'])
```

```
  key1 key2   A   B    C    D
0   K0   K0  A0  B0   C0   D0
1   K0   K1  A1  B1  NaN  NaN
2   K1   K0  A2  B2   C1   D1
3   K1   K0  A2  B2   C2   D2
4   K2   K1  A3  B3  NaN  NaN
```

```py theme={null}
pd.merge(left, right, how='right', on=['key1', 'key2'])
```

```
  key1 key2    A    B   C   D
0   K0   K0   A0   B0  C0  D0
1   K1   K0   A2   B2  C1  D1
2   K1   K0   A2   B2  C2  D2
3   K2   K0  NaN  NaN  C3  D3
```

```py theme={null}
pd.merge(left, right, how='outer', on=['key1', 'key2'])
```

```
  key1 key2    A    B    C    D
0   K0   K0   A0   B0   C0   D0
1   K0   K1   A1   B1  NaN  NaN
2   K1   K0   A2   B2   C1   D1
3   K1   K0   A2   B2   C2   D2
4   K2   K1   A3   B3  NaN  NaN
5   K2   K0  NaN  NaN   C3   D3
```

If a key combination appears more than once in both tables, the output will
contain the Cartesian product of the associated data.

In this small example a key that appears twice in the left frame and three times
in the right frame produces six rows in the output frame.

```py theme={null}
left = pd.DataFrame({'A' : [1,2], 'B' : [2, 2]})
right = pd.DataFrame({'A' : [4,5,6], 'B': [2,2,2]})
pd.merge(left, right, on='B', how='outer')
```

```
   A_x  B  A_y
0    1  2    4
1    1  2    5
2    1  2    6
3    2  2    4
4    2  2    5
5    2  2    6
```

To prevent very large outputs and memory overflow, manage duplicate values in
keys before joining large DataFrames.

While merging uses one or more columns as keys, joining uses the indexes, also
known as row labels.

Join can also perform left, right, inner, and outer merges, and defaults to
left.

```py theme={null}
left = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
                     'B': ['B0', 'B1', 'B2']},
                    index=['K0', 'K1', 'K2'])
right = pd.DataFrame({'C': ['C0', 'C2', 'C3'],
                      'D': ['D0', 'D2', 'D3']},
                     index=['K0', 'K2', 'K3'])
left.join(right)
```

```
     A   B    C    D
K0  A0  B0   C0   D0
K1  A1  B1  NaN  NaN
K2  A2  B2   C2   D2
```

```py theme={null}
left.join(right, how='outer')
```

```py theme={null}
      A    B    C    D
K0   A0   B0   C0   D0
K1   A1   B1  NaN  NaN
K2   A2   B2   C2   D2
K3  NaN  NaN   C3   D3
```

```py theme={null}
left.join(right, how='inner')
```

```py theme={null}
     A   B   C   D
K0  A0  B0  C0  D0
K2  A2  B2  C2  D2
```

This is equivalent to using merge with arguments instructing it to use the
indexes:

```py theme={null}
pd.merge(left, right, left_index=True, right_index=True, how='inner')
```

```
     A   B   C   D
K0  A0  B0  C0  D0
K2  A2  B2  C2  D2
```

You can join a frame indexed by a join key to a frame where the key is a column:

```py theme={null}
left = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],
                     'B': ['B0', 'B1', 'B2', 'B3'],
                     'key': ['K0', 'K1', 'K0', 'K1']})
right = pd.DataFrame({'C': ['C0', 'C1'],
                      'D': ['D0', 'D1']},
                     index=['K0', 'K1'])
left.join(right, on='key')
```

```
    A   B key   C   D
0  A0  B0  K0  C0  D0
1  A1  B1  K1  C1  D1
2  A2  B2  K0  C0  D0
3  A3  B3  K1  C1  D1
```

You can join on multiple keys if the passed DataFrame has a `MultiIndex`:

```py theme={null}
left = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],
                     'B': ['B0', 'B1', 'B2', 'B3'],
                     'key1': ['K0', 'K0', 'K1', 'K2'],
                     'key2': ['K0', 'K1', 'K0', 'K1']})
index = pd.MultiIndex.from_tuples([('K0', 'K0'), ('K1', 'K0'),
                                   ('K2', 'K0'), ('K2', 'K1')])
right = pd.DataFrame({'C': ['C0', 'C1', 'C2', 'C3'],
                      'D': ['D0', 'D1', 'D2', 'D3']},
                     index=index)
right
```

```
        C   D
K0 K0  C0  D0
K1 K0  C1  D1
K2 K0  C2  D2
   K1  C3  D3
```

```py theme={null}
left.join(right, on=['key1', 'key2'])
```

```
    A   B key1 key2    C    D
0  A0  B0   K0   K0   C0   D0
1  A1  B1   K0   K1  NaN  NaN
2  A2  B2   K1   K0   C1   D1
3  A3  B3   K2   K1   C3   D3
```

Note that this defaulted to a left join, but other types are also available:

```py theme={null}
left.join(right, on=['key1', 'key2'], how='inner')

```

```
    A   B key1 key2   C   D
0  A0  B0   K0   K0  C0  D0
2  A2  B2   K1   K0  C1  D1
3  A3  B3   K2   K1  C3  D3

```

For more information, including examples of using merge to join a single index
to a multi-index or to join two multi-indexes, see the [pandas documentation on
merging](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.merge.html).

When column names in the input frames overlap, pandas appends suffixes to
disambiguate them. These default to `_x` and `_y` but you can customize
them:

```py theme={null}
left = pd.DataFrame({'k': ['K0', 'K1', 'K2'], 'v': [1, 2, 3]})
right = pd.DataFrame({'k': ['K0', 'K0', 'K3'], 'v': [4, 5, 6]})
pd.merge(left, right, on='k')

```

```
    k  v_x  v_y
0  K0    1    4
1  K0    1    5

```

```py theme={null}
pd.merge(left, right, on='k', suffixes=['_l', '_r'])

```

```
    k  v_l  v_r
0  K0    1    4
1  K0    1    5

```

Join has similar arguments `lsuffix` and `rsuffix`.

```py theme={null}
left = left.set_index('k')
right = right.set_index('k')
left.join(right, lsuffix='_l', rsuffix='_r')

```

```
    v_l  v_r
k
K0    1  4.0
K0    1  5.0
K1    2  NaN
K2    3  NaN

```

You can join a list or tuple of DataFrames on their indexes:

```py theme={null}
right2 = pd.DataFrame({'v': [7, 8, 9]}, index=['K1', 'K1', 'K2'])
left.join([right, right2])

```

```
    v_x  v_y    v
K0    1  4.0  NaN
K0    1  5.0  NaN
K1    2  NaN  7.0
K1    2  NaN  8.0
K2    3  NaN  9.0

```

If you have two frames with similar indices and want to fill in missing values
in the left frame with values from the right frame, use the `combine_first()`
method:

```py theme={null}
df1 = pd.DataFrame([[np.nan, 3., 5.],
                    [-4.6, np.nan, np.nan],
                    [np.nan, 7., np.nan]])
df2 = pd.DataFrame([[-42.6, np.nan, -8.2],
                    [-5., 1.6, 4]],
                   index=[1, 2])
df1.combine_first(df2)
```

```
     0    1    2
0  NaN  3.0  5.0
1 -4.6  NaN -8.2
2 -5.0  7.0  4.0
```

The method `update()` overwrites values in a frame with values from another
frame:

```py theme={null}
df1.update(df2)
df1
```

```
      0    1    2
0   NaN  3.0  5.0
1 -42.6  NaN -8.2
2  -5.0  1.6  4.0
```

The [pandas documentation on merging](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.merge.html) has more
information, including examples of combining time series and other ordered data,
with options to fill and interpolate missing data.

We thank the pandas documentation for many of these examples.

## Filtering data

This example uses a vendors DataFrame similar to the one we used above:

```py theme={null}
import pandas as pd
import numpy as np
df = pd.DataFrame({'VendorNumber': [104, 205, 143, 166, 180],
               'VendorName': ['ACME Inc', 'Apogee LTD', 'Zenith Co', 'Hollerith Propulsion', 'Airtek Industrial'],
               'Active': [True, True, True, True, False],
               'OpenOrders': [200, 150, 290, 180, 0],
               'Purchases2015': [45000.0, 29000.0, 18000.0, 48000.0, 23000.0],
               'Purchases2016': [54000.0, 30450.0, 23400.0, 48960.0, 17250.0],
               'PercentGrowth': [0.20, 0.05, 0.30, 0.02, -0.25],
               'FirstPurchaseDate': ['2014-02-15', '2015-08-12', '2014-04-05', '2015-09-25', '2014-08-02']})
df['FirstPurchaseDate'] = df['FirstPurchaseDate'].astype('datetime64[ns]')
df
```

```
   VendorNumber            VendorName  Active  OpenOrders  Purchases2015  Purchases2016  PercentGrowth FirstPurchaseDate
0           104              ACME Inc    True         200        45000.0        54000.0           0.20        2014-02-15
1           205            Apogee LTD    True         150        29000.0        30450.0           0.05        2015-08-12
2           143             Zenith Co    True         290        18000.0        23400.0           0.30        2014-04-05
3           166  Hollerith Propulsion    True         180        48000.0        48960.0           0.02        2015-09-25

```

To filter only certain rows from a DataFrame, call the `query` method with a
boolean expression based on the column names.

```py theme={null}
df.query('OpenOrders>160')
```

```
   VendorNumber            VendorName  Active  OpenOrders  Purchases2015  Purchases2016  PercentGrowth FirstPurchaseDate
0           104              ACME Inc    True         200        45000.0        54000.0           0.20        2014-02-15
2           143             Zenith Co    True         290        18000.0        23400.0           0.30        2014-04-05
3           166  Hollerith Propulsion    True         180        48000.0        48960.0           0.02        2015-09-25
```

Filtering can be done with indices instead of queries:

```py theme={null}
df[(df.OpenOrders < 190) & (df.Active == True)]
```

```
   VendorNumber            VendorName  Active  OpenOrders  Purchases2015  Purchases2016  PercentGrowth FirstPurchaseDate
1           205            Apogee LTD    True         150        29000.0        30450.0           0.05        2015-08-12
3           166  Hollerith Propulsion    True         180        48000.0        48960.0           0.02        2015-09-25
```