<jupyter_start><jupyter_text>10. Combining information in Pandas<jupyter_code>import numpy as np
import pandas as pd
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>Often information is comming from different sources and it is necessary to combine it into one object. We are going to see the different ways in which information contained within separate Dataframes can be combined in a meaningful way.  10.1 Concatenation The simplest way we can combine two Dataframes is simply to "paste" them together:<jupyter_code>composers1 = pd.read_excel('Data/composers.xlsx', index_col='composer',sheet_name='Sheet1')
composers1
composers2 = pd.read_excel('Data/composers.xlsx', index_col='composer',sheet_name='Sheet3')
composers2<jupyter_output><empty_output><jupyter_text>To be concatenated, Dataframes need to be provided as a list:<jupyter_code>all_composers = pd.concat([composers1,composers2])
all_composers<jupyter_output><empty_output><jupyter_text>One potential problem is that two tables contain duplicated information:<jupyter_code>all_composers.loc['Mahler']<jupyter_output><empty_output><jupyter_text>It is very easy to get rid of it using.```duplicated()``` gives us a boolean series of duplications and we can just selected non-duplicated rows:<jupyter_code>all_composers.duplicated()
all_composers[~all_composers.duplicated()]<jupyter_output><empty_output><jupyter_text>10.2 Joining two tables An other classical case is that of two list with similar index but containing different information, e.g.<jupyter_code>composers1 = pd.read_excel('Data/composers.xlsx', index_col='composer',sheet_name='Sheet1')
composers1
composers2 = pd.read_excel('Data/composers.xlsx', index_col='composer',sheet_name='Sheet4')
composers2<jupyter_output><empty_output><jupyter_text>If we we use again simple concatenation, this doesn't help us much. We just end up with a large matrix with lots of NaN's:<jupyter_code>pd.concat([composers1, composers2])<jupyter_output><empty_output><jupyter_text>The better way of doing this is to **join** the tables. This is a classical database concept available in Pandas.```join()``` operates on two tables: the first one is the "left" table which uses ```join()``` as a method. The other table is the "right" one.Let's try the default join settings:<jupyter_code>composers1
composers2
composers1.join(composers2)<jupyter_output><empty_output><jupyter_text>We see that Pandas was smart enough to notice that the two tables had a index name and used it to combine the tables. We also see that one element from the second table (Brahms) is missing. The reason for this is the way indices not present in both tables are handled. There are four ways of doing this with two tables called here the "left" and "right" table.  10.2.1. Join left Here "left" and "right" just represent two Dataframes that should be merged. They have a common index, but not necessarily the same items. For example here Shostakovich is missing in the second table, while Brahms is missing in the first one. When using the "right" join, we use the first Dataframe as basis and only use the indices that appear there.<jupyter_code>composers1.join(composers2, how = 'left')<jupyter_output><empty_output><jupyter_text>Hence Brahms is left out.  10.2.2. Join right We can do the the opposite and use the indices of the second Dataframe as basis:<jupyter_code>composers1.join(composers2, how = 'right')<jupyter_output><empty_output><jupyter_text>Here we have Brahms but not Shostakovich.  10.2.3. Inner, outer Finally, we can just say that we want to recover eihter only the items that appaer in both Dataframes (inner, like in a Venn diagram) or all the items (outer).<jupyter_code>composers1.join(composers2, how = 'inner')
composers1.join(composers2, how = 'outer')<jupyter_output><empty_output><jupyter_text>10.3.4 Joining on columns : merge Above we have used ```join``` to join based on indices. However sometimes tables don't have the same indices but similar contents that we want to merge. For example let's imagine whe have the two Dataframes below:<jupyter_code>composers1 = pd.read_excel('Data/composers.xlsx', sheet_name='Sheet1')
composers2 = pd.read_excel('Data/composers.xlsx', sheet_name='Sheet6')
composers1
composers2<jupyter_output><empty_output><jupyter_text>The indices don't match and are not the composer name. In addition the columns containing the composer names have different labels. Here we can use ```merge()```and specify which columns we want to use for merging, and what type of merging we need (inner, left etc.)<jupyter_code>pd.merge(composers1, composers2, left_on='composer', right_on='last name')<jupyter_output><empty_output><jupyter_text>Again we can use another variety of join than the default inner:<jupyter_code>pd.merge(composers1, composers2, left_on='composer', right_on='last name',how = 'outer')
pd.merge(composers1, composers2, left_on='composer', right_on='last name',how = 'right')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08826479438314945, 'fraction_numerical': 0.012838515546639919, 'mean_word_length': 6.864353312302839, 'pattern_counts': {'":': 0, '<': 57, '<?xml version=': 0, '>': 57, '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': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '177694', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 1595, 'n_tokens_neox': 1559, 'n_words': 543, 'path': '10-DA_Pandas_combine.ipynb', 'repo_name': 'girijeshcse/NumpyPandas_course', 'size': '4985'}