Pandas: Splitting a column by delimiter and re-arrenging based on other columns

user_6704785 0
Viewed 380

Let df be a data frame.

In [1]: import pandas as pd
   ...: df = pd.DataFrame(columns = ['Home', 'Score', 'Away'])
   ...: df.loc[0] = ['Team A', '3-1', 'Team B']
   ...: df.loc[1] = ['Team B', '2-1', 'Team A']
   ...: df.loc[2] = ['Team B', '2-2', 'Team A']
   ...: df.loc[3] = ['Team A', '0-1', 'Team B']

In [2]: df
Out[2]:
     Home Score    Away
0  Team A   3-1  Team B
1  Team B   2-1  Team A
2  Team B   2-2  Team A
3  Team A   0-1  Team B

I want to make df_1 out of df.

In [4]: df_1
Out[4]:
  Team A Team B
0      3      1
1      1      2
2      2      2
3      0      1

What is the easiest way?

As a beginner, I can split the 'Score' column into two columns and then loop over the other columns and get df_1, but I guess there should be an easier way of doing that, probably with a lambda function or group_by method.

Any ideas?

pandas filter group-by python split
2 Answers

If it is just two teams, we can revert the score if needed.

Where functions in the following way, if the condition is true, it keeps original value. If not, it can call input value from a list of values. Our condition is on the team and mapper is a reversal of a string.

l_rev_string = lambda s: s[::-1]

df_score_rev = df.Score.apply(l_rev_string)

df1 = df.Score.where(df.Home == 'Team A', df_score_rev)\
    .str.split('-',expand=True)\
    .rename(columns = {0:'Team A',1:'Team B'})



|    |   Team A |   Team B |
|---:|---------:|---------:|
|  0 |        3 |        1 |
|  1 |        1 |        2 |
|  2 |        2 |        2 |
|  3 |        0 |        1 |
user_87921520

You can try this:

df["values"] = df.apply(lambda row: {row["Home"]:row["Score"].split("-")[0], row["Away"]:row["Score"].split("-")[1]}, axis=1)

output_df = pd.DataFrame(df["values"].tolist())

Output:

    Team A  Team B
0   3   1
1   1   2
2   2   2
3   0   1
user_56470380
Related