Adding columns to DataFrame extracted from string expression

Question

I have a DataFrame with a column keyboardInfo containing strings as follows

keyboardInfo
[alphabet de_CH:swiss 1080x667 text actionNone isMultiLine]
[alphabet de_CH:swiss 1080x667 text actionNone isMultiLine]
[alphabetAutomaticShifted de_CH:swiss 1080x667 text actionNone isMultiLine]
[alphabet de_CH:swiss 720x440 text actionNone isMultiLine]

The DataFrame is pretty big (around 5'000'000 rows). The third entry of each string (after the second white space) is always the width x height (but in total the string can contain different amounts of elements, that means it should be counted from the left). Now, I would like to add two additional columns to the DataFrame containing the width and height (as integers). The results should look as follows (the DataFrame contains also other columns which I droped here):

keyboardInfo                                                                    width    height
[alphabet de_CH:swiss 1080x667 text actionNone isMultiLine]                     1080     667
[alphabet de_CH:swiss 1080x667 text actionNone isMultiLine]                     1080     667
[alphabetAutomaticShifted de_CH:swiss 1080x667 text actionNone isMultiLine]     1080     667
[alphabet de_CH:swiss 720x440 text actionNone isMultiLine]                      720      440

How can this be done efficiently?

Are there more numbers in the text besides the width and the height? — Erfan
– Erfan, Commented Sep 27, 2020 at 23:42
@Erfan Most likely not but it is safer to split according to white spaces I guess. — machinery
– machinery, Commented Sep 27, 2020 at 23:47

Erfan · Accepted Answer · 2020-09-27 23:46:08Z

1

Option 1: split, first we get the third element, then we split on the x:

df[['width', 'height']] = df['keyboardInfo'].str.split().str[2].str.split('x', expand=True)

Option 2: Use str.extractall to get the numbers, then use unstack:

df[['width', 'height']] = df['keyboardInfo'].str.extractall("(\d+)").unstack()

Output:


                                        keyboardInfo width height
0  [alphabet de_CH:swiss 1080x667 text actionNone...  1080    667
1  [alphabet de_CH:swiss 1080x667 text actionNone...  1080    667
2  [alphabetAutomaticShifted de_CH:swiss 1080x667...  1080    667
3  [alphabet de_CH:swiss 720x440 text actionNone ...   720    440

answered Sep 27, 2020 at 23:46

Erfan

43.3k10 gold badges75 silver badges86 bronze badges

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

Collectives™ on Stack Overflow

Adding columns to DataFrame extracted from string expression

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related