Extracting multiple values in different rows

Question

I have a dataset

ID   col1    col2               year
1     A     111,222,3334       2010
2     B     344, 111          2010
3     C      121,123          2011

I wanna rearrange the dataset in the following way

ID   col1   col2            year
1     A     111            2010
1     A     222            2010
1     A     3334            2010
2     B     344            2010
2     B     111            2010
3     C     121            2011
3     C     123            2011

I can do it using the following code.

a = df.COMP_MONITOR_TYPE_CODE.str[:3]
df['col2'] = np.where(a == 111, 111)

Since, I have a very long data, its would be time consuming to do it one by one. Is there any other way to do it

Can you provide the code to create your initial dataset, so we have a test example to work with more easily. — Kraigolas, Feb 28 '21 at 23:13
Here you go @Kraigolas, I don't know why OP didn't give it... `df = pd.DataFrame({'ID':range(3), 'col1':list('ABC'), 'col2':['111,222,3334','344,111','121,123'],'year':[2010,2010,2011]})` — user1717828, Feb 28 '21 at 23:15
Does this answer your question? [Split (explode) pandas dataframe string entry to separate rows](https://stackoverflow.com/questions/12680754/split-explode-pandas-dataframe-string-entry-to-separate-rows) — wwnde, Feb 28 '21 at 23:15

score 5 · Accepted Answer · answered Feb 28 '21 at 23:10

split + explode:

df.assign(col2 = df.col2.str.split(',')).explode('col2')

#   ID col1  col2  year
#0   1    A   111  2010
#0   1    A   222  2010
#0   1    A  3334  2010
#1   2    B   344  2010
#1   2    B   111  2010
#2   3    C   121  2011
#2   3    C   123  2011

Extracting multiple values in different rows

1 Answers1