CODEWITHSUNDEEP
pythonpandas
MRA
MRA

Reputation: 323

Duplicate row based on value in different column

I have a dataframe of transactions. Each row represents a transaction of two item (think of it like a transaction of 2 event tickets or something). I want to duplicate each row based on the quantity sold.

Here's example code:

# dictionary of transactions

d = {
    '1': ['20',  'NYC', '2'],
    '2': ['30',  'NYC', '2'],
    '3': ['5',   'NYC', '2'],
    '4': ['300', 'LA',  '2'],
    '5': ['30',  'LA',  '2'],
    '6': ['100', 'LA',  '2']
}

columns=['Price', 'City', 'Quantity']

# create dataframe and rename columns

df = pd.DataFrame.from_dict(
    data=d, orient='index'
)
df.columns = columns

This produces a dataframe that looks like this

Price   City    Quantity
20       NYC         2
30       NYC         2
5        NYC         2
300      LA          2
30       LA          2
100      LA          2

So in the case above, each row will transform into two duplicate rows. If the 'quantity' column was 3, then that row would transform into three duplicate rows.

Upvotes: 29

Views: 27814

Answers (3)

BENY
BENY

Reputation: 323396

Answer by using repeat 

df.loc[df.index.repeat(df.Quantity)]
Out[448]: 
  Price City Quantity
1    20  NYC        2
1    20  NYC        2
2    30  NYC        2
2    30  NYC        2
3     5  NYC        2
3     5  NYC        2
4   300   LA        2
4   300   LA        2
5    30   LA        2
5    30   LA        2
6   100   LA        2
6   100   LA        2

Upvotes: 54

Dickster
Dickster

Reputation: 3009

How about this approach. I changed your data slightly to call out a sale of 4 tickets.

We use a helper np.ones() array, suitably sized ,and then the key line of code is: a[np.arange(a.shape[1])[:] > a[:,0,np.newaxis]] = 0

I was shown this technique here: numpy - update values using slicing given an array value

Then its simply a call to .stack() and some basic filtering to complete.

d = {'1': ['20', 'NYC', '2'], '2': ['30', 'NYC', '2'], '3': ['5', 'NYC', '2'], \
     '4': ['300', 'LA', '2'], '5': ['30', 'LA', '4'],  '6': ['100', 'LA', '2']}

columns=['Price', 'City', 'Quantity']
df = pd.DataFrame.from_dict(data=d, orient='index')
df.columns = columns
df['Quantity'] = df['Quantity'].astype(int)

# make a ones array 
my_ones = np.ones(shape=(len(df),df['Quantity'].max()))

# turn my_ones into a dataframe same index as df so we can join it to the right hand side. Plenty of other ways to achieve the same outcome. 
df_my_ones = pd.DataFrame(data =my_ones,index = df.index)

df = df.join(df_my_ones)

which looks like:

  Price City  Quantity  0  1  2  3
1    20  NYC         2  1  1  1  1
3     5  NYC         2  1  1  1  1
2    30  NYC         2  1  1  1  1
5    30   LA         4  1  1  1  1
4   300   LA         2  1  1  1  1

now get the Quantity column and the ones into a numpy array

a = df.iloc[:,2:].values

this is the clever bit

a[np.arange(a.shape[1])[:] > a[:,0,np.newaxis]] = 0

and re-assign back to df.

df.iloc[:,2:] = a

and now df looks like following, notice how we have set to zero past the number in Quantity:

  Price City  Quantity  0  1  2  3
1    20  NYC         2  1  1  0  0
3     5  NYC         2  1  1  0  0
2    30  NYC         2  1  1  0  0
5    30   LA         4  1  1  1  1
4   300   LA         2  1  1  0  0

df.set_index(['Price','City','Quantity'],inplace=True)
df =  df.stack().to_frame()
df.columns = ['sale_flag']
df.reset_index(inplace=True)
print df[['Price','City', 'Quantity']][df['sale_flag'] !=0]
print df

which produces:

Price City  Quantity
0     20  NYC         2
1     20  NYC         2
4      5  NYC         2
5      5  NYC         2
8     30  NYC         2
9     30  NYC         2
12    30   LA         4
13    30   LA         4
14    30   LA         4
15    30   LA         4
16   300   LA         2
17   300   LA         2

Upvotes: 4

Alexander
Alexander

Reputation: 109736

First, I recreated your data using integers instead of text. I also varied the quantity so that one can more easily understand the problem.

d = {1: [20, 'NYC', 1], 2: [30, 'NYC', 2], 3: [5, 'SF', 3],      
     4: [300, 'LA', 1], 5: [30, 'LA', 2],  6: [100, 'SF', 3]}

columns=['Price', 'City', 'Quantity'] 
# create dataframe and rename columns

df = pd.DataFrame.from_dict(data=d, orient='index').sort_index()
df.columns = columns

>>> df
   Price City  Quantity
1     20  NYC         1
2     30  NYC         2
3      5   SF         3
4    300   LA         1
5     30   LA         2
6    100   SF         3

I created a new DataFrame by using a nested list comprehension structure. 

df_new = pd.DataFrame([df.ix[idx] 
                       for idx in df.index 
                       for _ in range(df.ix[idx]['Quantity'])]).reset_index(drop=True)
>>> df_new
    Price City  Quantity
0      20  NYC         1
1      30  NYC         2
2      30  NYC         2
3       5   SF         3
4       5   SF         3
5       5   SF         3
6     300   LA         1
7      30   LA         2
8      30   LA         2
9     100   SF         3
10    100   SF         3
11    100   SF         3

Upvotes: 13

Related Questions