Pandas - duplicate rows based on values

Question

I have a dataframe, where I want to duplicate rows N times, where N is a value in another row.

For example, if this is my dataframe:

   Company  WEEK_DAYS   SEPTEMBER 15    SEPTEMBER 22   SEPTEMBER 29    value      
0  google    MON-FRI         0                0              5          0.5
1  google       TUE          3                2              0          0.7

so, the columns I want to replicate based on their value are SEPTEMBER 15, SEPTEMBER 22, and SEPTEMBER 29, and the value should be value.

So the final output should be something like that:

     Company  WEEK_DAYS     WEEK       value
0    google    MON-FRI    SEPTEMBER 29  0.5
1    google    MON-FRI    SEPTEMBER 29  0.5
2    google    MON-FRI    SEPTEMBER 29  0.5
3    google    MON-FRI    SEPTEMBER 29  0.5
4    google    MON-FRI    SEPTEMBER 29  0.5   
5    google      TUE      SEPTEMBER 15  0.7 
6    google      TUE      SEPTEMBER 15  0.7 
7    google      TUE      SEPTEMBER 15  0.7
8    google      TUE      SEPTEMBER 22  0.7 
9    google      TUE      SEPTEMBER 22  0.7

I tried using stack and pivot - but I didn't manage to get the desired output.

Any help will be appreciated!

jezrael · Accepted Answer · 2018-01-18 09:46:40Z

You can use:

first reshape by set_index and unstack for Series with Multiindex
repeat index for repeating all rows by loc
reset_index for convert MultiIndex to columns
remove column 0 by drop
rename column and change ordering by reindex

s = df.set_index(['Company','WEEK_DAYS','value']).stack()
df = (s.loc[s.index.repeat(s)]
       .reset_index()
       .drop(0, axis=1)
       .rename(columns={'level_3':'WEEK'})
       .reindex(columns=['Company','WEEK_DAYS','WEEK','value'])
      )
print (df)
  Company WEEK_DAYS          WEEK  value
0  google   MON-FRI  SEPTEMBER 29    0.5
1  google   MON-FRI  SEPTEMBER 29    0.5
2  google   MON-FRI  SEPTEMBER 29    0.5
3  google   MON-FRI  SEPTEMBER 29    0.5
4  google   MON-FRI  SEPTEMBER 29    0.5
5  google       TUE  SEPTEMBER 15    0.7
6  google       TUE  SEPTEMBER 15    0.7
7  google       TUE  SEPTEMBER 15    0.7
8  google       TUE  SEPTEMBER 22    0.7
9  google       TUE  SEPTEMBER 22    0.7

CermakM · Accepted Answer · 2018-01-18 09:59:40Z

0

You can make use of pandas.DataFrame.from_records function.

I suggest you created a list of values you want to have in your new DataFrame, i.e

records = [('google', 'MON-FRI', 'SEPTEMBER 29', '0.5')]
# assumes only one record for MON-FRI .. you might need to do some handling here otherwise
records *= int(df['SEPTEMBER 29'][df[WEEK_DAYS] == 'MON-FRI'])
labels = ['Company', 'WEEK_DAYS', 'WEEK', 'value']

new_df = pd.DataFrame.from_records(records, columns=labels)

I guess it's not the most elegant solution ever, but should work.

answered Jan 18, 2018 at 9:59

CermakM

2,0722 gold badges19 silver badges15 bronze badges

Collectives™ on Stack Overflow

Pandas - duplicate rows based on values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related