I have this data in a pandas.DataFrame:
Date, Team1, Team2, Team1 Score, Team2 Score, Event
8/2/17, Juventus, Milan, 2, 1, Friendly match
6/2/17, Milan, Napoli, 3, 0, Friendly match
5/1/17, Milan, Sampdoria, 1, 0, Friendly match
25/12/16, Parma, Milan, 0, 5, Friendly match
How I can make a list of Milan scored goals?
The output should look like::
[1, 3, 1, 5]
You can use numpy arrays' boolean indexing, here use values to get a 2D numpy array and use boolean indexing to get the values where Team is Milan:
df[["Team1 Score", "Team2 Score"]].values[df[["Team1", "Team2"]] == "Milan"]
# array([1, 3, 1, 5])
filter function ;)# slice df with just team columns and get values
t = df[['Team1', 'Team2']].values
# find the row and column slices where equal to 'Milan'
i, j = np.where(t == 'Milan')
# then slice the scores array with those positions
s = df[['Team1 Score', 'Team2 Score']].values
s[i, j]
array([1, 3, 1, 5])
I can compress this further because I know where all the columns are
v = df.values
i, j = np.where(v[:, [1, 2]] == 'Milan')
v[:, [3, 4]][i, j]
array([1, 3, 1, 5])
Milano squadra mia
df['tmp1'] = df.loc[df.Team1 == 'Milan', 'Team1 Score']
df['tmp2'] = df.loc[df.Team2 == 'Milan', 'Team2 Score']
df['milazzo'] = df.tmp1.fillna(0) + df.tmp2.fillna(0)
df.milazzo.tolist()
In [73]: df.milazzo.tolist()
Out[73]: [1.0, 3.0, 1.0, 5.0]
This will do the job:
pd.concat([df["Team1 Score"][df.Team1=='Milan'],df["Team2 Score"][df.Team2=='Milan']]).sort_index().values.tolist()
The output is [1, 3, 1, 5]
You can use pandas.DataFrame.apply() with a function to return a match for the team in either column.
Code:
def get_team_score(team):
def f(row):
if row.Team1 == team:
return row['Team1 Score']
if row.Team2 == team:
return row['Team2 Score']
return f
Test Code:
from io import StringIO
df = pd.read_csv(data)
print(df)
print(df.apply(get_team_score('Milan'), axis=1).values)
Test Data:
import pandas as pd
data = StringIO(u"""Date,Team1,Team2,Team1 Score,Team2 Score,Event
8/2/17,Juventus,Milan,2,1,Friendly match
6/2/17,Milan,Napoli,3,0,Friendly match
5/1/17,Milan,Sampdoria,1,0,Friendly match
25/12/16,Parma,Milan,0,5,Friendly match
""")
Results:
Date Team1 Team2 Team1 Score Team2 Score Event
0 8/2/17 Juventus Milan 2 1 Friendly match
1 6/2/17 Milan Napoli 3 0 Friendly match
2 5/1/17 Milan Sampdoria 1 0 Friendly match
3 25/12/16 Parma Milan 0 5 Friendly match
[1 3 1 5]
You can also use apply:
outlist = df[(df['Team1'] == 'Milan') | (df['Team2'] == 'Milan')].apply(
lambda k: k['Team1 Score'] if k['Team1'] == 'Milan' else k['Team2 Score'], axis=1
).tolist()
