Python: How can I extend a DataFrame with multiply fields that calculated from a column

user_8868108 0
Viewed 114

I have a datadrame which looks like:

     A    B 
0  2.0  'C=4;D=5;'
1  2.0  'C=4;D=5;'
2  2.0  'C=4;D=5;'

I can parse the string in column B, lets say using a function name parse_col(), in to a dict that looks like:

{C: 4, D: 5}

How can I add the 2 extra column to the data frame so it would look like that:

     A    B          C   D
0  2.0  'C=4;D=5;'   4   5
1  2.0  'C=4;D=5;'   4   5
2  2.0  'C=4;D=5;'   4   5

I can take only the specific column, parse it and add it but its clearly not the best way.
I also tried using a variation of the example in pandas apply documentation but I didn't manage to make it work only on a specific column.

pandas python
3 Answers

We can use Series.str.extractall and then chain it with unstack to pivot the rows to columns:

df[['C', 'D']] = df['B'].str.extractall('(\d+)').unstack()

     A           B  C  D
0  2.0  'C=4;D=5;'  4  5
1  2.0  'C=4;D=5;'  4  5
2  2.0  'C=4;D=5;'  4  5
user_90812670

You can use df.eval and functools.reduce, this way you can read the column names directly:

>>> from functools import reduce
>>> reduce(
            lambda x,y: x.eval(y),
            df.B.str
                .extractall(r'([A-Za-z]=\d+)')
                .unstack().xs(0), df
            )

     A           B  C  D
0  2.0  'C=4;D=5;'  4  5
1  2.0  'C=4;D=5;'  4  5
2  2.0  'C=4;D=5;'  4  5
user_54317910

You can use a named aggregation to extract the column name and the value associated with it. Then reshape and join it back.

df1 = (df['B'].str.extractall(r'(?P<col>[A-Za-z]+)=(?P<val>\d+);')
              .reset_index(1, drop=True)
              .pivot(columns='col', values='val'))

pd.concat([df, df1], axis=1)

     A         B  C  D
0  2.0  C=4;D=5;  4  5
1  2.0  C=4;D=5;  4  5
2  2.0  C=4;D=5;  4  5

One added benefit of this method is it's a bit safer if column 'B' can contain an arbitrary number of columns you need to assign. More importantly, the extraction of Column=Number will be correct even if values are unordered in column 'B'. Here's an extended example:

print(df)
     A    B 
0  2.0  C=4;D=5;
1  2.0  C=4;D=5;
2  2.0  C=4;D=5;
3  2.0  D=5;E=7;C=12;
4  2.0  D=1;C=4;

df1 = (df['B'].str.extractall(r'(?P<col>[A-Za-z]+)=(?P<val>\d+);')
              .reset_index(1, drop=True)
              .pivot(columns='col', values='val'))

pd.concat([df, df1], axis=1)
#     A              B   C  D    E
#0  2.0       C=4;D=5;   4  5  NaN
#1  2.0       C=4;D=5;   4  5  NaN
#2  2.0       C=4;D=5;   4  5  NaN
#3  2.0  D=5;E=7;C=12;  12  5    7
#4  2.0       D=1;C=4;   4  1  NaN
user_43333590
Related