Replace and duplicate string with a specific max count in pandas

user_5942100 0
Viewed 100

I have a dataset, df, that repeats a sequence for X amount of times. I would like to replace certain letters of this sequence and then repeat for a given max count.

Data

xy_pod  xy_pod  xy_pod  xy_pod
xy_pod  xy_pod  xy_pod  xy_pod
xy_pod  xy_pod  xy_pod  xy_pod

other letters where I would like to replace the 'xy' portion with:

   aa
   vee
   lee

Desired

xy_pod  xy_pod  xy_pod  xy_pod
xy_pod  xy_pod  xy_pod  xy_pod
xy_pod  xy_pod  xy_pod  xy_pod



aa_pod  aa_pod  aa_pod  aa_pod
aa_pod  aa_pod  aa_pod  aa_pod
aa_pod  aa_pod  aa_pod  aa_pod
    

vee_pod vee_pod vee_pod vee_pod
vee_pod vee_pod vee_pod vee_pod
vee_pod vee_pod vee_pod vee_pod


lee_pod lee_pod lee_pod lee_pod
lee_pod lee_pod lee_pod lee_pod
lee_pod lee_pod lee_pod lee_pod

Doing

df.replace(xy_pod, aa_pod, 12)
df.replace(aa_pod, vee_pod, 12)   
df.replace(vee_pod, lee_pod, 12)

This is very similar to the find and replace logic that excel offers. However, I am not sure how to specify the number of repetitions that I wish to occur. Also, how would I perform this for multiple sequences so that I do not have to perform the function for every new entry? Is there a more efficient way to do this?

Any suggestion or advice is appreciated

pandas numpy python
4 Answers

Try this:

pd.concat([df]+[df.stack().str.replace('xy', i).unstack() for i in ['aa','vee', 'lll']])

Output:

         0        1        2        3
0   xy_pod   xy_pod   xy_pod   xy_pod
1   xy_pod   xy_pod   xy_pod   xy_pod
2   xy_pod   xy_pod   xy_pod   xy_pod
0   aa_pod   aa_pod   aa_pod   aa_pod
1   aa_pod   aa_pod   aa_pod   aa_pod
2   aa_pod   aa_pod   aa_pod   aa_pod
0  vee_pod  vee_pod  vee_pod  vee_pod
1  vee_pod  vee_pod  vee_pod  vee_pod
2  vee_pod  vee_pod  vee_pod  vee_pod
0  lll_pod  lll_pod  lll_pod  lll_pod
1  lll_pod  lll_pod  lll_pod  lll_pod
2  lll_pod  lll_pod  lll_pod  lll_pod
user_63615310 
import pandas as pd
import numpy as np

One way of doing this:

Firstly make a Series Of Values:

ser=pd.Series(['xy','aa','vee','lee'])

OR

You can also make a list of values as it doesn't matter if it is Series or a list:

ser=['xy','aa','vee','lee']

Then If you want duplicates of each : _pod 12 times then use:

df=pd.DataFrame(np.repeat(df.values,4,axis=0),columns=df.columns)

Finally use concat() method:

result=pd.concat((df.replace('xy',x,regex=True) for x in ser),ignore_index=True)

Now if you print result you will get your desired output:

    col1        col2        col3        col4
0   xy_pod      xy_pod      xy_pod      xy_pod
1   xy_pod      xy_pod      xy_pod      xy_pod
2   xy_pod      xy_pod      xy_pod      xy_pod
3   xy_pod      xy_pod      xy_pod      xy_pod
4   xy_pod      xy_pod      xy_pod      xy_pod
5   xy_pod      xy_pod      xy_pod      xy_pod
..  ......      .......     ......      ......
..  ......      .......     ......      ......
42  lee_pod     lee_pod     lee_pod     lee_pod
43  lee_pod     lee_pod     lee_pod     lee_pod
44  lee_pod     lee_pod     lee_pod     lee_pod
45  lee_pod     lee_pod     lee_pod     lee_pod
46  lee_pod     lee_pod     lee_pod     lee_pod
47  lee_pod     lee_pod     lee_pod     lee_pod
user_142898920

