Question: I have a table with end dates in different years and would like to change any year ending in '2004' to '4712'. The column is Date type and I keep getting an error

I have tried different codes and case statements but none of which worked

```
select
    case
        when h.EndDate like '2004%' then '4712%'
        else h.EndDate
    end as EndDate
from table h

1/31/2004
2/28/2004
3/31/2004

```

and so on would be the only years changed to:

```
1/31/4712
2/28/4712
3/31/4712

```

Answer: 
Define a function to be applied to each row:

```
def fn2(val, maxVal):
    tbl = []
    v1 = val // maxVal
    v2 = val % maxVal
    if v1:
        tbl.extend([maxVal] * v1)
    if v2:
        tbl.append(v2)
    return pd.Series(tbl)

```

In the above function *maxVal* is the maximal *value* to be set in
an output row.

And the actual processing can be done in a single (although chained)
instruction:

```
df.set_index('Id').Values.apply(fn2, maxVal=800).stack()\
    .rename('Values').astype(int)\
    .reset_index(level=1, drop=True).reset_index()

```

Note that before *stack* some values are *NaN*, so the type was changed
to *float*. To change it back to *int*, I added *astype(int)*.

For your sample data the result is:

```
  Id Values
0  A    800
1  A    800
2  A    400
3  B    600

```

Edit following your comment
===========================

If you have **only one** another column, you can "divide" *Values*
into chunks almost as you proposed:

```
df.set_index(['Id', 'AnotherCol']).Values.apply(fn2, maxVal=800)\
    .stack().rename('Values').astype(int)\
    .reset_index(level=2, drop=True).reset_index()

```

The difference from your proposal is that the index level to drop is **2**
(not 0).

But if you have **more** such "additional" columns, a more natural way seems to:

* Calculate the *Series* of "divided *Values*" (I called it *vals*),
with the index taken from source `Id` column.
* Drop "original" *Values* column from *df*.
* Merge it with *vals*. The merge keys are:
	+ for *df* - `Id`,
	+ for *vals* - the index.

So the code is:

```
vals = df.set_index(['Id']).Values.apply(fn2, maxVal=800)\
    .stack().rename('Values').astype(int)\
    .reset_index(level=1, drop=True)
pd.merge(df.drop(columns=['Values']), vals,
    left_on='Id', right_index=True)

```

If you are unhappy about repeating index values, add `.reset_index(drop=True)`
to the last instruction.
This quick solution should give the required results, the code still needs optimization

```
max_val = 800

def split_x (x):
  lst=[]
  while x > max_val:
    x=x-max_val
    lst.append(max_val)
  if x != 0:
    lst.append(x)
  return lst

data = {'id':['A', 'B'],
        'value':[2000,600]}
df = pd.DataFrame(data)
df_new=pd.DataFrame({'id':[],'value':[]})

for i in range(len(df)):

  d=df.loc[i]['value']
  id=df.loc[i]['id']
  s=split_x(d)
  for i in range(len(s)):
    print (i,s[i])
    df_new=df_new.append({'id':id,'value':s[i]},ignore_index=True)

>>> df_new
  id  value
0  A  800.0
1  A  800.0
2  A  400.0
3  B  600.0

```