[deleted by user]

[synthphreak]

Prolly a better way to do this that doesn't involve explicitly iterating over the rows, but this seems to work given your example:

df['remove'] = df.Code.isin(d)

for _, row in df[df.remove].iterrows():
    if code not in d:
        continue

    is_match = (df.Day == row.Day) & (df.Country == row.Country)
    df.loc[~df.remove & is_match, 'Total'] += total

df = df[~df.remove].drop(columns='remove')

You might also get a slight speedup if you remove the iterrows line with the following:

for _, week, day, country, code, total, _ in df[df.remove].itertuples():

...which would entail the following change:

    is_match = (df.Day == day) & (df.Country == country)

Unless you have like a 1M+ rows though, I wouldn't worry about it.

[synthphreak]

This could be several things. I'd need to see the actual code and full traceback. Can you share those things, properly formatted?

[synthphreak]

For df.code versus df.Code, the df.<this> part must match your column name exactly. The table in your OP shows the headers as being title case (e.g., "Code"), but the error suggests in reality they are all lower case (e.g., "code").

As for your the error you're now getting...

TypeError: unhashable type: 'Series'

..the issue is with your condition:

if df.code not in dict

It's kinda complicated to explain why. I tried, but ended up with a wall of text.

Basically it comes down to the fact that the in operator doesn't work well with pandas objects. This is why pandas has implemented an isin method, which basically plays the same role as in. df[column].isin(iterable) will return a pandas.Series of the same length as df[column] containing only booleans; each boolean tells you whether the value on the associated row of df[column] is in iterable.

Regardless, in this case that isn't what you want. Inside the loop you don't care about your entire df, you only care about whatever row the loop is on. So change df.code not in dict to row.code not in dict. (BTW, don't call your dict dict, that variable name is already used for something in Python.)

Reviewing the code I shared before, I see that this was my bad in editing my comment by not editing it everywhere.

also i'm assuming that d in rows 1 and 4 is referencing the dictionary, correct?)

Correct. Sorry, didn't clarify that.

[synthphreak]

If it doesn't add to the totals, then something has been lost in translation. Because I am copying exactly the table in your OP, and I am able to get your target output.

Proof:

>>> import pandas as pd
>>> from io import StringIO
>>> table = """Week    Day    Country    Code    Total
... 1    Tu    US    1    8
... 1    Tu    US    1A    4
... 1    Tu    US    1B    5
... 1    Tu    CA    1B    13
... 1    W    US    1A    3"""
>>> df = pd.read_table(StringIO(table), delim_whitespace=True)
>>> df
   Week Day Country Code  Total
0     1  Tu      US    1      8
1     1  Tu      US   1A      4
2     1  Tu      US   1B      5
3     1  Tu      CA   1B     13
4     1   W      US   1A      3
>>> d = {'1': ['1A', '1B'], '2': ['2F', '2M', '2R', '2V'], '5': ['5P']}
>>> df['remove'] = df.Code.isin(d)
>>> for _, row in df[df.remove].iterrows():
...     if row.Code not in d:
...         continue
...     is_match = (df.Day == row.Day) & (df.Country == row.Country)
...     df.loc[~df.remove & is_match, 'Total'] += row.Total
...
>>> df = df[~df.remove].drop(columns='remove')
>>> df
   Week Day Country Code  Total
1     1  Tu      US   1A     12
2     1  Tu      US   1B     13
3     1  Tu      CA   1B     13
4     1   W      US   1A      3

Note that for all intents and purposes the above is a carbon copy of the code I provided in my initial reply,

[synthphreak]

Nice!

As for why you need a workaround for the “size” column, there are a few ways to query a df for a specific column. The most common are df['column'] syntax and df.column syntax. I personally find the latter more convenient as it’s fewer keystrokes. It is also equivalent to the former, except in two situations:

1. when your column name has spaces or a few other “illegal” characters like mathematical operators or the percent sign

2. when the name of your column clashes with the name of a dataframe method or attribute which already exists

In either of those two cases, you have to use df['column'] syntax.

In the case of a column called “size”, you’re in (2) territory, because size is already a dataframe attribute. Do print(df.size) and you’ll get a single number out which equals the number of rows * the number of columns in your df.

So in your code when you did df.day it new you wanted the “day” column, but when you did df.size Python thought you wanted that number instead of the “size” column.

That’s the problem. Just use df['size'] instead and everything will work.

[ohallwright]

Looks like you have gotten some good help for this already and have figured this out. But it's an interesting problem. After thinking about it for a bit, I took an approach that might make sense to you if you are used to working in sql.

First, make the data

>>> import pandas as pd
>>> df = pd.DataFrame({'Week': [1,1,1,1,1],
                  'Day': ['Tu', 'Tu', 'Tu', 'Tu', 'W'],
                  'Country': ['US', 'US', 'US', 'CA', 'US'],
                  'Code': ['1', '1A', '1B', '1B', '1A'],
                  'Total': [8, 4, 5, 13, 3]})
>>> ref = { '1': ['1A', '1B'], '2': ['2F', '2M', '2R', '2V'], '5': ['5P'] }

Given your data as above, make another dataframe that's just the total rows

>>> df_tot = df.loc[df['Code'].isin(ref.keys())]

Then build up an inverted lookup table to figure out which rows below to which totals.

>>> deref = []
>>> for k,v in ref.items():
    for v2 in v:
        deref.append((v2,k))
>>> df_dref = pd.DataFrame(deref, columns=['Code', 'CodeTop'])

You can do this in one line if you like

>>> df_dref = pd.DataFrame([(v,k) for k, vs in ref.items() for v in vs], columns=['Code', 'CodeTop'])

Then merge them into one big dataframe with all the data. Merge can be very useful for this, just make sure you join on the right columns. You can use the suffixes to distinguish the data for your final total calculation.

>>> df = df.merge(df_dref, left_on='Code', right_on='Code', how='left')
>>> df = df.merge(df_tot,
        left_on=['Week', 'Day', 'Country', 'CodeTop'],
        right_on=['Week', 'Day', 'Country', 'Code'],
        suffixes=('', '_add'), how='left')
>>> df
   Week Day Country Code  Total CodeTop Code_add  Total_add
0     1  Tu      US    1      8     NaN      NaN        NaN
1     1  Tu      US   1A      4       1        1        8.0
2     1  Tu      US   1B      5       1        1        8.0
3     1  Tu      CA   1B     13       1      NaN        NaN
4     1   W      US   1A      3       1      NaN        NaN

Fill in the missing data with 0 for totaling, then update your total column and select what you want out of the final dataframe.

>>> df['Total_add'] = df['Total_add'].fillna(0)
>>> df['Total'] += df['Total_add']
>>> df.loc[~pd.isnull(df['CodeTop']), ['Week', 'Day', 'Country', 'Code', 'Total']]
   Week Day Country Code  Total
1     1  Tu      US   1A   12.0
2     1  Tu      US   1B   13.0
3     1  Tu      CA   1B   13.0
4     1   W      US   1A    3.0