<jupyter_start><jupyter_text>Join and Reshape Data> Can I join you?- toc: true- branch: master- badges: true- author: <NAME>- categories: [data-science]  OverviewIn many applications, data may be spread across a number of files or databases or be arranged in a form that is not easy to analyze. In this post we'll explore different ways we can merge, concatenate and join datasets together. The data wrangling task of joining datasets is very well suited for pandas. There are several different tools that we can use, from very simple to very complex DataFrame combinations. We'll begin with one of the easiest: concatenation using the pandas method `pd.concat`. Concatenation means to join two things together. For example, with string concatenation, we join two or more strings end-to-end. We'll see that for a structure like a DataFrame, "end-to-end" has a slightly different meaning.  Introduction to pd.concat()To become more familiar with this method, let's look at the default parameters of `pd.concat()`.<jupyter_code>#collapse-hide

import pandas as pd
import numpy as np

help(pd.concat)<jupyter_output>Help on function concat in module pandas.core.reshape.concat:

concat(objs: 'Iterable[NDFrame] | Mapping[Hashable, NDFrame]', axis=0, join='outer', ignore_index: 'bool' = False, keys=None, levels=None, names=None, verify_integrity: 'bool' = False, sort: 'bool' = False, copy: 'bool' = True) -> 'FrameOrSeriesUnion'
    Concatenate pandas objects along a particular axis with optional set logic
    along the other axes.
    
    Can also add a layer of hierarchical indexing on the concatenation axis,
    which may be useful if the labels are the same (or overlapping) on
    the passed axis number.
    
    Parameters
    ----------
    objs : a sequence or mapping of Series or DataFrame objects
        If a mapping is passed, the sorted keys will be used as the `keys`
        argument, unless it is passed, in which case the values will be
        selected (see below). Any None objects will be dropped silently unless
        they are all None in which case a ValueError will be raised.
    a[...]<jupyter_text>We can see that there are a number of parameters to use with this method. To start, our use cases will focus on the `axis` and `join` parameters. First lets make a toy dataframe.<jupyter_code># Make DataFrames
df1 = pd.DataFrame(np.random.rand(3,2),
                  columns = ['cat', 'dog'])
df2 = pd.DataFrame(np.random.rand(3,2),
                  columns = ['cat', 'dog'])

display(df1); display(df2)<jupyter_output><empty_output><jupyter_text>Using `pd.concat`We'll combine them along the row axis (axis=0) which is the default setting for `pd.concat()`.<jupyter_code># Concatenate along the row axis
pd.concat([df1, df2])<jupyter_output><empty_output><jupyter_text>We can see that the concatenation preserves the indexes, so we now have some that repeat. One way to avoid this is to set the parameter `ignore_index=True`.Say we have another DataFrame and we would like to concatenate them along the *column axis*. We need to set the argument `axis=1` to do this. To test this out, we'll create a DataFrame with different column names, combine them, and view the results.<jupyter_code># Create the new DataFrame
df3 = pd.DataFrame(np.random.rand(3,2),
                  columns = ['bird', 'horse'])

# Concatenate it with one of the previous examples
pd.concat([df1, df3], axis=1)<jupyter_output><empty_output><jupyter_text>In the above examples, `df1` and `df2` have the same column names; it was easy to combine them. What do we do if we don't have any column names in common but still want to combine the DataFrames? There are additional arguments that we can pass to `pd.concat`.Using the `join` argument we can combine two DataFrames with overlapping columns. The default value of `join` is `outer` which is a union of all of the columns. Let's create a few more DataFrames to practice with.<jupyter_code># Create the DataFrames

data4 = {'Alpaca':['A1', 'A2', 'A3'], 'Bird':['B1', 'B2', 'B3'],
        'Camel':['C1', 'C2', 'C3']} 
df4 = pd.DataFrame(data4)
display(df4)

data5 = {'Bird':['B3', 'B4', 'B5'], 'Camel':['C3', 'C4', 'C5'],
        'Duck':['D3', 'D4', 'D5']} 
df5 = pd.DataFrame(data5, index=[3,4,5])
display(df5)

# Concatenate with default join='outer'
# (sort=False is used to suppress the warning that
# future pandas will no longer sort the rows) 
display(pd.concat([df4, df5], sort=False))<jupyter_output><empty_output><jupyter_text>We can see in the above example that the cells where there isn't data available now have `NaN` values. To join only the columns that the two DataFrames have in common, use the `join=inner` argument.<jupyter_code># Concatenate with default join='inner'
display(pd.concat([df4, df5], join='inner'))<jupyter_output><empty_output><jupyter_text>Now the only columns that remain are the ones that the two DataFrames had in common. There are other ways to combine DataFrames, especially when there are overlapping rows. The pandas `pd.merge()` method uses database-style joins which are similar to what you might see in SQL later in this course. Introduction to pd.merge()To become more familiar with this method, let's look at the available arguments and defaults for this function.<jupyter_code>#collapse-hide

# Display the arguments for pd.merge()
help(pd.merge)<jupyter_output>Help on function merge in module pandas.core.reshape.merge:

