Split row into multiple rows in pandas

Question

I have a DataFrame with a format like this (simplified)

a  b  43
a  c  22

I would like this to be split up in the following way.

a  b  20
a  b  20
a  b  1
a  b  1
a  b  1
a  c  20
a  c  1
a  c  1

Where I have as many rows as the number divides by 20, and then as many rows as the remainder. I have a solution that basically iterates over the rows and fills up a dictionary which can then be converted back to Dataframe but I was wondering if there is a better solution.

score 4 · Accepted Answer · answered Jul 03 '17 at 06:38

4

You can use floor divison with modulo first and then create new DataFrame by constructor with numpy.repeat.

Last need numpy.concatenate with list comprehension for C:

a,b = df.C // 20, df.C % 20
#print (a, b)

cols = ['A','B']
df = pd.DataFrame({x: np.repeat(df[x], a + b) for x in cols})
df['C'] = np.concatenate([[20] * x + [1] * y for x,y in zip(a,b)])
print (df)
   A  B   C
0  a  b  20
0  a  b  20
0  a  b   1
0  a  b   1
0  a  b   1
1  a  c  20
1  a  c   1
1  a  c   1

answered Jul 03 '17 at 06:38

jezrael

822,522
95
1,334
1,252

I get a `ValueError: operands could not be broadcast together with shape (2,) (3810,)` on trying this at the np.repeat line. – Linda Jul 03 '17 at 07:46
Problem is with your sample data? Or with real data? In your real data solution only data are changed? – jezrael Jul 03 '17 at 07:50
The problem is with the sample data. When I try your solution I get the above error. – Linda Jul 03 '17 at 07:52
Do you use `df = pd.DataFrame(dict(A=['a', 'a'], B=['b', 'c'], C=[43, 22]))` ? – jezrael Jul 03 '17 at 07:53

piRSquared · Answer 2 · 2017-07-03T07:15:55.220

Setup

Consider the dataframe df

df = pd.DataFrame(dict(A=['a', 'a'], B=['b', 'c'], C=[43, 22]))
df

   A  B   C
0  a  b  43
1  a  c  22

np.divmod and np.repeat

m = np.array([20, 1])
dm = list(zip(*np.divmod(df.C.values, m[0])))
# [(2, 3), (1, 2)]

rep = [sum(x) for x in dm]
new = np.concatenate([m.repeat(x) for x in dm])

df.loc[df.index.repeat(rep)].assign(C=new)

   A  B   C
0  a  b  20
0  a  b  20
0  a  b   1
0  a  b   1
0  a  b   1
1  a  c  20
1  a  c   1
1  a  c   1

Split row into multiple rows in pandas

2 Answers2

Linked