Split multiple columns with multiple delimiters; Newly-formed columns should have count of original values

Admin 1
Viewed 76

I'm new to python programming. I work on data where subsets of some attributes are obtained inside a single super-set (i.e. column/attribute) (with non-alphanumeric separators).

Representative data:

col1 col2 col3
1 x/x/z/z/z i\i\i\j
2 y/y/z/w/w/w k\k\i

Preferred output:

col1 w_col2 x_col2 y_col2 z_col2 i_col3 j_col3 k_col3
1 0 2 0 3 3 1 0
2 3 0 2 1 1 0 2

What I generally do:

  1. Find unique entries for such columns
  2. Split all such columns individually; Apply column prefixes (optionally); Store as new dataframe
  3. Apply value_counts() on axis 1
  4. Concat with original dataframe

Fortunately, the dataset is small with known columns to work on. However, I would like to know if there is a better (compact) way to approach this.

Edit:

  • Added new columns where values are to be treated (cast) as numbers (since they are counts)
col1 col2 col3 col4 col5
1 x/x/z/z/z i\i\i\j 0-2-4-2 1_2_5_3
2 y/y/z/w/w/w k\k\i 1-3-2-2 4_1_3_4

New preferred output:

col1 w_col2 x_col2 y_col2 z_col2 i_col3 j_col3 k_col3 0_col4 1_col4 2_col4 3_col4 0_col5 1_col5 2_col5 3_col5
1 0 2 0 3 3 1 0 0 2 4 2 1 2 5 3
2 3 0 2 1 1 0 2 1 3 2 2 4 1 3 4
pandas count python split
1 Answers

Here is a proposal using df.stack , then series.split folowed by series.explode and pd.crosstab

u = df[['col2','col3']].stack().str.split(r"\\|/").explode()
v = u.add("_"+ u.index.get_level_values(1))

out = pd.crosstab(v,v.index.get_level_values(0)).T.rename_axis(index=None,columns=None)

print(out)

   i_col3  j_col3  k_col3  w_col2  x_col2  y_col2  z_col2
0       3       1       0       0       2       0       3
1       1       0       2       3       0       2       1

EDIT:

As per edited question you can do similar steps for col4 and col5 then concat:

u = df[['col2','col3']].stack().str.split(r"\\|/").explode()
v = u.add("_"+ u.index.get_level_values(1))

a = df[['col4','col5']].stack().str.split("-|_",expand=True).unstack()
a.columns = a.columns.map("{0[0]}_{0[1]}".format)

out = (pd.concat((df[['col1']],
                  pd.crosstab(v,v.index.get_level_values(0))
                     .T.rename_axis(index=None,columns=None),
                  a),axis=1))
print(out)

   col1  i_col3  j_col3  k_col3  w_col2  x_col2  y_col2  z_col2 0_col4 0_col5  \
0     1       3       1       0       0       2       0       3      0      1   
1     2       1       0       2       3       0       2       1      1      4   

  1_col4 1_col5 2_col4 2_col5 3_col4 3_col5  
0      2      2      4      5      2      3  
1      3      1      2      3      2      4
user_98406370
Related