Pandas dataframe: how do I split one row into multiple rows by multi-value column? [duplicate]

Question

I have a dataframe that appears as follows:

   issue_key date     pkey          component              case_count
0  1060  2018-03-08  PROJ  console,configuration,management    8   
1  1464  2018-04-24  PROJ2 protocol                            1   
2  611   2017-03-31  PROJ  None                                2
3  2057  2018-10-30  PROJ  ha, console                         0

I need to split the rows with multiple values in the component column into one row per component.

When done, the dataframe should appear as follows:

   issue_key date     pkey          component              case_count
0  1060  2018-03-08  PROJ  console                           8
1  1060  2018-03-08  PROJ  configuration                     8
2  1060  2018-03-08  PROJ  management                        8   
3  1464  2018-04-24  PROJ2 protocol                          1   
4  611   2017-03-31  PROJ  None                              2
5  2057  2018-10-30  PROJ  ha                                0
6  2057  2018-10-30  PROJ  console                           0

Any suggestions on how best to do this?

kindly edit the table format. Hard to comprehend now.

YOLO
– YOLO

2018-12-19 23:12:35 +00:00
Commented Dec 19, 2018 at 23:12 — YOLO
– YOLO, Commented Dec 19, 2018 at 23:12
saw the same, just finished edits.

Eric
– Eric

2018-12-19 23:18:26 +00:00
Commented Dec 19, 2018 at 23:18 — Eric
– Eric, Commented Dec 19, 2018 at 23:18

YOLO · Accepted Answer · 2018-12-19 23:23:57Z

21

Let's say dd is your data frame. You can do:

# convert to list
dd['component'] = dd['component'].str.split(',')

# convert list of pd.Series then stack it
dd = (dd
 .set_index(['issue_key','date','pkey','case_count'])['component']
 .apply(pd.Series)
 .stack()
 .reset_index()
 .drop('level_4', axis=1)
 .rename(columns={0:'component'}))

       issue_key        date   pkey  case_count      component
0       1060  2018-03-08   PROJ           8        console
1       1060  2018-03-08   PROJ           8  configuration
2       1060  2018-03-08   PROJ           8     management
3       1464  2018-04-24  PROJ2           1       protocol
4        611  2017-03-31   PROJ           2           None
5       2057  2018-10-30   PROJ           0             ha
6       2057  2018-10-30   PROJ           0        console

answered Dec 19, 2018 at 23:23

YOLO

22k5 gold badges25 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pats4u Over a year ago

How do we achieve this incase there is another column which is a variable of component, say color of the component. Ex: console,configuration has blue,white in another column color.

Collectives™ on Stack Overflow

Pandas dataframe: how do I split one row into multiple rows by multi-value column? [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related