How to use the pandas.DataFrame.cumsum() function with a filter faster?

user_13826221 0
Viewed 479

I am a petroleum engineer and have a Data Frame that shows the volume of produced oil for each well in a monthly basis. It has the following columns:

  1. Well: well name.
  2. Date: the first day of the month.
  3. Oil_Prod: the volume of oil produced in the month (Date) in cubic meters (m³).
>>> df = pd.DataFrame({'Well': ['WellA', 'WellA', 'WellA', 'WellA', 'WellB', 'WellB', 'WellB', 'WellC', 'WellC', 'WellC'], 'Date': ['01/01/2020', '01/02/2020', '01/03/2020', '01/04/2020', '01/02/2020', '01/03/2020', '01/04/2020', '01/01/2015', '01/02/2015', '01/05/2015'], 'Oil_Prod': [1000, 2000, 3000, 3000, 2000, 1500, 1500, 500, 500, 300]})
>>> df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y')
>>> df.sort_values(['Well', 'Date'], inplace=True)
>>> df
    Well       Date  Oil_Prod
0  WellA 2020-01-01      1000
1  WellA 2020-02-01      2000
2  WellA 2020-03-01      3000
3  WellA 2020-04-01      3000
4  WellB 2020-02-01      2000
5  WellB 2020-03-01      1500
6  WellB 2020-04-01      1500
7  WellC 2015-01-01       500
8  WellC 2015-02-01       500
9  WellC 2015-05-01       300

I have to create a new column with the cumulative volume of produced oil (Oil_Cum) by well. The Data Frame was previously sorted by Welland Date.

I tried to solve it using a for loop but it takes too long run (see below).

Is there a way to do it faster?

>>> for well in df['Well'].unique() :
...     filter_well = df['Well'] == well
...     df.loc[filter_well, 'Oil_Cum'] = df.loc[filter_well, 'Oil_Prod'].cumsum()
...
>>> df
    Well       Date  Oil_Prod  Oil_Cum
0  WellA 2020-01-01      1000   1000.0
1  WellA 2020-02-01      2000   3000.0
2  WellA 2020-03-01      3000   6000.0
3  WellA 2020-04-01      3000   9000.0
4  WellB 2020-02-01      2000   2000.0
5  WellB 2020-03-01      1500   3500.0
6  WellB 2020-04-01      1500   5000.0
7  WellC 2015-01-01       500    500.0
8  WellC 2015-02-01       500   1000.0
9  WellC 2015-05-01       300   1300.0

Thanks!

pandas-groupby pandas dataframe python-3.x python
2 Answers

Try out this:

import pandas as pd
df = pd.DataFrame({'Well': ['WellA', 'WellA', 'WellA', 'WellA', 'WellB', 'WellB', 'WellB', 'WellC', 'WellC', 'WellC'], 'Date': ['01/01/2020', '01/02/2020', '01/03/2020', '01/04/2020', '01/02/2020', '01/03/2020', '01/04/2020', '01/01/2015', '01/02/2015', '01/05/2015'], 'Oil_Prod': [1000, 2000, 3000, 3000, 2000, 1500, 1500, 500, 500, 300]})
df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y')
df.sort_values(['Well', 'Date'], inplace=True)

df['Oil_Cum'] = df.groupby(['Well'])['Oil_Prod'].cumsum()
user_31574280

You might be interested in:

# assumindg df["Date"] is datetime
# othersise df["Date"] = pd.to_datetime(df["Date"])
df_cum  = pd.pivot_table(data=df, index="Date", columns="Well", values="Oil_Prod").sort_index().cumsum()

print(df_cum)
Well         WellA   WellB   WellC
Date                              
2015-01-01     NaN     NaN   500.0
2015-01-02     NaN     NaN  1000.0
2015-01-05     NaN     NaN  1300.0
2020-01-01  1000.0     NaN     NaN
2020-01-02  3000.0  2000.0     NaN
2020-01-03  6000.0  3500.0     NaN
2020-01-04  9000.0  5000.0     NaN
user_43170580
Related