Pandas creating new column (Issue)

user_17510530 0
Viewed 27

From Kaggle I downloaded some data about LaLiga (and its results) throughout the years. There´s a column named 'Score' (for instance: 1-1) and I want to create a new column called 'Total Goals'. As I try to apply my beginner pandas skills, the only thing I manage to pop up is a column with the value 'NaN'

df['Total Goals'] = df['Score'].apply(lambda x: x.split('-'))

df['Total Goals'] = pd.to_numeric(df['Total Goals'], errors='coerce')
df['Total Goals'] = df['Total Goals'][0]+ df['Total Goals'][1]
df.head()

Unfortunately I couldn´t figure out the exact issue and now want to ask you where the problem is.

pandas python
1 Answers

If I assume your source data looks a bit like this:

df = pd.DataFrame({'Score':['1-1','0-1','2-2']})

then you can simply do:

df['Total Goals'] = df['Score'].apply(lambda x: sum( [int(y) for y in x.split('-')] ))

This iterates over the list items created by the split using a list comprehension before summing them. Result:

    Score   Total Goals
0     1-1             2
1     0-1             1
2     2-2             4

For very large datasets the apply() may be too slow, in that case it may be better to capture both values into separate columns before summing them (starting from the same dataset above):

df[['sc1','sc2']] = df['Score'].str.split('-', 1, expand=True).astype(int)
df['Total Goals'] = df.sc1+df.sc2

In the best Pandas style, this can be magically combined, going straight to the desired result:

df['Total Goals'] = df['Score'].str.split('-', 1, expand=True).astype(int).sum(axis=1)
user_20090470
Related