How to remove fractioned Items and add sales to another row

Question

So basically my POS reports don't add up split bills.

If you look at df.Item there are items with fractions (1/2, 1/3, etc). I want to drop those lines but add the sales to the proper row.

                 Item  Outlet1  Outlet2  Outlet3  Outlet4
2             AIR GIN    162.0      NaN    189.0     54.0
3         AIR GIN 1/3      NaN      NaN      NaN      9.0
4           AIR VODKA    468.0      NaN    585.0    144.0
5       AIR VODKA 1/2      NaN      NaN     18.0      NaN

Example output:

                 Item  Outlet1  Outlet2  Outlet3  Outlet4
2             AIR GIN    162.0      NaN    189.0     63.0
3           AIR VODKA    468.0      NaN    603.0    144.0

I'm not sure where to start, New to python.

[Please don't post pictures of text](https://meta.stackoverflow.com/q/285551/4518341). Instead, copy the text itself and [edit] it in. It also wouldn't hurt to remove the columns that aren't relevant for the sake of example, like the categories. Check out [How to make good reproducible pandas examples](/q/20109391/4518341). — wjandrea, Dec 04 '22 at 00:35
It would probably be easier for you to pre-process your input data before you shove it into pandas. — Tim Roberts, Dec 04 '22 at 01:04

wjandrea · Accepted Answer · 2022-12-04T02:21:09.813

I'm assuming the data doesn't contain any duplicate items. It looks like it's total sales over a certain period, but just the itemization is messed up.

In that case, you can simply remove the fractions with .str.replace(), then group and sum.

df['Item'] = df['Item'].str.replace(r'\s+\d+/\d+$', '', regex=True)

df.groupby('Item').sum(min_count=1)  # `min_count` to respect NaNs

           Outlet1  Outlet2  Outlet3  Outlet4
Item                                         
AIR GIN      162.0      NaN    189.0     63.0
AIR VODKA    468.0      NaN    603.0    144.0

Then you can .reset_index() if you want.

P.S. Before your edit, you also had two other columns that weren't really relevant to the problem, but change the process. You could just include them in the groupby:

df.groupby(['Category', 'SubCategory', 'Item'])

How to remove fractioned Items and add sales to another row

1 Answers1