how to find index of equal values between non equal columns pandas
I have 3 data sets,( of game of thrones) For each battle i need to print the "Allegiances" (from the character_deaths data frame) and the "isMarried" (from the character_predictions data frame) of the defender_king and the attacker_king.
so actually i need to find indexes of equal names in two different columns in two different data sets .
i tried something like:
import pandas as pd
battles=pd.read_csv('https://github.com/TheMLGuy/Game-of-Thrones-Dataset/raw/master/battles.csv')
character_deaths=pd.read_csv('https://github.com/TheMLGuy/Game-of-Thrones-Dataset/raw/master/character-deaths.csv')
character_predictions=pd.read_csv('https://github.com/TheMLGuy/Game-of-Thrones-Dataset/raw/master/character-predictions.csv')
dfnd_chr_idx=[np.where(character_predictions.loc[:,'name']==battles.loc[i,'defender_king'])[0] for i in range(len(battles))]
#and to take:
character_deaths[dfnd_chr_idx,'Allegiances']..
but its not working well- if you have more elegant solutions i'd be happy to hear.
You could check by merging:
import pandas as pd
battles=pd.read_csv('https://github.com/TheMLGuy/Game-of-Thrones-Dataset/raw/master/battles.csv')
character_deaths=pd.read_csv('https://github.com/TheMLGuy/Game-of-Thrones-Dataset/raw/master/character-deaths.csv')
character_predictions=pd.read_csv('https://github.com/TheMLGuy/Game-of-Thrones-Dataset/raw/master/character-predictions.csv')
merged = pd.merge(battles, character_predictions, right_on=['name'], left_on =['defender_king'], how='inner')
which gives:
name_x year battle_number \
0 Battle of the Golden Tooth 298 1
1 Battle at the Mummer's Ford 298 2
2 Battle of Riverrun 298 3
3 Sack of Darry 298 7
4 Battle of Moat Cailin 299 8
5 Battle of Deepwood Motte 299 9
6 Battle of the Stony Shore 299 10
7 Battle of Winterfell 299 12
8 Sack of Winterfell 299 14
9 Battle of the Fords 299 17
10 Battle of the Ruby Ford 299 24
11 The Red Wedding 299 26
12 Siege of Riverrun 300 36
13 Siege of Raventree 300 37
14 Siege of Storm's End 299 16
15 Battle of Castle Black 300 28
16 Second Seige of Storm's End 300 34
17 Siege of Dragonstone 300 35
attacker_king defender_king attacker_1 attacker_2 \
0 Joffrey/Tommen Baratheon Robb Stark Lannister NaN
1 Joffrey/Tommen Baratheon Robb Stark Lannister NaN
2 Joffrey/Tommen Baratheon Robb Stark Lannister NaN
3 Joffrey/Tommen Baratheon Robb Stark Lannister NaN
4 Balon/Euron Greyjoy Robb Stark Greyjoy NaN
5 Balon/Euron Greyjoy Robb Stark Greyjoy NaN
6 Balon/Euron Greyjoy Robb Stark Greyjoy NaN
7 Balon/Euron Greyjoy Robb Stark Greyjoy NaN
8 Joffrey/Tommen Baratheon Robb Stark Bolton Greyjoy
9 Joffrey/Tommen Baratheon Robb Stark Lannister NaN
10 Joffrey/Tommen Baratheon Robb Stark Lannister NaN
11 Joffrey/Tommen Baratheon Robb Stark Frey Bolton
12 Joffrey/Tommen Baratheon Robb Stark Lannister Frey
13 Joffrey/Tommen Baratheon Robb Stark Bracken Lannister
14 Stannis Baratheon Renly Baratheon Baratheon NaN
15 Stannis Baratheon Mance Rayder Free folk Thenns
16 Joffrey/Tommen Baratheon Stannis Baratheon Baratheon NaN
17 Joffrey/Tommen Baratheon Stannis Baratheon Baratheon NaN
attacker_3 attacker_4 defender_1 ... isAliveHeir isAliveSpouse \
0 NaN NaN Tully ... 1.0 NaN
1 NaN NaN Baratheon ... 1.0 NaN
2 NaN NaN Tully ... 1.0 NaN
3 NaN NaN Darry ... 1.0 NaN
4 NaN NaN Stark ... 1.0 NaN
5 NaN NaN Stark ... 1.0 NaN
6 NaN NaN Stark ... 1.0 NaN
7 NaN NaN Stark ... 1.0 NaN
8 NaN NaN Stark ... 1.0 NaN
9 NaN NaN Tully ... 1.0 NaN
10 NaN NaN Stark ... 1.0 NaN
11 NaN NaN Stark ... 1.0 NaN
12 NaN NaN Tully ... 1.0 NaN
13 NaN NaN Blackwood ... 1.0 NaN
14 NaN NaN Baratheon ... NaN 1.0
15 Giants NaN Night's Watch ... NaN NaN
16 NaN NaN Baratheon ... 1.0 NaN
17 NaN NaN Baratheon ... 1.0 NaN
isMarried isNoble age numDeadRelations boolDeadRelations isPopular \
0 0 0 NaN 7 1 1
1 0 0 NaN 7 1 1
2 0 0 NaN 7 1 1
3 0 0 NaN 7 1 1
4 0 0 NaN 7 1 1
5 0 0 NaN 7 1 1
6 0 0 NaN 7 1 1
7 0 0 NaN 7 1 1
8 0 0 NaN 7 1 1
9 0 0 NaN 7 1 1
10 0 0 NaN 7 1 1
11 0 0 NaN 7 1 1
12 0 0 NaN 7 1 1
13 0 0 NaN 7 1 1
14 1 1 22.0 2 1 1
15 0 0 NaN 0 0 1
16 0 0 NaN 4 1 1
17 0 0 NaN 4 1 1
popularity isAlive
0 1.00000 0
1 1.00000 0
2 1.00000 0
3 1.00000 0
4 1.00000 0
5 1.00000 0
6 1.00000 0
7 1.00000 0
8 1.00000 0
9 1.00000 0
10 1.00000 0
11 1.00000 0
12 1.00000 0
13 1.00000 0
14 1.00000 0
15 0.70903 1
16 1.00000 1
17 1.00000 1
[18 rows x 58 columns]