Groupby in Reverse

Question

I have a pandas dataframe with name of variables, the values for each and the count (which shows the frequency of that row):

df = pd.DataFrame({'var':['A', 'B', 'C'], 'value':[10, 20, 30], 'count':[1,2,3]})

var  value  count
A    10     1
B    20     2
C    30     3

I want to use count to get an output like this:

var  value
A    10
B    20
B    20
C    30
C    30
C    30

What is the best way to do that?

https://stackoverflow.com/questions/47336704/repeat-rows-in-a-pandas-dataframe-based-on-column-value — Shijith, Sep 26 '20 at 14:56
How the content of this question refers to the title "groupby in reverse"? — Binyamin Even, Oct 20 '20 at 12:21

Shubham Sharma · Accepted Answer · 2020-09-26T15:13:51.660

7

You can use index.repeat:

i = df.index.repeat(df['count'])
d = df.loc[i, :'value'].reset_index(drop=True)

   var  value
0   A     10
1   B     20
2   B     20
3   C     30
4   C     30
5   C     30

edited Sep 26 '20 at 15:13

answered Sep 26 '20 at 14:51

Shubham Sharma

68,127
6
24
53

Scott Boston · Answer 2 · 2020-09-26T15:06:58.400

3

Use repeat with reindex for this short one-liner:

df.reindex(df.index.repeat(df['count']))

Output:

  var  value  count
0   A     10      1
1   B     20      2
1   B     20      2
2   C     30      3
2   C     30      3
2   C     30      3

Or to eliminate the 'count' column:

df[['var','value']].reindex(df.index.repeat(df['count']))

OR

df.reindex(df.index.repeat(df['count'])).drop('count', axis=1)

Output:

  var  value
0   A     10
1   B     20
1   B     20
2   C     30
2   C     30
2   C     30

edited Sep 26 '20 at 15:06

answered Sep 26 '20 at 15:00

Scott Boston

147,308
15
139
187

1

This is actually the best answer so far. It's the only one that doesn't need to know the labels of the other columns. – Peaceful James Sep 26 '20 at 15:04
1

`df.reindex(df.index.repeat(df['count'])).iloc[:,:-1].reset_index(drop=True)` – Shijith Sep 26 '20 at 15:05

score 2 · Answer 3 · answered Sep 26 '20 at 14:57

Using Series.repeat

import pandas as pd
df = pd.DataFrame({'var':['A', 'B', 'C'], 'value':[10, 20, 30], 'count':[1,2,3]})
new_df = pd.DataFrame()

new_df['var'] = df['var'].repeat(df['count'])
new_df['value'] = df['value'].repeat(df['count'])
new_df

    var value
0   A   10
1   B   20
1   B   20
2   C   30
2   C   30
2   C   30

score 1 · Answer 4 · answered Sep 26 '20 at 14:54

1

There are many, many ways to achieve this. Here is one cheeky approach that I like doing:

df.transform({
    "count": lambda x: [i for i in range(x)],
    "var": lambda x: x,
    "value": lambda x: x
}).explode("count").drop("count", axis=1)

answered Sep 26 '20 at 14:54

Peaceful James

1,807
1
7
16

Groupby in Reverse

4 Answers4