merge(left: 'DataFrame | Series', right: 'DataFrame | Series', how: 'str' = 'inner', on: 'IndexLabel | None' = None, left_on: 'IndexLabel | None' = None, right_on: 'IndexLabel | None' = None, left_index: 'bool' = False, right_index: 'bool' = False, sort: 'bool' = False, suffixes: 'Suffixes' = ('_x', '_y'), copy: 'bool' = True, indicator: 'bool' = False, validate: 'str | None' = None) -> 'DataFrame'
    Merge DataFrame or named Series objects with a database-style join.
    
    A named Series object is treated as a DataFrame with a single named column.
    
    The join is done on columns or indexes. If joining columns on
    columns, the DataFrame indexes *will be ignored*. Otherwise if joining indexes
    on indexes or indexes on a column or columns, the index will be passed on.
    When performing a cross merge, no column specifications to merge on are
    allowed.
    
    Parameters
    ----------
    left : DataFrame
  [...]<jupyter_text>Some of the arguments are similar to what we have seen already with concatenation. But with `pd.merge()` we can do a lot of different types of joins. Let's summarize some of the different parameters.* `left` - one of the DataFrames to merge* `right` - the other DataFrame to merge* `how` - specifies the kind of merge to make* `on` - the column on which to merge; needs to be in both DataFrames* `left_on` - the column to join on in the left DataFrame* `right_on` - the column to join on in the right DataFrame* `left_index` or `right_index` - use the index from the left/right DataFrame as the join key(s)Let's start with one of the more basic joins, which is exactly the same as the `pd.concat()` column-wise example we completed previously.We'll create some more DataFrames for practice.<jupyter_code>df6 = pd.DataFrame({'day': ['Mon', 'Tues', 'Wed', 'Thurs', 'Fri'],
                   'weather': ['sunny', 'windy', 'rainy', 'cloudy', 'cold']})

df7 = pd.DataFrame({'day': ['Wed', 'Fri', 'Mon', 'Thurs', 'Tues'],
                   'temp': [88, 75, 62, 65, 53]})

display(df6)
display(df7)                
# Merge them with the default values
df8 = pd.merge(df6, df7)
display(df8)<jupyter_output><empty_output><jupyter_text>Our new Dataframe df8 only has one column for the `day`.`pd.merge()` recognized the shared column and merged them on this column. It's also important to note that the order of the days (rows) in each DataFrame doesn't have to be the same in order for them to be merged. There will be cases where the column names don't match, which is where the `left_on` and `right_on` parameters are useful. Let's use the same DataFrame as above but instead change the column name on one of them.<jupyter_code># Change the column name day -> day_of_week
df7_new = pd.DataFrame({'day_of_week': ['Wed', 'Fri', 'Mon', 'Thurs', 'Tues'],
                   'temp': [88, 75, 62, 65, 53]})

# Merge with left and right columns
df8_new = pd.merge(df6, df7_new, left_on='day', right_on='day_of_week')
display(df8_new)<jupyter_output><empty_output><jupyter_text>There is a redundant column where `day` and `day_of_week` both have the same values. We can use the `.drop()` method to remove right after we join the DataFrames.<jupyter_code>df8_drop = pd.merge(df6, df7_new, 
                    left_on='day', 
                    right_on='day_of_week').drop('day_of_week', axis=1)

display(df8_drop)<jupyter_output><empty_output><jupyter_text>JoinsSo far we have not considered when there are missing values in the columns and we want to merge or join them. We'll create a DataFrame of information about pets in an animal rescue, where we don't have all the data on all the animals.<jupyter_code>df_animals = pd.DataFrame({'name': ['Snowball', 'Bob', 'Tweetie', 'Fluffy'],
                   'type': ['cat', 'rat', 'bird', 'dog']})

df_animals1 = pd.DataFrame({'name': ['Snowball', 'Bob'],
                   'weight': [400, 749]})

display(df_animals); display(df_animals1)
# Merge in two different ways
df_animals_outer = pd.merge(df_animals, df_animals1, how='outer')
df_animals_inner = pd.merge(df_animals, df_animals1, how='inner')

display(df_animals_outer); display(df_animals_inner)<jupyter_output><empty_output><jupyter_text>We started with a list of animal names, types, and weights but didn't have weight data for all animals. The `outer` join used all of the animals and filled in `NaN` for the missing weights. This type of join is called a union. The `inner` join only includes rows where there is data in all three columns; this type is called an *intersection*.  Using the .join methodThere is a also a `.join()` method that is available with DataFrame objects. This method is a convenient way to join DataFrames without needing to make a full merge() call. Some of the parameters we can use here are:- other - the other DataFrame you are joining- on - the column or Index on which to join the other DataFrame (default is the Index)- how - similar to merge the choices are left, right, outer, innerWe need to use `set_index` on our DataFrames so that they can be joined on those indexes.<jupyter_code># Reset indices
df6a = df6.set_index('day')
df7a = df7.set_index('day')

# Join on the new index day
df6a.join(df7a, on='day')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10118154538634659, 'fraction_numerical': 0.010221305326331583, 'mean_word_length': 4.287555775904809, 'pattern_counts': {'":': 0, '<': 48, '<?xml version=': 0, '>': 52, 'https://': 0, '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': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '189534', 'license': 'Apache-2.0', 'n_tokens_mistral': 3255, 'n_tokens_neox': 3103, 'n_words': 1395, 'path': '_notebooks/2019-08-03-reshaping-data.ipynb', 'repo_name': 'jhnnxyzptlk/computer-blue', 'size': '10674'}