Pandas - Find Rows With Matching Values in Two Columns and Multiply Value in Another Column

Pandas - find rows with matching values in two columns and multiply value in another column

One way is to groupby A + C, take the product and count, filter out those that only have a single item in the group, then inner merge back on A + C to your original frame, eg:

df.merge(
    df.groupby(['A', 'C']).D.agg(['prod', 'count'])
    [lambda r: r['count'] > 1],
    left_on=['A', 'C'],
    right_index=True
)

Gives you:

     A   C  D  id  prod  count
0  foo  10  9   1    63      2
2  foo  10  7   3    63      2
4  foo  50  5   5    15      2
6  foo  50  3   7    15      2

Then drop/rename columns as appropriate.

Python - multiply columns based on values in other column, and values located in the first row of the same columns

Extract values from weight and 1st row of T columns and then do an outer product:

tcols = df.filter(like='T').columns
df[tcols] = np.outer(df.weight, df[tcols].loc[0])

df
  ID  weight      T1      T2      T3      T4
0  A    1.00  1000.0  2000.0  3000.0  4000.0
1  B    0.04    40.0    80.0   120.0   160.0
2  C    0.01    10.0    20.0    30.0    40.0
3  D    0.06    60.0   120.0   180.0   240.0

python pandas how to multiply columns by other values in different dataframe

Use dot for the multiplication and join to df1:

#set indices if needed
df1 = df1.set_index("ITEM")
df2 = df2.set_index("prices")

output = df1.join(df1.drop("tag", axis=1).dot(df2).add_suffix("_price"))

>>> output
  ITEM  tag  A35  A37  A38  day1_price  day2_price
0   B1  SAS    8    3    1          17          31
1   B2  HTW    1    3    3          16          12
2   B3  ASD    0    8    0          16          16
3   B4  KLD    1   10    0          21          23

multiply two dataframes by matching rows and columns

Update

We can try

with(
  df1,
  as.data.frame(t(t(unname(df2[as.character(ID)])) * Value))
)

which gives

    V1   V2   V3   V4   V5
1 2556 2568 2150 2160 2387
2 5325 5350 3225 3240 4340
3 6390 6420 4300 4320 5425

---

Maybe this base R code with reshape + merge could help

reshape(
  transform(
    type.convert(
      merge(df1, stack(df2), by.x = "ID", by.y = "ind", all = TRUE),
      as.is = TRUE
    ),
    idx = ave(ID, ID, Value, FUN = seq_along),
    p = Value * values
  )[c("Value", "idx", "p")],
  direction = "wide",
  idvar = "idx",
  timevar = "Value"
)

which gives

  idx p.215 p.216 p.217 p.213 p.214
1   1  3225  3240  5425  6390  6420
2   2  2150  2160  2387  2556  2568
3   3  4300  4320  4340  5325  5350

---

Data

df1 <- data.frame(
  ID = c(105, 105, 90, 90, 100),
  Value = 213:217
)

df2 <- data.frame(
  `90` = c(10, 15, 20),
  `100` = c(11, 20, 25),
  `105` = c(12, 25, 30),
  check.names = FALSE
)

Pandas multiply column by another column in all rows below

I believe this will achieve your objective:

df['C'] = df.B[::-1].cumsum()[::-1].shift(-1).fillna(0) * df.A

df.B[::-1] reverses the series, and then the cumulative sum is calculated on this reversed series, which is then reversed again to get it in the original order. The net effect is a cumsum bottom up instead of top down.

You then need to shift this cumsum row by one and multiply it by column 'A'.

The fillna(0) was to match your results, otherwise the bottom number would be NaN because there are no rows below it.

Pandas: Multiply row value by groupby of another column as a new column

Use the transform option, to align the values with the length of the original dataframe; should be faster than an apply, and without the anonymous function :

df['NewSales'] = df.groupby(['state', 'store']).sales.transform('sum') * df.rate

print(Df)

         state store  sales  rate  NewSales
0  california     a     11   0.6      13.8
1  california     a     12   0.4       9.2
2  california     b     32   0.7      22.4

multiply columns based on values in other column

Here you go:

df3 = df1.merge(df2[['name', 'multiplier']], on='name')
df3['product'] = df3['number'] * df3['multiplier']
print(df3)

## -- End pasted text --
   id name  number  multiplier  product
0   1   aa       3           2        6
1   2   aa       6           2       12
2   3   bb       2           4        8
3   4   bb       8           4       32
4   5   cc       3           6       18

---

Related Topics