Looking for the 12 time count need stack the find the count

s = df.stack()
find_count = s.groupby(s.shift().ne(s).cumsum()).transform('count')
n = 12
out = s[find_count==n].replace({'xy':'aa'},regex=True).combine_first(s).unstack()
out
Out[227]: 
        0       1       2       3
0  aa_pod  aa_pod  aa_pod  aa_pod
1  aa_pod  aa_pod  aa_pod  aa_pod
2  aa_pod  aa_pod  aa_pod  aa_pod
user_79645270

A numpy solution:

import numpy as np
import pandas as pd

df = pd.DataFrame({0: {0: 'xy_pod', 1: 'xy_pod', 2: 'xy_pod'},
                   1: {0: 'xy_pod', 1: 'xy_pod', 2: 'xy_pod'},
                   2: {0: 'xy_pod', 1: 'xy_pod', 2: 'xy_pod'},
                   3: {0: 'xy_pod', 1: 'xy_pod', 2: 'xy_pod'}})
# Get xy and pod from DF
key, tail = df.iloc[0, 0].split('_')
# Create array in same shape with _pod 
base = np.full(df.shape, f'_{tail}', dtype='object')
# Create Array With prefixes for broadcasting
s = np.array([key, 'aa', 'vee', 'lll'])[:, None, None]
# s + base and concatenate
new_df = pd.DataFrame(np.concatenate(s + base))

print(new_df)

new_df

          0        1        2        3
0    xy_pod   xy_pod   xy_pod   xy_pod
1    xy_pod   xy_pod   xy_pod   xy_pod
2    xy_pod   xy_pod   xy_pod   xy_pod
3    aa_pod   aa_pod   aa_pod   aa_pod
4    aa_pod   aa_pod   aa_pod   aa_pod
5    aa_pod   aa_pod   aa_pod   aa_pod
6   vee_pod  vee_pod  vee_pod  vee_pod
7   vee_pod  vee_pod  vee_pod  vee_pod
8   vee_pod  vee_pod  vee_pod  vee_pod
9   lll_pod  lll_pod  lll_pod  lll_pod
10  lll_pod  lll_pod  lll_pod  lll_pod
11  lll_pod  lll_pod  lll_pod  lll_pod

Some timing information via perfplot:

perfplot of runtimes

import numpy as np
import pandas as pd
import perfplot


def gen_data(n):
    return pd.DataFrame(np.full((n, max(3, n // 25)), 'xy_pod'))


def scott_boston(df):
    return pd.concat([df] + [df.stack().str.replace('xy', i).unstack() for i in
                             ['aa', 'vee', 'lll']])


def beny(df):
    s = df.stack()
    find_count = s.groupby(s.shift().ne(s).cumsum()).transform('count')
    n = 12
    return s[find_count == n].replace({'xy': 'aa'}, regex=True).combine_first(
        s).unstack()


def anurag_dabas(df):
    ser = pd.Series(['xy', 'aa', 'vee', 'lee'])
    df = pd.DataFrame(np.repeat(df.values, 4, axis=0), columns=df.columns)
    return pd.concat((df.replace('xy', x, regex=True) for x in ser),
                     ignore_index=True)


def henry_ecker(df):
    key, tail = df.iloc[0, 0].split('_')
    base = np.full(df.shape, f'_{tail}', dtype='object')
    s = np.array([key, 'aa', 'vee', 'lll'])[:, None, None]
    return pd.DataFrame(np.concatenate(s + base))


if __name__ == '__main__':
    out = perfplot.bench(
        setup=gen_data,
        kernels=[
            scott_boston,
            beny,
            anurag_dabas,
            henry_ecker
        ],
        labels=['Scott Boston', 'BENY', 'Anurag Dabas', 'Henry Ecker'],
        n_range=[2 ** k for k in range(15)],
        equality_check=None
    )
    out.save('perfplot_results.png', transparent=False)
user_154978880
Related