Pandas add calculated row for every row in a dataframe

Question

I have a dataframe like so:

id  variable  value
1      x        5
1      y        5
2      x        7
2      y        7

Now for every row, I want to add a calculated row. This is what I am doing as of now:

a = 2
b = 5
c = 1
d = 3
df2 = pd.DataFrame(columns =  ["id", "variable", "value"])
for index, row in df.iterrows():
    if row['variable'] == 'x':
        df2 = df2.append({'id':row['id'], 'variable':'x1', 'value':a*row['value']+b}, ignore_index=True)
    else:
        df2 = df2.append({'id':row['id'], 'variable':'y1', 'value':c*row['value']+d}, ignore_index=True)
df = pd.concat([df, df2])
df = df.sort_values(['id', 'variable'])

And so finally I get:

id  variable  value
1      x        5
1      x1       15
1      y        5
1      y1       8
2      x        7
2      x1       19
2      y        7
2      y1       10

But surely there must be a better way to do this. Perhaps where I could avoid for loop and sorting, as there are a lot of rows.

Answer 1

iterrows

Of all the ways to iterate over a pandas DataFrame, iterrows is the worst. This creates a new series for each row. this series also has a single dtype, so it gets upcast to the least general type needed. This can lead to unexpected loss of information (large ints converted to floats), or loss in performance (object dtype).

Slightly better is itertuples

append

Appending row per row can be very slow (link1link2)

Better would be to assembly them in a list, and make a new DataFrame in 1 go.

vectorise

One easy change you can make is not iterating over the database in 'Python' space, but using boolean indexing

x = df["variable"] == "x"
y = df["variable"] == "y"

df2 = df.copy()
df2.loc[x, "value"] = df.loc[x, "value"] * a + b
df2.loc[y, "value"] = df.loc[y, "value"] * c + d
df2.loc[x, "variable"] = "x1"
df2.loc[y, "variable"] = "y1"

wide vs long

This can be made a lot easier by reforming your dataframe by making it a bit wider:

df_reformed = (
    df.set_index(["id", "variable"]).unstack("variable").droplevel(0, axis=1)
)

variable  x   y
id        
1     5   5
2     7   7

Then you can calculate x1 and y1 vectorised:

df_reformed.assign(
    x1=df_reformed["x"] * a + b, 
    y1=df_reformed["y"] * c + d
)

and then convert this back to the long format:

result = (
    df_reformed.assign(
        x1=df_reformed["x"] * a + b, 
        y1=df_reformed["y"] * c + d
    )
    .stack()
    .rename("value")
    .sort_index()
    .reset_index()
)

Answer 2

I agree with the accepted answer. An alternative way to frame this is a multi-index, with indices of id and variable.

You will then effectively have three-dimensional data, where the first dimension is an integral ID, the second dimension is a categorical variable name, and the third dimension is your value.

You could extend this concept even further, with dimensions of id, variable (only to contain x and y), subscript (0 or 1, whatever that represents in your context), and value. Certain indexing operations will be made easier by this approach.