Add incremental values following an id

Question

`I'm trying to add incremetal values for each id in this pandas dataframe initial table

id col1   col2
1   0.12     10 
1   0.23     20
1    1.1     30
2   0.25     10
2    2.1     20
2    1.2     30

what i want to acheive

id  col1   col2
1   0.12     10 
1   0.23     20
1    1.1     30
1      0     40
1      0     50
2   0.25     10
2    2.1     20
2    1.2     30
2      0     40
2      0     50

i tried :

def func(row):
    for i in row["id"]:
        for j in range(40,50+1,10):
            row["id"] = i
            row["col1"] = 0
            row["col2"] = j

df = df.apply(lambda row:func(row))

but this raise an error that id doesn't exist KeyError: 'id'

You should use [`iterrows`](https://stackoverflow.com/a/16476974/17200348) to iterate over rows in your dataframe. — B Remmelzwaal, Jan 31 '23 at 19:15
I would do this if I were you. Just copy and paste the code section. sets = [] for id in set(df['id']): for j in range(40, 50 + 1, 10): s = pd.Series({ 'id': id, 'col1': 0, 'col2': j }) sets.append(s) df2 = pd.DataFrame(sets) df = pd.concat([df, df2], ignore_index=True) — SW Jeong, Jan 31 '23 at 19:33

score 0 · Accepted Answer · answered Jan 31 '23 at 19:36

No need for a loop, you can approach this by using a MultiIndex.from_product :

N = 50 # <- adjust here the limit to reach

gr = df.groupby("id", as_index=False).count()
idx = pd.MultiIndex.from_product([gr["id"], range(10, N+10, 10)], names=["id", "col2"])

out = (df.set_index(["id", "col2"]).reindex(idx, fill_value=0).reset_index()[df.columns])

Output :

print(out)

   id  col1  col2
0   1  0.12    10
1   1  0.23    20
2   1  1.10    30
3   1  0.00    40
4   1  0.00    50
5   2  0.25    10
6   2  2.10    20
7   2  1.20    30
8   2  0.00    40
9   2  0.00    50

score 0 · Answer 2 · answered Jan 31 '23 at 19:54

You can group by id and append values to needed columns via pandas concatenation:

df_to_append = pd.DataFrame([[0,40],[0,50]], columns=['col1','col2'])
df = df.groupby('id', as_index=False).apply(lambda x: pd.concat([x, df_to_append], ignore_index=True))\
        .reset_index(drop=True).fillna(method='ffill')

    id  col1  col2
0  1.0  0.12    10
1  1.0  0.23    20
2  1.0  1.10    30
3  1.0  0.00    40
4  1.0  0.00    50
5  2.0  0.25    10
6  2.0  2.10    20
7  2.0  1.20    30
8  2.0  0.00    40
9  2.0  0.00    50

Add incremental values following an id

2 Answers2