Add incremental values following an id-CodePudding

`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'

CodePudding user response：

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

CodePudding user response：

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