filling specific values in columns based on filtering strings in another column in pandas

user5OKX 0
Viewed 17

Assume that we have the following dataframe

df1
Full code    Semi-code    Score
1111-ABC     1111         1 
0000-ABC     0000         2
AP00-ABC     AP00         1
1234-XYZ     1234         2

and I want to create the following dataframe

df2
Semi-Code    ABC         XYZ
1111          1          nan
0000          2          nan
AP00          1          nan
1324          nan        2

Basically the new dataframe takes the Semi-Code for df1 and then"

  • creates 2 new columns (1 for each of the codes that you see in the Full code thus ABC and XYZ
  • then it places the score under the correct column and nan in the other

Any ideas how can I do it without using for loops?

dataframe filter pandas python
1 Answers

This is a variation on a pivot:

(df.assign(col=df['Full code'].str.extract('([^-]+$)', expand=False))
   .pivot('Semi-code', 'col', 'Score')
   .reset_index().rename_axis(columns=None)
)

output:

  Semi-code  ABC  XYZ
0      0000  2.0  NaN
1      1111  1.0  NaN
2      1234  NaN  2.0
3      AP00  1.0  NaN

If order is important, use pivot_table with sort=False:

(df.assign(col=df['Full code'].str.extract('([^-]+$)', expand=False))
   .reset_index()
   .pivot_table(index='Semi-code', columns='col', values='Score', sort=False)
   .reset_index().rename_axis(columns=None)
)

output:

  Semi-code  ABC  XYZ
0      1111  1.0  NaN
1      0000  2.0  NaN
2      AP00  1.0  NaN
3      1234  NaN  2.0
mozway0
Related