Creating new Columns and fill them based on another columns values

Question

Let's say I have a dataframe df looking like this:

|ColA     |
|---------|
|B=7      |
|(no data)|
|C=5      |
|B=3,C=6  |

How do I extract the data into new colums, so it looks like this:

|ColA  | B | C |
|------|---|---|
|True  | 7 |   |
|False |   |   |
|True  |   | 5 |
|True  | 3 | 6 |

For filling the columns I know I can use regex .extract, as shown in this solution.

But how do I set the Column name at the same time? So far I use a loop over df.ColA.loc[df["ColA"].isna()].iteritems(), but that does not seem like the best option for a lot of data.

mozway · Accepted Answer · 2021-10-25 07:13:42Z

1

You could use str.extractall to get the data, then reshape the output and join to a derivative of the original dataframe:

# create the B/C columns
df2 = (df['ColA'].str.extractall('([^=]+)=([^=,]+),?')
                 .set_index(0, append=True)
                 .droplevel('match')[1]
                 .unstack(0, fill_value='')
       )

# rework ColA and join previous output
df.notnull().join(df2).fillna('')

# or if several columns:
df.assign(ColA=df['ColA'].notnull()).join(df2).fillna('')

output:

    ColA  B  C
0   True  7   
1  False      
2   True     5
3   True  3  6

edited Oct 25, 2021 at 7:13

answered Oct 25, 2021 at 6:55

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jan T. Over a year ago

Hi, this looks good already. But if I have multiple columns in df then it sets all column values as 'True'. Could you also explain why you use (~df.isnull())? Thanks!

mozway Over a year ago

Then you can use df.assign(ColA=~df['ColA'].isnull()).join(…), the ~ is to invert the result of isnull but one can actually use notnull ;)

Collectives™ on Stack Overflow

Creating new Columns and fill them based on another columns values

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related