Remove values after a '','''

user_4502950 0
Viewed 55

I have a dataset (with multiple columns but for the sake of sample data, I have added two relevant columns)

 Type         SKU      Sizes
 Variable      3        XS,XS
 Variation     65       XS
 Variation     7        S,M,L
 variable      89       S
 Variation     90       M
 Variation     92       L

I have a list of values stored in dup_arr. I want to remove all sizes after ',' with the SKU values stored in dup_arr. For Example, if dup_arr = 3, I want the end result to look like

 Type         SKU      Sizes
 Variable      3        XS
 Variation     65       XS
 Variation     7        S,M,L
 variable      89       S
 Variation     90       M
 Variation     92       L

Note that the first row doesn't have XS after ','

I am trying to loop through each value and check for values

for i in OF['SKU']:
    for j in dup_arr:
       if i == j:
          OF.loc[OF['Attribute 1 value(s)']  = OF['Attribute 1 value(s)'].str.split(',').str[0]

however, it is removing all the sizes after ',' for all the values of SKU. I can understand why is happening but not sure how to remove all the sizes after ',' for that particular SKU value stored in dup_arr.

python
2 Answers

First off, you almost never have to loop through a dataframe like that. If you find yourself writing a loop, find a different way.

In this case, I would use pandas.Series.where to accomplish this:

from io import StringIO
import pandas
table = StringIO("""\
 Type         SKU      Sizes
 Variable      3        XS,XS
 Variation     65       XS
 Variation     7        S,M,L
 variable      89       S
 Variation     90       M
 Variation     92       L
""")
dup_arr = [3]
df = (
    pandas.read_table(table, sep='\s\s+')
        .assign(FirstSize=lambda df: df['Sizes'].where(
            ~df['SKU'].isin(dup_arr),          # condition to use original values
            df['Sizes'].str.split(',').str[0]  # all other values
        ))
)

df

And that gives me:

        Type  SKU  Sizes FirstSize
0   Variable    3  XS,XS        XS
1  Variation   65     XS        XS
2  Variation    7  S,M,L     S,M,L
3   variable   89      S         S
4  Variation   90      M         M
5  Variation   92      L         L
user_15527480

You can give a condition and remove the value.

If you already have a dataframe and want to remove the value, then use this.

df['Sizes'] = df.apply(lambda x: x['Sizes'].split(',')[0] if x['SKU'] == sku_val else x['Sizes'], axis=1)

or you can give it like this (using . instead of ['colname']):

df['Sizes'] = df.apply(lambda x: x.Sizes.split(',')[0] if x.SKU == sku_val else x.Sizes, axis=1)

Output - Before:

        Type  SKU  Sizes
0   Variable    3  XS,XS
1  Variation   65     XS
2  Variation    7  S,M,L
3   variable   89      S
4  Variation   90      M
5  Variation   92      L

Output - After:

        Type  SKU  Sizes
0   Variable    3     XS
1  Variation   65     XS
2  Variation    7  S,M,L
3   variable   89      S
4  Variation   90      M
5  Variation   92      L
user_138739800
Related