In this lab, we'll gain practice combining DataFrames through concatenation. We'll also learn to read in tables from a database and store them in separate tables, as well as how to execute various types of joins to selectively combine the information stored in the tables!
You will be able to:
- Understand and explain when to use DataFrame joins and merges
- Be able to use pd.merge when combining DataFrames based on column values
- Understand, explain and use a range of DataFrame merge types: outer, inner, left and right
- Use pd.concat() to stack DataFrames
We'll start with a quick section to help us gain practice with concatenating datasets using pd.concat()
.
Run the cell below to create some sample DataFrames for us to work with.
import pandas as pd
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3'],
'C': ['C0', 'C1', 'C2', 'C3'],
'D': ['D0', 'D1', 'D2', 'D3']},
index=[0, 1, 2, 3])
df2 = pd.DataFrame({'A': ['A4', 'A5', 'A6', 'A7'],
'B': ['B4', 'B5', 'B6', 'B7'],
'C': ['C4', 'C5', 'C6', 'C7'],
'D': ['D4', 'D5', 'D6', 'D7']},
index=[4, 5, 6, 7])
df3 = pd.DataFrame({'A': ['A8', 'A9', 'A10', 'A11'],
'B': ['B8', 'B9', 'B10', 'B11'],
'C': ['C8', 'C9', 'C10', 'C11'],
'D': ['D8', 'D9', 'D10', 'D11']},
index=[8, 9, 10, 11])
Now that we have multiple DataFrames to work with, we can execute a concatenation to join them together.
In the cell below, concatenate the 3 DataFrames together using the appropriate function.
combined_df = pd.concat([df1, df2, df3])
combined_df
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
A | B | C | D | |
---|---|---|---|---|
0 | A0 | B0 | C0 | D0 |
1 | A1 | B1 | C1 | D1 |
2 | A2 | B2 | C2 | D2 |
3 | A3 | B3 | C3 | D3 |
4 | A4 | B4 | C4 | D4 |
5 | A5 | B5 | C5 | D5 |
6 | A6 | B6 | C6 | D6 |
7 | A7 | B7 | C7 | D7 |
8 | A8 | B8 | C8 | D8 |
9 | A9 | B9 | C9 | D9 |
10 | A10 | B10 | C10 | D10 |
11 | A11 | B11 | C11 | D11 |
EXPECTED OUTPUT:
We can also specify if the concatenation is an Outer Join or an Inner Join. Next, we'll execute an inner join. Before we do, we need to create another table that contains some overlapping index values with a DataFrame that already exists.
Run the cell below to create the new DataFrame.
df4 = pd.DataFrame({'B': ['B2', 'B3', 'B6', 'B7'],
'D': ['D2', 'D3', 'D6', 'D7'],
'F': ['F2', 'F3', 'F6', 'F7']},
index=[2, 3, 6, 7])
Now, in the cell below, use the pd.concat()
method to join DataFrames 1 and 4. However, this time, specify that the join
is 'inner'
, and axis=1
.
df1_and_4 = pd.concat([df1, df4], join='inner', axis=1)
df1_and_4
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
A | B | C | D | B | D | F | |
---|---|---|---|---|---|---|---|
2 | A2 | B2 | C2 | D2 | B2 | D2 | F2 |
3 | A3 | B3 | C3 | D3 | B3 | D3 | F3 |
EXPECTED OUTPUT:
We'll notice that in this case, the results returned contain only the rows with indexes that exist in both tables--rows 2 and 3. The resulting table contains the values for each column in both tables for the rows.
Note that there are many, many ways that we can make full use of the concat()
functionality in pandas to join DataFrames together--these are just a few of the most common examples pulled from the pandas documentation on the subject. For a full view of all the ways we can use pd.concat()
, see the pandas documentation!
Now, we'll move on to working with the Hearthstone cards database. This database contains information on cards from the popular game, Hearthstone! For full information on the dataset, see the kaggle page for this dataset.
This database consists of the following tables:
- cards
- dust_costs
- entourages
- mechanics
- play_requirements
Many of rows in each table--but not all--correspond to the same cards. As such, each table contains a column called card_id
which acts as a Primary Key for each table. We'll make use of these keys to join the different tables together into a single DataFrame. We'll also experiment with different types of joins to help us decide exactly what information we want to combine.
Before we can begin working with the database, we'll first need to connect to it. For this, we'll use the sqlalchemy
library.
In the cell below:
- Import the
create_engine
module fromsqlalchemy
- Create an engine that connects to
sqlite:///database.sqlite
.
from sqlalchemy import create_engine
database_path = "database.sqlite"
engine = create_engine('sqlite:///' + database_path, echo=False)
Now that we've successfully connected to our Database, we'll make use of pandas to read in each table individually.
In the cell below, read in each sql table into separate pandas DataFrames using the read_sql_table
method.
The first argument should be a string corresponding to the name of the table, and the second argument should be the engine
object we created in the cell above.
If you're unsure of how to do this, see the documentation.
#df_from_table = pd.read_sql_table(table_name, engine)
cards_df = pd.read_sql_table('cards', engine)
dust_df = pd.read_sql_table('dust_costs', engine)
entourages_df = pd.read_sql_table('entourages', engine)
mechanics_df = pd.read_sql_table('mechanics', engine)
play_requirements_df = pd.read_sql_table('play_requirements', engine)
Great. Now, let's set the correct column, card_id
, as the index column for each of these tables, and then display each to ensure that everything is as expected.
For each of the dataframes we created in the cell above, call the .set_index()
method and pass in card_id
. Also set inplace=True
. Then, display the head of each respective DataFrame to ensure everything worked.
NOTE: Since we are performing this operation in place, running any cell a second time will result in pandas throwing an error. If you need to run something a second time, restart the kernel using the jupyter notebook menu at the top of the page.
cards_df.set_index('card_id', inplace=True)
cards_df.head()
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
player_class | type | name | set | text | cost | attack | health | rarity | collectible | flavor | race | how_to_earn | how_to_earn_golden | targeting_arrow_text | faction | durability | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
card_id | |||||||||||||||||
KARA_00_07 | MAGE | SPELL | Astral Portal | KARA | Summon a random <b>Legendary</b> minion. | 1.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN |
NEW1_008a | DRUID | SPELL | Ancient Teachings | EXPERT1 | Draw a card. | 0.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN |
BRM_010t2 | DRUID | MINION | Druid of the Flame | BRM | None | 3.0 | 2.0 | 5.0 | COMMON | NaN | None | BEAST | None | None | None | None | NaN |
AT_132 | NEUTRAL | MINION | Justicar Trueheart | TGT | <b>Battlecry:</b> Replace your starting Hero P... | 6.0 | 6.0 | 3.0 | LEGENDARY | 1.0 | It's like putting racing stripes and a giant s... | None | None | None | None | None | NaN |
OG_141 | NEUTRAL | MINION | Faceless Behemoth | OG | None | 10.0 | 10.0 | 10.0 | COMMON | 1.0 | Rejected names: Forty-Foot Faceless, Big ol' N... | None | None | None | None | None | NaN |
dust_df.set_index('card_id', inplace=True)
dust_df.head()
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
action | cost | |
---|---|---|
card_id | ||
BRM_010t2 | CRAFTING_NORMAL | 40 |
BRM_010t2 | CRAFTING_GOLDEN | 400 |
BRM_010t2 | DISENCHANT_NORMAL | 5 |
BRM_010t2 | DISENCHANT_GOLDEN | 50 |
AT_132 | CRAFTING_NORMAL | 1600 |
entourages_df.set_index('card_id', inplace=True)
entourages_df.head()
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
entourage_card_id | |
---|---|
card_id | |
KAR_A10_22 | KAR_A10_09 |
KAR_A10_22 | KAR_A10_02 |
KAR_A10_22 | KAR_A10_08 |
KAR_A10_22 | KAR_A10_04 |
KAR_A10_22 | KAR_A10_05 |
mechanics_df.set_index('card_id', inplace=True)
mechanics_df.head()
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
mechanic | |
---|---|
card_id | |
AT_132 | BATTLECRY |
GVG_011a | TAG_ONE_TURN_EFFECT |
EX1_583 | BATTLECRY |
LOE_007t | EVIL_GLOW |
LOE_007t | ImmuneToSpellpower |
play_requirements_df.set_index('card_id', inplace=True)
play_requirements_df.head()
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
play_requirement | value | |
---|---|---|
card_id | ||
KARA_00_07 | REQ_NUM_MINION_SLOTS | 1 |
PRO_001a | REQ_NUM_MINION_SLOTS | 1 |
NAX1_01 | REQ_NUM_MINION_SLOTS | 1 |
DS1h_292_H1 | REQ_STEADY_SHOT | 0 |
DS1h_292_H1 | REQ_MINION_OR_ENEMY_HERO | 0 |
Now that we have the tables loaded correctly, we're going to execute some joins. There are 4 different kinds of joins, which can best be visualized with venn diagrams:
In these diagrams, each circle represents a DataFrame or SQL Table. The left table is the table we are working with, and the right table is the table we want to join to the table we are working with. We'll start by executing the most common type of join, an Inner Join.
In the cell below, join cards_df
with mechanics_df
using the built-in .join()
method on the cards_df
object.
Pass in the following parameters:
- the table we want to join with,
mechanics_df
- The
how
parameter set to the type of join we want,'inner'
cards_with_mechanics_df = cards_df.join(mechanics_df, how='inner')
cards_with_mechanics_df
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
player_class | type | name | set | text | cost | attack | health | rarity | collectible | flavor | race | how_to_earn | how_to_earn_golden | targeting_arrow_text | faction | durability | mechanic | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
card_id | ||||||||||||||||||
AT_002 | MAGE | SPELL | Effigy | TGT | <b>Secret:</b> When a friendly minion dies, su... | 3.0 | NaN | NaN | RARE | 1.0 | Burning man, brah. | None | None | None | None | None | NaN | SECRET |
AT_005t | NEUTRAL | MINION | Boar | TGT | <b>Charge</b> | 3.0 | 4.0 | 2.0 | None | NaN | None | BEAST | None | None | None | None | NaN | CHARGE |
AT_006 | MAGE | MINION | Dalaran Aspirant | TGT | <b>Inspire:</b> Gain <b>Spell Damage +1</b>. | 4.0 | 3.0 | 5.0 | COMMON | 1.0 | Is he aspiring or inspiring? Make up your mind! | None | None | None | None | None | NaN | INSPIRE |
AT_007 | MAGE | MINION | Spellslinger | TGT | <b>Battlecry:</b> Add a random spell to each p... | 3.0 | 3.0 | 4.0 | COMMON | 1.0 | Does he sling spells, or do his spells linger ... | None | None | None | None | None | NaN | BATTLECRY |
AT_009 | MAGE | MINION | Rhonin | TGT | <b>Deathrattle:</b> Add 3 copies of Arcane Mis... | 8.0 | 7.0 | 7.0 | LEGENDARY | 1.0 | A masterless shamurai. | None | None | None | None | None | NaN | DEATHRATTLE |
AT_010 | HUNTER | MINION | Ram Wrangler | TGT | <b>Battlecry:</b> If you have a Beast, summon ... | 5.0 | 3.0 | 3.0 | RARE | 1.0 | Not getting trampled is really the trick here. | None | None | None | None | None | NaN | BATTLECRY |
AT_011e | NEUTRAL | ENCHANTMENT | Light's Blessing | TGT | Increased Attack. | NaN | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | AURA |
AT_012 | PRIEST | MINION | Spawn of Shadows | TGT | <b>Inspire:</b> Deal 4 damage to each hero. | 4.0 | 5.0 | 4.0 | RARE | 1.0 | What did you expect to happen? He's a Spawn. ... | None | None | None | None | None | NaN | INSPIRE |
AT_017 | NEUTRAL | MINION | Twilight Guardian | TGT | <b>Battlecry:</b> If you're holding a Dragon, ... | 4.0 | 2.0 | 6.0 | EPIC | 1.0 | A result of magical experiments carried out by... | DRAGON | None | None | None | None | NaN | BATTLECRY |
AT_018 | PRIEST | MINION | Confessor Paletress | TGT | <b>Inspire:</b> Summon a random <b>Legendary</... | 7.0 | 5.0 | 4.0 | LEGENDARY | 1.0 | She sees into your past and makes you face you... | None | None | None | None | None | NaN | INSPIRE |
AT_019 | WARLOCK | MINION | Dreadsteed | TGT | <b>Deathrattle:</b> Summon a Dreadsteed. | 4.0 | 1.0 | 1.0 | EPIC | 1.0 | Crescendo himself summoned this steed, riding ... | DEMON | None | None | None | None | NaN | DEATHRATTLE |
AT_022 | WARLOCK | SPELL | Fist of Jaraxxus | TGT | When you play or discard this, deal $4 damage ... | 4.0 | NaN | NaN | RARE | 1.0 | * Not actually Jaraxxus' fist. | None | None | None | None | None | NaN | InvisibleDeathrattle |
AT_023 | WARLOCK | MINION | Void Crusher | TGT | <b>Inspire:</b> Destroy a random minion for ea... | 6.0 | 5.0 | 4.0 | RARE | 1.0 | We like to call him "Wesley". | DEMON | None | None | None | None | NaN | INSPIRE |
AT_028 | ROGUE | MINION | Shado-Pan Rider | TGT | <b>Combo:</b> Gain +3 Attack. | 5.0 | 3.0 | 7.0 | COMMON | 1.0 | He needed a break after that business in the V... | None | None | None | None | None | NaN | COMBO |
AT_030 | ROGUE | MINION | Undercity Valiant | TGT | <b>Combo:</b> Deal 1 damage. | 2.0 | 3.0 | 2.0 | COMMON | 1.0 | Almost went to play for Stormwind before signi... | None | None | None | Deal 1 damage. | None | NaN | COMBO |
AT_032 | ROGUE | MINION | Shady Dealer | TGT | <b>Battlecry:</b> If you have a Pirate, gain +... | 3.0 | 4.0 | 3.0 | RARE | 1.0 | I have great deal for you... for 4 damage to y... | None | None | None | None | None | NaN | BATTLECRY |
AT_035t | ROGUE | SPELL | Ambush! | TGT | When you draw this, summon a 4/4 Nerubian for ... | 0.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | TOPDECK |
AT_036 | ROGUE | MINION | Anub'arak | TGT | <b>Deathrattle:</b> Return this to your hand a... | 9.0 | 8.0 | 4.0 | LEGENDARY | 1.0 | Was actually a pretty nice guy before, you kno... | None | None | None | None | None | NaN | DEATHRATTLE |
AT_037 | DRUID | SPELL | Living Roots | TGT | <b>Choose One -</b> Deal $2 damage; or Summon ... | 1.0 | NaN | NaN | COMMON | 1.0 | 2 out of 2 saplings recommend that you summon ... | None | None | None | None | None | NaN | CHOOSE_ONE |
AT_038 | DRUID | MINION | Darnassus Aspirant | TGT | <b>Battlecry:</b> Gain an empty Mana Crystal.\... | 2.0 | 2.0 | 3.0 | RARE | 1.0 | She loves mana crystals, she hates mana crysta... | None | None | None | None | None | NaN | BATTLECRY |
AT_038 | DRUID | MINION | Darnassus Aspirant | TGT | <b>Battlecry:</b> Gain an empty Mana Crystal.\... | 2.0 | 2.0 | 3.0 | RARE | 1.0 | She loves mana crystals, she hates mana crysta... | None | None | None | None | None | NaN | DEATHRATTLE |
AT_039 | DRUID | MINION | Savage Combatant | TGT | <b>Inspire:</b> Give your hero\n+2 Attack this... | 4.0 | 5.0 | 4.0 | RARE | 1.0 | Maybe if you whistle a tune it will soothe him... | BEAST | None | None | None | None | NaN | INSPIRE |
AT_039e | DRUID | ENCHANTMENT | Savage | TGT | +2 Attack this turn. | NaN | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | TAG_ONE_TURN_EFFECT |
AT_040 | DRUID | MINION | Wildwalker | TGT | <b>Battlecry:</b> Give a friendly Beast +3 Hea... | 4.0 | 4.0 | 4.0 | COMMON | 1.0 | She was born to be something. She is just not... | None | None | None | None | None | NaN | BATTLECRY |
AT_042 | DRUID | MINION | Druid of the Saber | TGT | <b>Choose One -</b> Transform to gain <b>Charg... | 2.0 | 2.0 | 1.0 | COMMON | 1.0 | That's saberTEETH, not like curved pirate blad... | None | None | None | None | None | NaN | CHOOSE_ONE |
AT_042t | DRUID | MINION | Sabertooth Lion | TGT | <b>Charge</b> | 2.0 | 2.0 | 1.0 | COMMON | NaN | None | BEAST | None | None | None | None | NaN | CHARGE |
AT_042t2 | DRUID | MINION | Sabertooth Panther | TGT | <b>Stealth</b> | 2.0 | 3.0 | 2.0 | COMMON | NaN | None | BEAST | None | None | None | None | NaN | STEALTH |
AT_045 | DRUID | MINION | Aviana | TGT | Your minions cost (1). | 9.0 | 5.0 | 5.0 | LEGENDARY | 1.0 | Call her "Tweety". She'll find it real funny.... | None | None | None | None | None | NaN | AURA |
AT_046 | SHAMAN | MINION | Tuskarr Totemic | TGT | <b>Battlecry:</b> Summon a random basic Totem. | 3.0 | 3.0 | 2.0 | COMMON | 1.0 | Turns out the tuskarr aren't real choosy about... | None | None | None | None | None | NaN | BATTLECRY |
AT_047 | SHAMAN | MINION | Draenei Totemcarver | TGT | <b>Battlecry:</b> Gain +1/+1 for each friendly... | 4.0 | 4.0 | 4.0 | RARE | 1.0 | It's nice to find a real craftsman in this day... | None | None | None | None | None | NaN | BATTLECRY |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
TB_SPT_DPromoSecret3 | WARRIOR | SPELL | Visions of Valor | TB | <b>Secret:</b> When your opponent summons a Le... | 2.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | SECRET |
TB_SPT_DPromoSecret4 | WARRIOR | SPELL | Visions of Fate | TB | <b>Secret:</b> When your opponent takes lethal... | 2.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | SECRET |
TB_SPT_DPromoSecret5 | WARRIOR | SPELL | Visions of the Amazon | TB | <b>Secret:</b> When your opponent summons a mi... | 2.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | SECRET |
TB_SPT_DPromoSecret6 | WARRIOR | SPELL | Visions of the Sorcerer | TB | <b>Secret:</b> When your opponent summons a mi... | 2.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | SECRET |
TB_SPT_DPromoSecret7 | WARRIOR | SPELL | Visions of the Necromancer | TB | <b>Secret:</b> When your opponent summons a mi... | 2.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | SECRET |
TB_SPT_DPromoSecret9 | WARRIOR | SPELL | Visions of Knowledge | TB | <b>Secret:</b> When your opponent's hand has 9... | 2.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | SECRET |
TB_SPT_DPromo_Hero2 | WARRIOR | HERO | The Cow King | TB | None | NaN | NaN | 30.0 | None | NaN | None | None | None | None | None | None | NaN | InvisibleDeathrattle |
TB_SPT_DpromoPortal | NEUTRAL | MINION | Enigmatic Portal | TB | At the start of next turn, your hero is transf... | 4.0 | 0.0 | 10.0 | None | NaN | None | None | None | None | None | None | NaN | BATTLECRY |
TB_SPT_DpromoPortal | NEUTRAL | MINION | Enigmatic Portal | TB | At the start of next turn, your hero is transf... | 4.0 | 0.0 | 10.0 | None | NaN | None | None | None | None | None | None | NaN | IMMUNE |
TB_SPT_Minion1 | NEUTRAL | MINION | Shieldsman | TB | <b>Taunt</b>\n<b>Battlecry:</b> Gain Health eq... | 2.0 | 0.0 | 1.0 | None | NaN | None | None | None | None | None | None | NaN | TAUNT |
TB_SPT_Minion2 | NEUTRAL | MINION | Battle Standard | TB | Adjacent minions have +2 Attack. | 2.0 | 0.0 | 2.0 | None | NaN | None | None | None | None | None | None | NaN | ADJACENT_BUFF |
TB_SPT_Minion2 | NEUTRAL | MINION | Battle Standard | TB | Adjacent minions have +2 Attack. | 2.0 | 0.0 | 2.0 | None | NaN | None | None | None | None | None | None | NaN | AURA |
TU4c_003 | NEUTRAL | MINION | Barrel | MISSIONS | Is something in this barrel? | 0.0 | 0.0 | 2.0 | COMMON | NaN | None | None | None | None | None | None | NaN | DEATHRATTLE |
TU4c_008e | NEUTRAL | ENCHANTMENT | Might of Mukla | MISSIONS | King Mukla has +8 Attack this turn. | NaN | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | TAG_ONE_TURN_EFFECT |
TU4f_007 | NEUTRAL | MINION | Crazy Monkey | MISSIONS | <b>Battlecry:</b> Throw Bananas. | 1.0 | 1.0 | 2.0 | COMMON | NaN | None | None | None | None | None | None | NaN | BATTLECRY |
XXX_001 | NEUTRAL | SPELL | Damage 1 | CHEAT | Deal $1 damage. | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower |
XXX_002 | NEUTRAL | SPELL | Damage 5 | CHEAT | Deal $5 damage. | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower |
XXX_008 | NEUTRAL | SPELL | Freeze | CHEAT | <b>Freeze</b> a character. | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | FREEZE |
XXX_020 | NEUTRAL | SPELL | Damage all but 1 | CHEAT | Set the Health of a character to 1. | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower |
XXX_060 | NEUTRAL | SPELL | Damage All | CHEAT | Set the Health of a character to 0. | 0.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower |
XXX_100 | NEUTRAL | MINION | Yogg-Saron Test (Manual) | CHEAT | <b>Battlecry:</b> Cast each spell you've cast ... | 0.0 | 7.0 | 5.0 | LEGENDARY | NaN | None | None | None | None | None | None | NaN | BATTLECRY |
XXX_101 | NEUTRAL | SPELL | Set health to full | CHEAT | Set a character's health to full, and removes ... | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower |
XXX_102 | NEUTRAL | SPELL | Add 1 to Health. | CHEAT | Adds 1 health to a damaged character. Does NOT... | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower |
XXX_103 | NEUTRAL | SPELL | Add 2 to Health | CHEAT | Adds 2 health to a damaged character. Does NOT... | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower |
XXX_104 | NEUTRAL | SPELL | Add 4 to Health. | CHEAT | Adds 4 health to a damaged character. Does NOT... | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower |
XXX_105 | NEUTRAL | SPELL | Add 8 to Health. | CHEAT | Adds 8 health to a damaged character. Does NOT... | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower |
XXX_107 | NEUTRAL | SPELL | Set Health to 1 | CHEAT | Set a character's health to 1, and remove all ... | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower |
XXX_110 | NEUTRAL | MINION | Yogg-Saron Test (Auto) | CHEAT | <b>Battlecry:</b> Cast 30 random spells <i>(ta... | 0.0 | 7.0 | 5.0 | LEGENDARY | NaN | None | None | None | None | None | None | NaN | BATTLECRY |
hexfrog | NEUTRAL | MINION | Frog | CORE | <b>Taunt</b> | 0.0 | 0.0 | 1.0 | COMMON | NaN | None | BEAST | None | None | None | None | NaN | TAUNT |
tt_010 | MAGE | SPELL | Spellbender | EXPERT1 | <b>Secret:</b> When an enemy casts a spell on ... | 3.0 | NaN | NaN | EPIC | 1.0 | While it's fun to intercept enemy lightning bo... | None | None | None | None | None | NaN | SECRET |
1079 rows × 18 columns
Examine the output from the cell above and compare it to the original output of both the cards_df
and mechanics_df
DataFrame heads we displayed earlier. Notice how it this now combines the columns from both?
Question
If you inspect the original cards_df
DataFrame, you'll notice that it contains 2,819 records. The result of our inner join, cards_with_mechanics_df
, conntains only 1079 rows. Why?
Write your answer below this line:
Not all cards are in the mechanics table. An inner join returns rows that have matching ids in both tables.
By default, the .join()
method performs a left join if no parameter is passed in for how=
. In the cell below, perform a Left Join of cards_with_mechanics_df
and play_requirements_df
, with cards_with_mechanics_df
as the left table.
Then, display left_join_df
to inspect our results.
left_join_df = cards_with_mechanics_df.join(play_requirements_df)
left_join_df
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
player_class | type | name | set | text | cost | attack | health | rarity | collectible | flavor | race | how_to_earn | how_to_earn_golden | targeting_arrow_text | faction | durability | mechanic | play_requirement | value | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
card_id | ||||||||||||||||||||
AT_002 | MAGE | SPELL | Effigy | TGT | <b>Secret:</b> When a friendly minion dies, su... | 3.0 | NaN | NaN | RARE | 1.0 | Burning man, brah. | None | None | None | None | None | NaN | SECRET | NaN | NaN |
AT_005t | NEUTRAL | MINION | Boar | TGT | <b>Charge</b> | 3.0 | 4.0 | 2.0 | None | NaN | None | BEAST | None | None | None | None | NaN | CHARGE | NaN | NaN |
AT_006 | MAGE | MINION | Dalaran Aspirant | TGT | <b>Inspire:</b> Gain <b>Spell Damage +1</b>. | 4.0 | 3.0 | 5.0 | COMMON | 1.0 | Is he aspiring or inspiring? Make up your mind! | None | None | None | None | None | NaN | INSPIRE | NaN | NaN |
AT_007 | MAGE | MINION | Spellslinger | TGT | <b>Battlecry:</b> Add a random spell to each p... | 3.0 | 3.0 | 4.0 | COMMON | 1.0 | Does he sling spells, or do his spells linger ... | None | None | None | None | None | NaN | BATTLECRY | NaN | NaN |
AT_009 | MAGE | MINION | Rhonin | TGT | <b>Deathrattle:</b> Add 3 copies of Arcane Mis... | 8.0 | 7.0 | 7.0 | LEGENDARY | 1.0 | A masterless shamurai. | None | None | None | None | None | NaN | DEATHRATTLE | NaN | NaN |
AT_010 | HUNTER | MINION | Ram Wrangler | TGT | <b>Battlecry:</b> If you have a Beast, summon ... | 5.0 | 3.0 | 3.0 | RARE | 1.0 | Not getting trampled is really the trick here. | None | None | None | None | None | NaN | BATTLECRY | NaN | NaN |
AT_011e | NEUTRAL | ENCHANTMENT | Light's Blessing | TGT | Increased Attack. | NaN | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | AURA | NaN | NaN |
AT_012 | PRIEST | MINION | Spawn of Shadows | TGT | <b>Inspire:</b> Deal 4 damage to each hero. | 4.0 | 5.0 | 4.0 | RARE | 1.0 | What did you expect to happen? He's a Spawn. ... | None | None | None | None | None | NaN | INSPIRE | NaN | NaN |
AT_017 | NEUTRAL | MINION | Twilight Guardian | TGT | <b>Battlecry:</b> If you're holding a Dragon, ... | 4.0 | 2.0 | 6.0 | EPIC | 1.0 | A result of magical experiments carried out by... | DRAGON | None | None | None | None | NaN | BATTLECRY | NaN | NaN |
AT_018 | PRIEST | MINION | Confessor Paletress | TGT | <b>Inspire:</b> Summon a random <b>Legendary</... | 7.0 | 5.0 | 4.0 | LEGENDARY | 1.0 | She sees into your past and makes you face you... | None | None | None | None | None | NaN | INSPIRE | NaN | NaN |
AT_019 | WARLOCK | MINION | Dreadsteed | TGT | <b>Deathrattle:</b> Summon a Dreadsteed. | 4.0 | 1.0 | 1.0 | EPIC | 1.0 | Crescendo himself summoned this steed, riding ... | DEMON | None | None | None | None | NaN | DEATHRATTLE | NaN | NaN |
AT_022 | WARLOCK | SPELL | Fist of Jaraxxus | TGT | When you play or discard this, deal $4 damage ... | 4.0 | NaN | NaN | RARE | 1.0 | * Not actually Jaraxxus' fist. | None | None | None | None | None | NaN | InvisibleDeathrattle | NaN | NaN |
AT_023 | WARLOCK | MINION | Void Crusher | TGT | <b>Inspire:</b> Destroy a random minion for ea... | 6.0 | 5.0 | 4.0 | RARE | 1.0 | We like to call him "Wesley". | DEMON | None | None | None | None | NaN | INSPIRE | NaN | NaN |
AT_028 | ROGUE | MINION | Shado-Pan Rider | TGT | <b>Combo:</b> Gain +3 Attack. | 5.0 | 3.0 | 7.0 | COMMON | 1.0 | He needed a break after that business in the V... | None | None | None | None | None | NaN | COMBO | NaN | NaN |
AT_030 | ROGUE | MINION | Undercity Valiant | TGT | <b>Combo:</b> Deal 1 damage. | 2.0 | 3.0 | 2.0 | COMMON | 1.0 | Almost went to play for Stormwind before signi... | None | None | None | Deal 1 damage. | None | NaN | COMBO | REQ_TARGET_FOR_COMBO | 0.0 |
AT_032 | ROGUE | MINION | Shady Dealer | TGT | <b>Battlecry:</b> If you have a Pirate, gain +... | 3.0 | 4.0 | 3.0 | RARE | 1.0 | I have great deal for you... for 4 damage to y... | None | None | None | None | None | NaN | BATTLECRY | NaN | NaN |
AT_035t | ROGUE | SPELL | Ambush! | TGT | When you draw this, summon a 4/4 Nerubian for ... | 0.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | TOPDECK | NaN | NaN |
AT_036 | ROGUE | MINION | Anub'arak | TGT | <b>Deathrattle:</b> Return this to your hand a... | 9.0 | 8.0 | 4.0 | LEGENDARY | 1.0 | Was actually a pretty nice guy before, you kno... | None | None | None | None | None | NaN | DEATHRATTLE | NaN | NaN |
AT_037 | DRUID | SPELL | Living Roots | TGT | <b>Choose One -</b> Deal $2 damage; or Summon ... | 1.0 | NaN | NaN | COMMON | 1.0 | 2 out of 2 saplings recommend that you summon ... | None | None | None | None | None | NaN | CHOOSE_ONE | REQ_TARGET_TO_PLAY | 0.0 |
AT_038 | DRUID | MINION | Darnassus Aspirant | TGT | <b>Battlecry:</b> Gain an empty Mana Crystal.\... | 2.0 | 2.0 | 3.0 | RARE | 1.0 | She loves mana crystals, she hates mana crysta... | None | None | None | None | None | NaN | BATTLECRY | NaN | NaN |
AT_038 | DRUID | MINION | Darnassus Aspirant | TGT | <b>Battlecry:</b> Gain an empty Mana Crystal.\... | 2.0 | 2.0 | 3.0 | RARE | 1.0 | She loves mana crystals, she hates mana crysta... | None | None | None | None | None | NaN | DEATHRATTLE | NaN | NaN |
AT_039 | DRUID | MINION | Savage Combatant | TGT | <b>Inspire:</b> Give your hero\n+2 Attack this... | 4.0 | 5.0 | 4.0 | RARE | 1.0 | Maybe if you whistle a tune it will soothe him... | BEAST | None | None | None | None | NaN | INSPIRE | NaN | NaN |
AT_039e | DRUID | ENCHANTMENT | Savage | TGT | +2 Attack this turn. | NaN | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | TAG_ONE_TURN_EFFECT | NaN | NaN |
AT_040 | DRUID | MINION | Wildwalker | TGT | <b>Battlecry:</b> Give a friendly Beast +3 Hea... | 4.0 | 4.0 | 4.0 | COMMON | 1.0 | She was born to be something. She is just not... | None | None | None | None | None | NaN | BATTLECRY | REQ_FRIENDLY_TARGET | 0.0 |
AT_040 | DRUID | MINION | Wildwalker | TGT | <b>Battlecry:</b> Give a friendly Beast +3 Hea... | 4.0 | 4.0 | 4.0 | COMMON | 1.0 | She was born to be something. She is just not... | None | None | None | None | None | NaN | BATTLECRY | REQ_TARGET_IF_AVAILABLE | 0.0 |
AT_040 | DRUID | MINION | Wildwalker | TGT | <b>Battlecry:</b> Give a friendly Beast +3 Hea... | 4.0 | 4.0 | 4.0 | COMMON | 1.0 | She was born to be something. She is just not... | None | None | None | None | None | NaN | BATTLECRY | REQ_TARGET_WITH_RACE | 20.0 |
AT_040 | DRUID | MINION | Wildwalker | TGT | <b>Battlecry:</b> Give a friendly Beast +3 Hea... | 4.0 | 4.0 | 4.0 | COMMON | 1.0 | She was born to be something. She is just not... | None | None | None | None | None | NaN | BATTLECRY | REQ_MINION_TARGET | 0.0 |
AT_042 | DRUID | MINION | Druid of the Saber | TGT | <b>Choose One -</b> Transform to gain <b>Charg... | 2.0 | 2.0 | 1.0 | COMMON | 1.0 | That's saberTEETH, not like curved pirate blad... | None | None | None | None | None | NaN | CHOOSE_ONE | NaN | NaN |
AT_042t | DRUID | MINION | Sabertooth Lion | TGT | <b>Charge</b> | 2.0 | 2.0 | 1.0 | COMMON | NaN | None | BEAST | None | None | None | None | NaN | CHARGE | NaN | NaN |
AT_042t2 | DRUID | MINION | Sabertooth Panther | TGT | <b>Stealth</b> | 2.0 | 3.0 | 2.0 | COMMON | NaN | None | BEAST | None | None | None | None | NaN | STEALTH | NaN | NaN |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
TB_SPT_DPromoSecret3 | WARRIOR | SPELL | Visions of Valor | TB | <b>Secret:</b> When your opponent summons a Le... | 2.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | SECRET | NaN | NaN |
TB_SPT_DPromoSecret4 | WARRIOR | SPELL | Visions of Fate | TB | <b>Secret:</b> When your opponent takes lethal... | 2.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | SECRET | NaN | NaN |
TB_SPT_DPromoSecret5 | WARRIOR | SPELL | Visions of the Amazon | TB | <b>Secret:</b> When your opponent summons a mi... | 2.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | SECRET | NaN | NaN |
TB_SPT_DPromoSecret6 | WARRIOR | SPELL | Visions of the Sorcerer | TB | <b>Secret:</b> When your opponent summons a mi... | 2.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | SECRET | NaN | NaN |
TB_SPT_DPromoSecret7 | WARRIOR | SPELL | Visions of the Necromancer | TB | <b>Secret:</b> When your opponent summons a mi... | 2.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | SECRET | NaN | NaN |
TB_SPT_DPromoSecret9 | WARRIOR | SPELL | Visions of Knowledge | TB | <b>Secret:</b> When your opponent's hand has 9... | 2.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | SECRET | NaN | NaN |
TB_SPT_DPromo_Hero2 | WARRIOR | HERO | The Cow King | TB | None | NaN | NaN | 30.0 | None | NaN | None | None | None | None | None | None | NaN | InvisibleDeathrattle | NaN | NaN |
TB_SPT_DpromoPortal | NEUTRAL | MINION | Enigmatic Portal | TB | At the start of next turn, your hero is transf... | 4.0 | 0.0 | 10.0 | None | NaN | None | None | None | None | None | None | NaN | BATTLECRY | NaN | NaN |
TB_SPT_DpromoPortal | NEUTRAL | MINION | Enigmatic Portal | TB | At the start of next turn, your hero is transf... | 4.0 | 0.0 | 10.0 | None | NaN | None | None | None | None | None | None | NaN | IMMUNE | NaN | NaN |
TB_SPT_Minion1 | NEUTRAL | MINION | Shieldsman | TB | <b>Taunt</b>\n<b>Battlecry:</b> Gain Health eq... | 2.0 | 0.0 | 1.0 | None | NaN | None | None | None | None | None | None | NaN | TAUNT | NaN | NaN |
TB_SPT_Minion2 | NEUTRAL | MINION | Battle Standard | TB | Adjacent minions have +2 Attack. | 2.0 | 0.0 | 2.0 | None | NaN | None | None | None | None | None | None | NaN | ADJACENT_BUFF | NaN | NaN |
TB_SPT_Minion2 | NEUTRAL | MINION | Battle Standard | TB | Adjacent minions have +2 Attack. | 2.0 | 0.0 | 2.0 | None | NaN | None | None | None | None | None | None | NaN | AURA | NaN | NaN |
TU4c_003 | NEUTRAL | MINION | Barrel | MISSIONS | Is something in this barrel? | 0.0 | 0.0 | 2.0 | COMMON | NaN | None | None | None | None | None | None | NaN | DEATHRATTLE | NaN | NaN |
TU4c_008e | NEUTRAL | ENCHANTMENT | Might of Mukla | MISSIONS | King Mukla has +8 Attack this turn. | NaN | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | TAG_ONE_TURN_EFFECT | NaN | NaN |
TU4f_007 | NEUTRAL | MINION | Crazy Monkey | MISSIONS | <b>Battlecry:</b> Throw Bananas. | 1.0 | 1.0 | 2.0 | COMMON | NaN | None | None | None | None | None | None | NaN | BATTLECRY | NaN | NaN |
XXX_001 | NEUTRAL | SPELL | Damage 1 | CHEAT | Deal $1 damage. | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower | REQ_TARGET_TO_PLAY | 0.0 |
XXX_002 | NEUTRAL | SPELL | Damage 5 | CHEAT | Deal $5 damage. | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower | REQ_TARGET_TO_PLAY | 0.0 |
XXX_008 | NEUTRAL | SPELL | Freeze | CHEAT | <b>Freeze</b> a character. | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | FREEZE | REQ_TARGET_TO_PLAY | 0.0 |
XXX_020 | NEUTRAL | SPELL | Damage all but 1 | CHEAT | Set the Health of a character to 1. | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower | REQ_TARGET_TO_PLAY | 0.0 |
XXX_060 | NEUTRAL | SPELL | Damage All | CHEAT | Set the Health of a character to 0. | 0.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower | REQ_TARGET_TO_PLAY | 0.0 |
XXX_100 | NEUTRAL | MINION | Yogg-Saron Test (Manual) | CHEAT | <b>Battlecry:</b> Cast each spell you've cast ... | 0.0 | 7.0 | 5.0 | LEGENDARY | NaN | None | None | None | None | None | None | NaN | BATTLECRY | NaN | NaN |
XXX_101 | NEUTRAL | SPELL | Set health to full | CHEAT | Set a character's health to full, and removes ... | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower | REQ_TARGET_TO_PLAY | 0.0 |
XXX_102 | NEUTRAL | SPELL | Add 1 to Health. | CHEAT | Adds 1 health to a damaged character. Does NOT... | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower | REQ_TARGET_TO_PLAY | 0.0 |
XXX_103 | NEUTRAL | SPELL | Add 2 to Health | CHEAT | Adds 2 health to a damaged character. Does NOT... | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower | REQ_TARGET_TO_PLAY | 0.0 |
XXX_104 | NEUTRAL | SPELL | Add 4 to Health. | CHEAT | Adds 4 health to a damaged character. Does NOT... | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower | REQ_TARGET_TO_PLAY | 0.0 |
XXX_105 | NEUTRAL | SPELL | Add 8 to Health. | CHEAT | Adds 8 health to a damaged character. Does NOT... | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower | REQ_TARGET_TO_PLAY | 0.0 |
XXX_107 | NEUTRAL | SPELL | Set Health to 1 | CHEAT | Set a character's health to 1, and remove all ... | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | ImmuneToSpellpower | REQ_TARGET_TO_PLAY | 0.0 |
XXX_110 | NEUTRAL | MINION | Yogg-Saron Test (Auto) | CHEAT | <b>Battlecry:</b> Cast 30 random spells <i>(ta... | 0.0 | 7.0 | 5.0 | LEGENDARY | NaN | None | None | None | None | None | None | NaN | BATTLECRY | NaN | NaN |
hexfrog | NEUTRAL | MINION | Frog | CORE | <b>Taunt</b> | 0.0 | 0.0 | 1.0 | COMMON | NaN | None | BEAST | None | None | None | None | NaN | TAUNT | NaN | NaN |
tt_010 | MAGE | SPELL | Spellbender | EXPERT1 | <b>Secret:</b> When an enemy casts a spell on ... | 3.0 | NaN | NaN | EPIC | 1.0 | While it's fun to intercept enemy lightning bo... | None | None | None | None | None | NaN | SECRET | NaN | NaN |
1226 rows × 20 columns
Note that the results of this sort of join are dependent upon the position of each table--if we were to make cards_with_mechanics_df
the right table and play_requirements_df
the left table and then perform a Right Join, our results would be the same.
Question:
Describe what was included from each table in this join.
Write your answer below this line:
Everything from cards_with_mechanics_df - but not shure why there are more rows than in the original table
In the cell below, perform an outer join between cards_df
and dust_df
. Since these tables contain columns with the same name, we'll need to specify a suffix for at least one of them, so that the column can be renamed to avoid a naming collision.
During this join, set the rsuffix
parameter to _dust
outer_join_df = cards_df.join(dust_df, how='outer', rsuffix='_dust')
outer_join_df
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
player_class | type | name | set | text | cost | attack | health | rarity | collectible | flavor | race | how_to_earn | how_to_earn_golden | targeting_arrow_text | faction | durability | action | cost_dust | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
card_id | |||||||||||||||||||
AT_001 | MAGE | SPELL | Flame Lance | TGT | Deal $8 damage to a minion. | 5.0 | NaN | NaN | COMMON | 1.0 | It's on the rack next to ice lance, acid lance... | None | None | None | None | None | NaN | CRAFTING_NORMAL | 40.0 |
AT_001 | MAGE | SPELL | Flame Lance | TGT | Deal $8 damage to a minion. | 5.0 | NaN | NaN | COMMON | 1.0 | It's on the rack next to ice lance, acid lance... | None | None | None | None | None | NaN | CRAFTING_GOLDEN | 400.0 |
AT_001 | MAGE | SPELL | Flame Lance | TGT | Deal $8 damage to a minion. | 5.0 | NaN | NaN | COMMON | 1.0 | It's on the rack next to ice lance, acid lance... | None | None | None | None | None | NaN | DISENCHANT_NORMAL | 5.0 |
AT_001 | MAGE | SPELL | Flame Lance | TGT | Deal $8 damage to a minion. | 5.0 | NaN | NaN | COMMON | 1.0 | It's on the rack next to ice lance, acid lance... | None | None | None | None | None | NaN | DISENCHANT_GOLDEN | 50.0 |
AT_002 | MAGE | SPELL | Effigy | TGT | <b>Secret:</b> When a friendly minion dies, su... | 3.0 | NaN | NaN | RARE | 1.0 | Burning man, brah. | None | None | None | None | None | NaN | CRAFTING_NORMAL | 100.0 |
AT_002 | MAGE | SPELL | Effigy | TGT | <b>Secret:</b> When a friendly minion dies, su... | 3.0 | NaN | NaN | RARE | 1.0 | Burning man, brah. | None | None | None | None | None | NaN | CRAFTING_GOLDEN | 800.0 |
AT_002 | MAGE | SPELL | Effigy | TGT | <b>Secret:</b> When a friendly minion dies, su... | 3.0 | NaN | NaN | RARE | 1.0 | Burning man, brah. | None | None | None | None | None | NaN | DISENCHANT_NORMAL | 20.0 |
AT_002 | MAGE | SPELL | Effigy | TGT | <b>Secret:</b> When a friendly minion dies, su... | 3.0 | NaN | NaN | RARE | 1.0 | Burning man, brah. | None | None | None | None | None | NaN | DISENCHANT_GOLDEN | 100.0 |
AT_003 | MAGE | MINION | Fallen Hero | TGT | Your Hero Power deals 1 extra damage. | 2.0 | 3.0 | 2.0 | RARE | 1.0 | And he can't get up. | None | None | None | None | None | NaN | CRAFTING_NORMAL | 100.0 |
AT_003 | MAGE | MINION | Fallen Hero | TGT | Your Hero Power deals 1 extra damage. | 2.0 | 3.0 | 2.0 | RARE | 1.0 | And he can't get up. | None | None | None | None | None | NaN | CRAFTING_GOLDEN | 800.0 |
AT_003 | MAGE | MINION | Fallen Hero | TGT | Your Hero Power deals 1 extra damage. | 2.0 | 3.0 | 2.0 | RARE | 1.0 | And he can't get up. | None | None | None | None | None | NaN | DISENCHANT_NORMAL | 20.0 |
AT_003 | MAGE | MINION | Fallen Hero | TGT | Your Hero Power deals 1 extra damage. | 2.0 | 3.0 | 2.0 | RARE | 1.0 | And he can't get up. | None | None | None | None | None | NaN | DISENCHANT_GOLDEN | 100.0 |
AT_004 | MAGE | SPELL | Arcane Blast | TGT | Deal $2 damage to a minion. This spell gets do... | 1.0 | NaN | NaN | EPIC | 1.0 | Now with 100% more blast! | None | None | None | None | None | NaN | CRAFTING_NORMAL | 400.0 |
AT_004 | MAGE | SPELL | Arcane Blast | TGT | Deal $2 damage to a minion. This spell gets do... | 1.0 | NaN | NaN | EPIC | 1.0 | Now with 100% more blast! | None | None | None | None | None | NaN | CRAFTING_GOLDEN | 1600.0 |
AT_004 | MAGE | SPELL | Arcane Blast | TGT | Deal $2 damage to a minion. This spell gets do... | 1.0 | NaN | NaN | EPIC | 1.0 | Now with 100% more blast! | None | None | None | None | None | NaN | DISENCHANT_NORMAL | 100.0 |
AT_004 | MAGE | SPELL | Arcane Blast | TGT | Deal $2 damage to a minion. This spell gets do... | 1.0 | NaN | NaN | EPIC | 1.0 | Now with 100% more blast! | None | None | None | None | None | NaN | DISENCHANT_GOLDEN | 400.0 |
AT_005 | MAGE | SPELL | Polymorph: Boar | TGT | Transform a minion into a 4/2 Boar with <b>Cha... | 3.0 | NaN | NaN | RARE | 1.0 | It's always Huffer. | None | None | None | None | None | NaN | CRAFTING_NORMAL | 100.0 |
AT_005 | MAGE | SPELL | Polymorph: Boar | TGT | Transform a minion into a 4/2 Boar with <b>Cha... | 3.0 | NaN | NaN | RARE | 1.0 | It's always Huffer. | None | None | None | None | None | NaN | CRAFTING_GOLDEN | 800.0 |
AT_005 | MAGE | SPELL | Polymorph: Boar | TGT | Transform a minion into a 4/2 Boar with <b>Cha... | 3.0 | NaN | NaN | RARE | 1.0 | It's always Huffer. | None | None | None | None | None | NaN | DISENCHANT_NORMAL | 20.0 |
AT_005 | MAGE | SPELL | Polymorph: Boar | TGT | Transform a minion into a 4/2 Boar with <b>Cha... | 3.0 | NaN | NaN | RARE | 1.0 | It's always Huffer. | None | None | None | None | None | NaN | DISENCHANT_GOLDEN | 100.0 |
AT_005t | NEUTRAL | MINION | Boar | TGT | <b>Charge</b> | 3.0 | 4.0 | 2.0 | None | NaN | None | BEAST | None | None | None | None | NaN | NaN | NaN |
AT_006 | MAGE | MINION | Dalaran Aspirant | TGT | <b>Inspire:</b> Gain <b>Spell Damage +1</b>. | 4.0 | 3.0 | 5.0 | COMMON | 1.0 | Is he aspiring or inspiring? Make up your mind! | None | None | None | None | None | NaN | CRAFTING_NORMAL | 40.0 |
AT_006 | MAGE | MINION | Dalaran Aspirant | TGT | <b>Inspire:</b> Gain <b>Spell Damage +1</b>. | 4.0 | 3.0 | 5.0 | COMMON | 1.0 | Is he aspiring or inspiring? Make up your mind! | None | None | None | None | None | NaN | CRAFTING_GOLDEN | 400.0 |
AT_006 | MAGE | MINION | Dalaran Aspirant | TGT | <b>Inspire:</b> Gain <b>Spell Damage +1</b>. | 4.0 | 3.0 | 5.0 | COMMON | 1.0 | Is he aspiring or inspiring? Make up your mind! | None | None | None | None | None | NaN | DISENCHANT_NORMAL | 5.0 |
AT_006 | MAGE | MINION | Dalaran Aspirant | TGT | <b>Inspire:</b> Gain <b>Spell Damage +1</b>. | 4.0 | 3.0 | 5.0 | COMMON | 1.0 | Is he aspiring or inspiring? Make up your mind! | None | None | None | None | None | NaN | DISENCHANT_GOLDEN | 50.0 |
AT_006e | MAGE | ENCHANTMENT | Power of Dalaran | TGT | Increased Spell Damage. | NaN | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | NaN | NaN |
AT_007 | MAGE | MINION | Spellslinger | TGT | <b>Battlecry:</b> Add a random spell to each p... | 3.0 | 3.0 | 4.0 | COMMON | 1.0 | Does he sling spells, or do his spells linger ... | None | None | None | None | None | NaN | CRAFTING_NORMAL | 40.0 |
AT_007 | MAGE | MINION | Spellslinger | TGT | <b>Battlecry:</b> Add a random spell to each p... | 3.0 | 3.0 | 4.0 | COMMON | 1.0 | Does he sling spells, or do his spells linger ... | None | None | None | None | None | NaN | CRAFTING_GOLDEN | 400.0 |
AT_007 | MAGE | MINION | Spellslinger | TGT | <b>Battlecry:</b> Add a random spell to each p... | 3.0 | 3.0 | 4.0 | COMMON | 1.0 | Does he sling spells, or do his spells linger ... | None | None | None | None | None | NaN | DISENCHANT_NORMAL | 5.0 |
AT_007 | MAGE | MINION | Spellslinger | TGT | <b>Battlecry:</b> Add a random spell to each p... | 3.0 | 3.0 | 4.0 | COMMON | 1.0 | Does he sling spells, or do his spells linger ... | None | None | None | None | None | NaN | DISENCHANT_GOLDEN | 50.0 |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
XXX_110 | NEUTRAL | MINION | Yogg-Saron Test (Auto) | CHEAT | <b>Battlecry:</b> Cast 30 random spells <i>(ta... | 0.0 | 7.0 | 5.0 | LEGENDARY | NaN | None | None | None | None | None | None | NaN | NaN | NaN |
XXX_111 | NEUTRAL | SPELL | AI Buddy - All Charge, All Windfury! | CHEAT | Play this card to give all minions <b>Charge</... | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | NaN | NaN |
XXX_111e | NEUTRAL | ENCHANTMENT | All Charge, All Windfury, All The Time | CHEAT | Your minions always have <b>Charge</b> and <b>... | NaN | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | NaN | NaN |
XXX_112 | NEUTRAL | SPELL | Fill Deck | CHEAT | Fill target hero's deck with random cards. | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | NaN | NaN |
XXX_113 | NEUTRAL | SPELL | Again | CHEAT | None | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | NaN | NaN |
XXX_115 | NEUTRAL | SPELL | Destroy Played Cards | CHEAT | Whenever a player summons a minion, destroy it. | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | NaN | NaN |
XXX_115e | NEUTRAL | ENCHANTMENT | Destroy Played Card Enchantment | CHEAT | Whenever a player summons a minion, destroy it. | NaN | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | NaN | NaN |
XXX_119 | NEUTRAL | SPELL | Death No Rattle | CHEAT | Died without triggering <b>Deathrattle</b>, Al... | 0.0 | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | NaN | NaN |
XXX_119e | None | ENCHANTMENT | Death No Rattle | CHEAT | Died without triggering <b>Deathrattle</b>, Al... | NaN | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | NaN | NaN |
XXX_999_Crash | NEUTRAL | SPELL | Crash the server | CHEAT | Crash the server | 0.0 | NaN | NaN | COMMON | NaN | None | None | None | None | None | None | NaN | NaN | NaN |
ds1_whelptoken | NEUTRAL | MINION | Whelp | EXPERT1 | None | 1.0 | 1.0 | 1.0 | None | NaN | None | DRAGON | None | None | None | None | NaN | NaN | NaN |
hexfrog | NEUTRAL | MINION | Frog | CORE | <b>Taunt</b> | 0.0 | 0.0 | 1.0 | COMMON | NaN | None | BEAST | None | None | None | None | NaN | NaN | NaN |
skele11 | NEUTRAL | MINION | Skeleton | CORE | <b></b> | 1.0 | 1.0 | 1.0 | COMMON | NaN | None | None | None | None | None | None | NaN | NaN | NaN |
skele21 | NEUTRAL | MINION | Damaged Golem | EXPERT1 | None | 1.0 | 2.0 | 1.0 | COMMON | NaN | None | MECHANICAL | None | None | None | None | NaN | CRAFTING_NORMAL | 40.0 |
skele21 | NEUTRAL | MINION | Damaged Golem | EXPERT1 | None | 1.0 | 2.0 | 1.0 | COMMON | NaN | None | MECHANICAL | None | None | None | None | NaN | CRAFTING_GOLDEN | 400.0 |
skele21 | NEUTRAL | MINION | Damaged Golem | EXPERT1 | None | 1.0 | 2.0 | 1.0 | COMMON | NaN | None | MECHANICAL | None | None | None | None | NaN | DISENCHANT_NORMAL | 5.0 |
skele21 | NEUTRAL | MINION | Damaged Golem | EXPERT1 | None | 1.0 | 2.0 | 1.0 | COMMON | NaN | None | MECHANICAL | None | None | None | None | NaN | DISENCHANT_GOLDEN | 50.0 |
tt_004 | NEUTRAL | MINION | Flesheating Ghoul | EXPERT1 | Whenever a minion dies, gain +1 Attack. | 3.0 | 2.0 | 3.0 | COMMON | 1.0 | 'Flesheating' is an unfair name. It's just th... | None | None | None | None | None | NaN | CRAFTING_NORMAL | 40.0 |
tt_004 | NEUTRAL | MINION | Flesheating Ghoul | EXPERT1 | Whenever a minion dies, gain +1 Attack. | 3.0 | 2.0 | 3.0 | COMMON | 1.0 | 'Flesheating' is an unfair name. It's just th... | None | None | None | None | None | NaN | CRAFTING_GOLDEN | 400.0 |
tt_004 | NEUTRAL | MINION | Flesheating Ghoul | EXPERT1 | Whenever a minion dies, gain +1 Attack. | 3.0 | 2.0 | 3.0 | COMMON | 1.0 | 'Flesheating' is an unfair name. It's just th... | None | None | None | None | None | NaN | DISENCHANT_NORMAL | 5.0 |
tt_004 | NEUTRAL | MINION | Flesheating Ghoul | EXPERT1 | Whenever a minion dies, gain +1 Attack. | 3.0 | 2.0 | 3.0 | COMMON | 1.0 | 'Flesheating' is an unfair name. It's just th... | None | None | None | None | None | NaN | DISENCHANT_GOLDEN | 50.0 |
tt_004o | NEUTRAL | ENCHANTMENT | Cannibalize | EXPERT1 | Increased Attack. | NaN | NaN | NaN | None | NaN | None | None | None | None | None | None | NaN | NaN | NaN |
tt_010 | MAGE | SPELL | Spellbender | EXPERT1 | <b>Secret:</b> When an enemy casts a spell on ... | 3.0 | NaN | NaN | EPIC | 1.0 | While it's fun to intercept enemy lightning bo... | None | None | None | None | None | NaN | CRAFTING_NORMAL | 400.0 |
tt_010 | MAGE | SPELL | Spellbender | EXPERT1 | <b>Secret:</b> When an enemy casts a spell on ... | 3.0 | NaN | NaN | EPIC | 1.0 | While it's fun to intercept enemy lightning bo... | None | None | None | None | None | NaN | CRAFTING_GOLDEN | 1600.0 |
tt_010 | MAGE | SPELL | Spellbender | EXPERT1 | <b>Secret:</b> When an enemy casts a spell on ... | 3.0 | NaN | NaN | EPIC | 1.0 | While it's fun to intercept enemy lightning bo... | None | None | None | None | None | NaN | DISENCHANT_NORMAL | 100.0 |
tt_010 | MAGE | SPELL | Spellbender | EXPERT1 | <b>Secret:</b> When an enemy casts a spell on ... | 3.0 | NaN | NaN | EPIC | 1.0 | While it's fun to intercept enemy lightning bo... | None | None | None | None | None | NaN | DISENCHANT_GOLDEN | 400.0 |
tt_010a | MAGE | MINION | Spellbender | EXPERT1 | None | 1.0 | 1.0 | 3.0 | EPIC | NaN | None | None | None | None | None | None | NaN | CRAFTING_NORMAL | 400.0 |
tt_010a | MAGE | MINION | Spellbender | EXPERT1 | None | 1.0 | 1.0 | 3.0 | EPIC | NaN | None | None | None | None | None | None | NaN | CRAFTING_GOLDEN | 1600.0 |
tt_010a | MAGE | MINION | Spellbender | EXPERT1 | None | 1.0 | 1.0 | 3.0 | EPIC | NaN | None | None | None | None | None | None | NaN | DISENCHANT_NORMAL | 100.0 |
tt_010a | MAGE | MINION | Spellbender | EXPERT1 | None | 1.0 | 1.0 | 3.0 | EPIC | NaN | None | None | None | None | None | None | NaN | DISENCHANT_GOLDEN | 400.0 |
5849 rows × 19 columns
Inspect the output above. Note that the naming collision has been avoided by renaming the cost
column from the right table to cost_dust
.
In this lab, we learned how to:
- Concatenate multiple DataFrames together into a single DataFrame
- Use
sqlalchemy
to connect to a sqlite database and read in individual tables as DataFrames - Understand and execute the various types of joins (inner, outer, left, and right joins)