Duplicate rows of dataframe based on condition

Question

I have a pandas data frame that looks something like this:

hotel_id         date         length_of_stay     clicks
A               2019-01-01           3               7
B               2019-01-06           2               11
C               2019-01-03           1               4

and I want the result to be:

hotel_id         date                            clicks
A               2019-01-01                          7
A               2019-01-02                          7
A               2019-01-03                          7
B               2019-01-06                          11
B               2019-01-07                          11
C               2019-01-03                          4

So we see how many clicks we got for each night someone stayed at the hotel...

I cant think of an elegant way to do this.. can someone help?

stackoverflow.com/questions/46659378/….. this might help

iamklaus
– iamklaus

2019-02-20 16:22:03 +00:00
Commented Feb 20, 2019 at 16:22 — iamklaus
– iamklaus, Commented Feb 20, 2019 at 16:22

Anton vBR · Accepted Answer · 2019-02-20 16:46:30Z

4

Use numpy.repeat():

m= pd.DataFrame(np.repeat(df.values,df.length_of_stay,axis=0),columns=df.columns)
m['date']=m.groupby('hotel_id')['date'].transform(lambda x: pd.date_range(start=x.iloc[0], periods=len(x)))

or:

newdf = pd.DataFrame(np.repeat(df.values,df.length_of_stay,axis=0),columns=df.columns)
newdf['date'] = [i for day, n in zip(df.date,df.length_of_stay) 
                   for i in pd.date_range(start=day, periods=n)]

Full example:

import pandas as pd
import numpy as np

data = '''\
hotel_id         date         length_of_stay     clicks
A               2019-01-01           3               7
B               2019-01-06           2               11
C               2019-01-03           1               4'''

fileobj = pd.compat.StringIO(data)
df = pd.read_csv(fileobj, parse_dates=['date'], sep='\s+')

m= pd.DataFrame(np.repeat(df.values,df.length_of_stay,axis=0),columns=df.columns)
m['date']=m.groupby('hotel_id')['date'].transform(lambda x: pd.date_range(start=x.iloc[0], periods=len(x)))
print(m)

  hotel_id       date length_of_stay clicks
0        A 2019-01-01              3      7
1        A 2019-01-02              3      7
2        A 2019-01-03              3      7
3        B 2019-01-06              2     11
4        B 2019-01-07              2     11
5        C 2019-01-03              1      4

edited Feb 20, 2019 at 16:46

Anton vBR

19k6 gold badges47 silver badges47 bronze badges

answered Feb 20, 2019 at 16:17

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

5 Comments

Anton vBR Over a year ago

No worries. Just adding info to make the answer easier to understand. You did all the work +1.

anky Over a year ago

@AntonvBR absolutely ..!! agreed "D

Tim496 Over a year ago

Thank you both so much! Very elegant solution :)

Tim496 Over a year ago

Thanks @AntonvBR! I was thinking of something along the lines of your answer but couldn't quite put it altogether

Anton vBR Over a year ago

@Tim496 By my solution do you mean the zip or iterrows? The itterows is the "quick and dirty" way of solving things in my opinion.

Anton vBR · Accepted Answer · 2019-02-20 16:33:27Z

2

Here is a different solution making use of the "ugly" df.iterrows():

newdf = pd.concat(pd.DataFrame({
        'hotel_id': row['hotel_id'],
        'date': pd.date_range(start=row['date'], periods=row['length_of_stay']),
        'length_of_stay': row['length_of_stay'],
        'clicks': row['clicks']
    }) for ind, row in df.iterrows())

Full example:

import pandas as pd

data = '''\
hotel_id         date         length_of_stay     clicks
A               2019-01-01           3               7
B               2019-01-06           2               11
C               2019-01-03           1               4'''

fileobj = pd.compat.StringIO(data)
df = pd.read_csv(fileobj, parse_dates=['date'], sep='\s+')

newdf = pd.concat(pd.DataFrame({
    'hotel_id': row['hotel_id'],
    'date': pd.date_range(start=row['date'], periods=row['length_of_stay']),
    'length_of_stay': row['length_of_stay'],
    'clicks': row['clicks']
}) for ind, row in df.iterrows())

Returns:

   clicks       date hotel_id  length_of_stay
0       7 2019-01-01        A               3
1       7 2019-01-02        A               3
2       7 2019-01-03        A               3
0      11 2019-01-06        B               2
1      11 2019-01-07        B               2
0       4 2019-01-03        C               1

answered Feb 20, 2019 at 16:33

Anton vBR

19k6 gold badges47 silver badges47 bronze badges

2 Comments

anky Over a year ago

interesting. iterrows with elegence. :D

Anton vBR Over a year ago

@anky_91 I just thought of it and thought: why not post it. The thing is quite easy to follow at least :)

Collectives™ on Stack Overflow

Duplicate rows of dataframe based on condition

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related