How can I separate text into multiple values in a CSV file using Python?

Admin 1
Viewed 199

I'd like to begin processing some data for analysis but I have to separate the responses into multiple values. Currently each column contains one value that is combined with 3 responses, Agree: #score, Disagree: #score, Neither agree nor disagree. I'd like to separate the responses from the column into individual values to create an analysis for a visualization. Would I need to include regular expression to do this?

sample dataset

So far that code I have is just to load the data with some libraries I plan to use:

import numpy as np
import matplotlib.pyplot as plt
import pandas as pd

def load_data():

    # importing datasets
    df=pd.read_csv('dataset.csv')
    
    return df
load_data().head()
pandas dataframe csv python
1 Answers

You need to use str.split(';') to first split the values into multiple columns. Then for each column value, split the string again using str.split(':') but take [-1] part of it.

Here's how you can do it.

import pandas as pd
df = pd.DataFrame({'username':['Dragonfly','SpeedHawk','EagleEye'],
                   'Question1':['Comfortable:64;Neither comfortable nor uncomfortable:36',
                                'Comfortable:0;Neither comfortable nor uncomfortable:100',
                                'Comfortable:10;Neither comfortable nor uncomfortable:90'],
                   'Question2':['Agree:46;Disagree:13;Neither agree nor disagree:41',
                               'Agree:96;Disagree:0;Neither agree nor disagree:4',
                               'Agree:90;Disagree:5;Neither agree nor disagree:5']})

df[['Q1_Comfortable','Q1_Neutral']] = df['Question1'].str.split(';',expand=True)
df[['Q2_Agree','Q2_Disagree','Q2_Neutral']] = df['Question2'].str.split(';',expand=True)

df.drop(columns=['Question1','Question2'],inplace=True)
for col in df.columns[1:]:
    df[col] = df[col].str.split(':').str[-1]

print (df)

The output of this will be:

    username Q1_Comfortable Q1_Neutral Q2_Agree Q2_Disagree Q2_Neutral
0  Dragonfly             64         36       46          13         41
1  SpeedHawk              0        100       96           0          4
2   EagleEye             10         90       90           5          5
user_138739800
Related