Sum of Rows Based on Column Value

Sum of rows based on column value

ddply(df, "X1", numcolwise(sum))

see ?numcolwise for details and examples.

Sum rows based on columns inside pandas dataframe

You can replace values of tuple by first value of tuple in Series.mask and then aggregate sum:

tup = (1, 2)

df['idbasin'] = df['idbasin'].mask(df['idbasin'].isin(tup), tup[0])
#alternative
#df['idbasin'] = np.where(df['idbasin'].isin(tup), tup[0], df['idbasin'])
df = df.groupby(['idrun', 'idbasin','time'], as_index=False)['q'].sum()
print (df)
    idrun  idbasin  time    q
0 -192541        1     0  0.0
1 -192541        1     1  1.5
2 -192541        3     0  0.0
3 -192541        3     1  1.0
4 -192540        1     0  0.0
5 -192540        1     1  1.5
6 -192540        3     0  0.0
7 -192540        3     1  1.0

How to update value of a column based on sum of same column values of other records in MYSQL?

Mysql multi table update manual page - https://dev.mysql.com/doc/refman/8.0/en/update.html

eg

drop table if exists t;

CREATE TABLE T(ID INT,SUBJECT VARCHAR(10), MARK INT);

INSERT INTO T VALUES
(1,'ALL',NULL),(1,'AAA',1),(1,'BBB',2),
(2,'ALL',NULL),(2,'AAA',10),(2,'BBB',20);

UPDATE T 
JOIN
 (SELECT ID,SUM(MARK) MARK FROM T WHERE SUBJECT <> 'ALL' GROUP BY ID) S ON S.ID = T.ID
SET T.MARK = S.MARK
WHERE SUBJECT = 'ALL';

Sum if column name is higher than row value

We can check with np.greater_equal.outer, then slice the column mask the unwanted cell with boolean output as NaN

s = pd.to_datetime(df.date).values
m = np.greater_equal.outer(pd.to_datetime(df.columns[:-1]).values,s).T
df = df.append(df.iloc[:,:-1].where(m).sum().to_frame('Total').T)
df
Out[381]: 
       01-01-2020  01-01-2021  01-01-2022        date
1             1.0         3.0         6.0  01-01-2020
2             4.0         4.0         2.0  01-10-2021
3             5.0         1.0         9.0  01-12-2021
Total         1.0         3.0        17.0         NaN

Bigquery query to get sum of values of one column based on another column

Try this:

WITH sample AS (
  SELECT * FROM UNNEST([
    STRUCT('abc' AS company, 'http://www.abc.net1' AS link, 1 AS full_count),
    ('abc', 'http://www.abc.net1/page1', 2),
    ('abc', 'http://www.abc.net1/page1/folder1', 3),
    ('abc', 'http://www.abc.net1/page1/folder2', 4),
    ('abc', 'http://www.abc.net1/page2', 5),
    ('xyz', 'http://www.xyz.net1/', 6),
    ('xyz', 'http://www.xyz.net1/page1/', 7),
    ('xyz', 'http://www.xyz.net1/page1/file1', 8)
  ])
)
SELECT first.company, first.link, SUM(second.full_count) AS starts_with_count
  FROM sample first, sample second 
 WHERE STARTS_WITH(second.link, first.link)
 GROUP BY 1, 2
;

output:

SUM all row based on selected name First Column in Excel

Use SUMPRODUCT() instead.

=SUMPRODUCT((B3:D5)*(A3:A5=B8))

For Microsoft-365 you can use FILTER().

=SUM(FILTER(B3:D5,A3:A5=B8))

Group by month, sum rows based in column, and keep the other columns

IIUC, you need a single groupby. You need to rework your "revenue" column as numeric.

df['date'] = pd.to_datetime(df['date'], dayfirst=True)

group = df['date'].dt.strftime('%b')

(df.assign(revenue=pd.to_numeric(df['revenue'].str.replace(',', '.')))
   .groupby([group, 'name', 'type'])
   .agg('sum')
   .reset_index()
 )

Output:

   date name      type      size  revenue
0   Apr    A     Basic   6908746      0.1
1   Dec    A  Standard    248753      0.4
2   Dec    B   Premium     82346      0.5
3   Feb    A     Basic   3356943      0.1
4   Feb    D   Premium  12049667      2.1
5   Feb    E  Standard       486      0.9
6   Jan    C     Basic   3589749      0.4
7   Mar    A     Basic     28586      0.2
8   Mar    B     Basic   8734684      0.1
9   Mar    D   Premium       192      0.7
10  Oct    F     Basic     23847      0.3

Note that the above is aggregating months of different years into the same group. If you want to keep years separate, use a period:

group = df['date'].dt.to_period('M')

Output:

       date name      type      size  revenue
0   2021-01    C     Basic   3589749      0.4
1   2021-02    A     Basic   3356943      0.1
2   2021-04    A     Basic   6908746      0.1
3   2021-10    F     Basic     23847      0.3
4   2021-12    A  Standard    248753      0.4
5   2021-12    B   Premium     82346      0.5
6   2022-02    D   Premium  12049667      2.1
7   2022-02    E  Standard       486      0.9
8   2022-03    A     Basic     28586      0.2
9   2022-03    B     Basic   8734684      0.1
10  2022-03    D   Premium       192      0.7

Pandas sum rows by group based on condition

agg can be very useful here. Try this:

df = df.groupby('Region', as_index=False).agg({'Year':'max', 'value':'sum'})

Output:

>>> df
  Region  Year  value
0     R1  2017     18
1     R2  2018     27
2     R3  2019     31

Get sum of column values for specific rows only | Get sum of sales for each country

This is simple to do in DAX. Add the column:

TotalSalesPerCountry = 
var curCountry = yourTable[Country]
return CALCULATE(SUM(Sales), FILTER(yourTable, curCountry = yourTable[Country]))

Explanation:
For each row in the table, get the country. Filter yourTable on the curCountry and SUM this together).

---

Related Topics