Splitting values in the column and creating new cols small problem

user_14181044 0
Viewed 113

I have an survey data in which one column is as follows:

Evaluations_Col

E: 3, D: 3, C: 3, S: 3, E: 3, X, K: 3   
E: 1, D: 1, C: 1, S: 1, E: 1, X, K: 1
E: 2, D: 2, C: 2, S: 2, E: 2, X, K: 2 
E: 5, D: 5, C: 5, S: 5, E: 5, X, K: 5
E: 3, D: 1, C: 1, S: 1, E: 1, X, K: 1

NOTE: I need to ignore X values in the columns.

I want to extract each evaluation and separate them as columns separately for each type of evaluation. and at the end expected columns will be like:

E_col    D_col     C_Col   ...

3          3         3
1          1         1
2          2         2
5          5         5
3          1         1

I can maybe split them by comma and get a list like this, [E: 3, D: 3, C: 3, S: 3, E: 3, K: 3] What how to create seperate column for each and spread the corresponding values correctly?

I can achive normally by this but X values cause problem bc dictionary... How can I exclude it?

df1 = pd.DataFrame([dict([y.split(':') for y in x.split(',')]) for x in test_col])
df1.head()

error is

ValueError: dictionary update sequence element #9 has length 1; 2 is required
pandas python
3 Answers

Using list comprehension and filtering lines that are with ':' separator only:

Let's break the list comprehension to parts:

  1. Looping on lines : for x in test_col
  2. Seperating only the lines (denoted by x) to colums by splitting by ',' : for y in x.split(',')
  3. Splitting column to key-value pair only if ':' seperator exists : y.split(':') for y in x.split(',') ***only*** if ':' in y (that solves the problem described)

Code:

import pandas as pd
import numpy as np

test_col = []
with open('data.csv', 'r') as f:
    test_col = [l.strip() for l in f.readlines()]

df = pd.DataFrame([dict([y.split(':') for y in x.split(',') if ':' in y]) for x in test_col])
print(df.head())

Output:

    E   D   C   S   E   K
0   3   3   3   3   3   3
1   1   1   1   1   1   1
2   2   2   2   2   2   2
3   5   5   5   5   5   5
4   3   1   1   1   1   1
user_141488640

One way is to use str.extractall:

s = df["Value"].str.extractall(r"([A-Z]):\s(\d)").reset_index().groupby("level_0")

print (pd.DataFrame(s[1].agg(list).tolist(), columns=s[0].get_group(0).tolist()))

   E  D  C  S  E  K
0  3  3  3  3  3  3
1  1  1  1  1  1  1
2  2  2  2  2  2  2
3  5  5  5  5  5  5
4  3  1  1  1  1  1
user_92844230

using str.split and stack

df1 = (
    df["Evaluations_Col"]
    .str.split(",", expand=True)
    .stack()
    .str.split(":", expand=True)
    .set_index(0, append=True)
    .dropna()
    .unstack([1, 2])
    .droplevel(1,1)
)


    1                    
0   E   D   C   S   E   K
0   3   3   3   3   3   3
1   1   1   1   1   1   1
2   2   2   2   2   2   2
3   5   5   5   5   5   5
4   3   1   1   1   1   1
user_93751020
Related