CODEWITHSUNDEEP
pythonpandasdataframe
user7057659
user7057659

Reputation:

Python - Write new row based on comma-separated values in table column

I have a sample dataset:

column1 column2 column3     column4                       column5 
  a       b        c       paddy, jimmy, john     [242352, 2351235, 65436324]
  a       z        c       james, jill, jillian   [325134, 63464374568, 43574578654]
  s       t        y       patsy                      [463465573452]

I want to separate 'column4' and 'column5' separated by comas. So that each column 4 and 5 only has one value. The rest of the row would be repeated.

Example of the resulting dataframe:

column1 column2 column3  column4     column5 
  a       b        c     paddy      242352
  a       b        c     jimmy      2351235
  a       b        c     john      65436324
  .......

Any solutions are appreciated. I have had a look at previous similar questions on stack overflow but because I have float values the solutions given do not work for me.

Upvotes: 1

Views: 1553

Answers (1)

Abhi
Abhi

Reputation: 4233

Use pd.reindex and pd.index.repeat to repeat rows.

Then use str.extractall to extract only numbers from col5 and str.split, stack to expand col4 and col5

# Reindex and repeat cols on len of split and reset index
df1 = df.reindex(df.index.repeat(df['column4'].fillna("").str.split(',').apply(len)))
df1 = df1.drop(['column4','column5'],1)

# Splitting both cols
s = df['column4'].str.split(',', expand=True).stack().reset_index(level=1,drop=True)
s1 = df['column5'].str.extractall('(\d+)').reset_index(level=1,drop=True)[0]

# Now grouping the series and df using cumcount.
df1 = df1.set_index(df1.groupby(df1.index).cumcount(), append=True)
s = s.to_frame('column4').set_index(s.groupby(s.index).cumcount(), append=True)
s1 = s1.to_frame('column5').set_index(s1.groupby(s1.index).cumcount(), append=True)

# Joining the all of them together and reset index.
df1 = df1.join(s, how='outer').join(s1,how='outer').reset_index(level=[0,1],drop=True)

print (df1)

output:

column1 column2 column3  column4    column5
0   a     b       c       paddy     242352
1   a     b       c       jimmy     2351235
2   a     b       c       john      65436324
3   a     z       c       james     325134
4   a     z       c       jill      63464374568
5   a     z       c       jillian   43574578654
6   s     t       y       patsy     463465573452

Upvotes: 2

Related Questions