How to separate Pandas column that contains values stored as text and numbers into two seperate columns

user_8150532 0
Viewed 2412

I have a Pandas column that contains results from a survey, which are either free text or numbers from 1-5. I am retrieving these from an API in JSON format and convert them into a DataFrame. Each row represents one question with the answer of a participant like this:

Memberid | Question | Answer
       1   Q1             3
       1   Q2             2
       1   Q3         Test Text
       2   Q1             3
       2   Q2             2
       2   Q3         Test Text

The column that has the results stores all of them as string for now, so when exporting them to excel the numbers are stored as text.

My goal is to have a separate column for the text answers and leave the field they were originally in empty, so that we have separate columns for the text results and the numeric results for calculation purposes.

Memberid | Question | Numeric Answers | Freetext answers
       1   Q1             3
       1   Q2             2
       1   Q3                             Test Text
       2   Q1             3
       2   Q2             2
       2   Q3                             Test Text

I am generating this df from lists like this: 

d = {'Memberid':memberid, 'Question':title, 'Answer':results}
df = pd.DataFrame(d)

So the first thing I tried was to convert the numeric values in the column from string to numbers via this:

df["Answer"] = pd.to_numeric(df['Answer'], errors='ignore')

Idea was that if it works I can simply do a for loop to check if a value in the answer column is string and then move that value into a new column.

The issue is, that the errors command does not work as intended for me. When I leave it on ignore, nothing gets converted. When I change it to coerce, the numbers get converted from str to numeric, but the fields where there freetext answers are now empty in Excel.

pandas python-3.x python
5 Answers

You can use Series.str.extract with a regex pattern:

  • (\d+)? will extract consecutive digits
  • (\D+) will extract consecutive non-digit characters
  • The ?P<text> syntax will name your match group - making this the column heading.
df.join(df.pop('Answer').str.extract('(?P<numbers>\d+)?(?P<text>\D+)?').fillna(''))

[out]

   Memberid Question numbers       text
0         1       Q1       3           
1         1       Q2       2           
2         1       Q3          Test Text
3         2       Q1       3           
4         2       Q2       2           
5         2       Q3          Test Text
user_102015800

You can build the Numeric Answers column with to_numeric(,errors='coerce'), then use isna on that column to build the FreeText Answers one:

df['Numeric Answers'] = pd.to_numeric(df['Answer'], errors='coerce')
mask = df['Numeric Answers'].isna()
df.loc[mask, 'FreeText Answers'] = df.loc[mask, 'Answer']
df.drop(columns=['Answer'])

It gives: 

   Memberid Question  Numeric Answers FreeText Answers
0         1       Q1              3.0              NaN
1         1       Q2              2.0              NaN
2         1       Q3              NaN        Test Text
3         2       Q1              3.0              NaN
4         2       Q2              2.0              NaN
5         2       Q3              NaN        Test Text

If you do not like the NaN you can replace them with empty strings:

df['FreeText Answers'].fillna('', inplace=True)
df['Numeric Answers'] = df['Numeric Answers'].astype(object).fillna('')

to finally obtain:

   Memberid Question Numeric Answers FreeText Answers
0         1       Q1               3                 
1         1       Q2               2                 
2         1       Q3                        Test Text
3         2       Q1               3                 
4         2       Q2               2                 
5         2       Q3                        Test Text
user_35452730

Something like this can be done to construct 2 lists (one for text data and one for numeric data):

text_data = ["" if s.isdigit() else s for s in df['Question']] # "" default string
numeric_data = [s if s.isdigit() else 0 for s in df['Question']] # 0 default numeric value
user_116694460

you could do something like : 

import pandas as pd
df = pd.DataFrame({"Question":['Q1', 'Q2','Q3'],'Answers':['Answer1', '1','2']})
idx = df.Answers.str.isnumeric()
df['Numeric Answers'] = None
df['Freetext answers'] = ''
df.loc[~idx, 'Freetext answers'] = df.loc[~idx, 'Answers']
df.loc[idx, 'Numeric Answers'] = df.loc[idx, 'Answers']
user_112051490

Hopefully this answers your question. I used the string digits method to separate the numbers from the text. You can then apply pd.numeric to convert the numbers column

    import pandas as pd
    import numpy as np
    import string   

     a={
        'Memberid':[1,1,1,2,2,2],
        'Question':['Q1','Q2','Q3','Q1','Q2','Q3'],
        'Answer':['3','2','Test Text','3','2','Test Text']
      }

    df = pd.DataFrame.from_dict(a)
    digits = list(string.digits)   
    df = df.assign(Numeric_Answers= np.where(df['Answer'].isin(digits),                          
                                             df['Answer'],
                                             np.nan
                                            ),

                   FreeText =       np.where(df['Answer'].isin(digits),
                                             np.nan,
                                             df['Answer']
                                           )
                  )

        Memberid    Question    Answer  Numeric_Answers     FreeText
    0       1        Q1           3          3                 NaN
    1       1        Q2           2          2                 NaN
    2       1        Q3        Test Text    NaN             Test Text
    3       2        Q1           3          3                 NaN
    4       2        Q2           2          2                 NaN
    5       2        Q3        Test Text    NaN             Test Text
user_71757130
Related