Best way to manipulate a dataframe to change column values?

Question

My Current Dataframe looks like the below, I am having trouble changing the "fan of sport?" column to what i want it to look like.

import pandas as pd
df = pd.DataFrame([[1, "John","fb: Y, bb: N, cbb: N"], [2, "Joe","fb: Y, bb: N, cbb: Y"], [3, "Jess","fb: N, bb: Y, cbb: N"], [4,"James","fb: Y, bb: N, cbb: Y "]], columns=['id', 'name', "fan of sport"])

id |  name   |   fan of sport?      | 
1    John     fb: Y, bb: N, cbb: N 
2    Joe      fb: Y, bb: N, cbb: Y  
3    Jess     fb: N, bb: Y, cbb: N 
4    James    fb: Y, bb: N, cbb: Y

Expected Dataframe

df = pd.DataFrame([[1, "John","|| fb || bb || cbb || | Y | N | N |"], [2, "Joe","|| fb || bb || cbb || | Y | N | Y |"], [3, "Jess","|| fb || bb || cbb || | N | Y | N |"], [4,"James","|| fb || bb || cbb || | Y | N | Y | "]], columns=['id', 'name', "fan of sport"])

id |  name   |   fan of sport?      | 
1    John     || fb || bb || cbb || | Y | N | N |

2    Joe      || fb || bb || cbb || | Y | N | Y |

3    Jess     || fb || bb || cbb || | N | Y | N | 

4    James    || fb || bb || cbb || | Y | N | Y |

https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples — Paul H, Dec 02 '20 at 01:33
do you really want all of those pipe characters (`|`) in the dataframe? — Paul H, Dec 02 '20 at 01:53
@muchappreciated please accept one of the answers as solution by clicking the checkmark next to my answer. Thank you! — David Erickson, Dec 10 '20 at 05:46

score 0 · Answer 1 · answered Dec 02 '20 at 01:38

0

It looks like you want to replace : with ||

For this, you can use the str.replace(':','||')

Example:

df['fan of sport?'] = df['fan of sport?'].str.replace(':','||')

The code basically replaces your current column with the new one. Just repeat for the other columns.

answered Dec 02 '20 at 01:38

Pat Keenan

22
3

The order would change as well. Rather than fb: Y, bb: N, cbb: N it would change to || fb || bb || cbb || | Y | N | N| – muchappreciated Dec 02 '20 at 01:42

score 0 · Answer 2 · answered Dec 02 '20 at 01:49

You can first remove the Y and N with replace to return fb, bb, cbbb, etc. Then, use re.findall to get all of the Ys and Ns.

df['fan of sport?'] = ('|| ' + df['fan of sport?'].replace(['Y', 'N', ',', ':'], 
                                                            ['||', '||', '', ''], regex=True)
                        + ' | ' + df['fan of sport?'].apply(lambda x: ' | '.join(re.findall('[Y|N]+', x))) + ' |')
df
Out[1]: 
   id   name                        fan of sport?
0   1   John  || fb || bb || cbb || | Y | N | N |
1   2    Joe  || fb || bb || cbb || | Y | N | Y |
2   3   Jess  || fb || bb || cbb || | N | Y | N |
3   4  James  || fb || bb || cbb || | Y | N | Y |

is there a better way to implement this concept for results that don't yield Y/N as the result? let's say we had integers — muchappreciated, Dec 02 '20 at 01:53
@muchappreciated integers you would use the regex `\d+` to capture. — David Erickson, Dec 02 '20 at 01:53

Best way to manipulate a dataframe to change column values?

2 Answers2