CODEWITHSUNDEEP
pythonpandasnumpy
jwlon81
jwlon81

Reputation: 359

How to repeat rows based on value of a column in Python

I have the following df that comprises of code/product & weeks columns. 

code.  Product  .   weeks
123 .  product1 .    1;2
123 .  product1 .    3
321 .  product2 .    4;5;6
321 .  product2 .    7

For those rows that have more than 1 week (eg 1;2 or 4;5;6), I want to repeat these rows. My Desirsed output is as follows:

code.  Product  .   weeks
123 .  product1 .    1
123 .  product1 .    2
123 .  product1 .    3
321 .  product2 .    4
321 .  product2 .    5
321 .  product2 .    6
321 .  product2 .    7

What is the best approach to take using pandas or numpy?

Upvotes: 1

Views: 1171

Answers (2)

jezrael
jezrael

Reputation: 863541

Use:

df = (df.set_index(['code','Product'])['weeks']
       .str.split(';', expand=True)
       .stack()
       .reset_index(level=2, drop=True)
       .reset_index(name='weeks'))
print (df)
   code   Product weeks
0   123  product1     1
1   123  product1     2
2   123  product1     3
3   321  product2     4
4   321  product2     5
5   321  product2     6
6   321  product2     7

Explanation:

  1. First set_index by all repeated columns
  2. Create DataFrame by split
  3. Reshape by stack
  4. Last data cleaning by reset_index

Another solution:

from itertools import chain

weeks = df['weeks'].str.split(';')
lens = weeks.str.len()
df = pd.DataFrame({
    'code' : df['code'].repeat(lens),
    'Product' : df['Product'].repeat(lens),
    'weeks' : list(chain.from_iterable(weeks.values.tolist())), 
})

print (df)
   code   Product weeks
0   123  product1     1
0   123  product1     2
1   123  product1     3
2   321  product2     4
2   321  product2     5
2   321  product2     6
3   321  product2     7

Explanation:

  1. Create lists by split
  2. Get lengths of lsits by len
  3. Last repeat columns and flatten weeks

Upvotes: 2

Kiruparan Balachandran
Kiruparan Balachandran

Reputation: 362

#assume test.xlsx is your data
test = pd.read_excel('test.xlsx')  
test_processed = pd.DataFrame(columns=test.columns)
for index, row in test.iterrows():
   weeks = row['weeks'].split(';')
   for week in weeks:
       test_processed = test_processed.append({'code':row['code'], 'Product':row['Product'],'weeks':week}, ignore_index=True)

Upvotes: 0

Related Questions