Split rows based on different rows and columns

user_11075172 0
Viewed 59

I could really appreciate your help on this.

I have a table with products, dates, and amounts. This is what the initial table looks like.

Product ID     goliveyear     endyear     Revenue   
    1            2020-10       2022-02       90
    1            2020-10       2022-02       140
    1            2020-10       2022-02       60

The purpose is to split each row into the number of months remaining until the end of the year If it's the first year then split starting from the month of the first year until the end of the year If the year is the end year then split until the month in the end year. the revenue needs to be split on the number of rows of the month as the revenue in the first table refers to the whole period. all years in between will be divided into 12 rows along with the revenue one for each month.

  Product ID     goliveyear     endyear     Year    Month   Revenue
    1            2020-10       2022-02       2020     10      90/3=30
    1            2020-10       2022-02       2020     11       30
    1            2020-10       2022-02       2020     12       30
    1            2020-10       2022-02       2021     01     140/12 =11.67
    1            2020-10       2022-02       2021     02       11.67
    1            2020-10       2022-02       2021     03       11.67
    1            2020-10       2022-02       2021     04       11.67
   ...            ...            ...         ...      ...      ... 
    1            2020-10       2022-02       2022     01      60/2 = 30
    1            2020-10       2022-02       2022     02       30

Thank you so much, everyone.

pandas python
2 Answers

Quite a few steps.

Start by setting up the df

from io import StringIO
import pandas as pd
from datetime import datetime,timedelta


df = pd.read_csv(StringIO(
"""
Product_ID     goliveyear     endyear     Revenue   
    1            2020-10       2022-02       90
    1            2020-10       2022-02       140
    1            2020-10       2022-02       60
"""), delim_whitespace=True)
df['goliveyear'] = pd.to_datetime(df['goliveyear'])
df['endyear'] = pd.to_datetime(df['endyear'])
df

Then add year_start, year_end, period_start, period_end columns

df['ys'] = df['goliveyear'].dt.year + df.groupby('Product_ID').cumcount()
df['ye'] = df['ys'] + 1
df['ys'] = pd.to_datetime(df['ys'], format = '%Y')
df['ye'] = pd.to_datetime(df['ye'], format = '%Y')+ timedelta(days=-1)
df['ps'] = df[['goliveyear','ys']].max(axis=1)
df['pe'] = df[['endyear','ye']].min(axis=1)

produces


      Product_ID  goliveyear           endyear                Revenue  ys                   ye                   ps                   pe
--  ------------  -------------------  -------------------  ---------  -------------------  -------------------  -------------------  -------------------
 0             1  2020-10-01 00:00:00  2022-02-01 00:00:00         90  2020-01-01 00:00:00  2020-12-31 00:00:00  2020-10-01 00:00:00  2020-12-31 00:00:00
 1             1  2020-10-01 00:00:00  2022-02-01 00:00:00        140  2021-01-01 00:00:00  2021-12-31 00:00:00  2021-01-01 00:00:00  2021-12-31 00:00:00
 2             1  2020-10-01 00:00:00  2022-02-01 00:00:00         60  2022-01-01 00:00:00  2022-12-31 00:00:00  2022-01-01 00:00:00  2022-02-01 00:00:00

Then add months as lists at first

df['months'] = df.apply(lambda r: [d.month for d in pd.date_range(r['ps'], r['pe'], freq='MS', closed = None).to_pydatetime()], axis=1)

output:

      Product_ID  goliveyear           endyear                Revenue  ys                   ye                   ps                   pe                   months
--  ------------  -------------------  -------------------  ---------  -------------------  -------------------  -------------------  -------------------  ---------------------------------------
 0             1  2020-10-01 00:00:00  2022-02-01 00:00:00         90  2020-01-01 00:00:00  2020-12-31 00:00:00  2020-10-01 00:00:00  2020-12-31 00:00:00  [10, 11, 12]
 1             1  2020-10-01 00:00:00  2022-02-01 00:00:00        140  2021-01-01 00:00:00  2021-12-31 00:00:00  2021-01-01 00:00:00  2021-12-31 00:00:00  [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]
 2             1  2020-10-01 00:00:00  2022-02-01 00:00:00         60  2022-01-01 00:00:00  2022-12-31 00:00:00  2022-01-01 00:00:00  2022-02-01 00:00:00  [1, 2]

Then we explode months do the required calc for revenue and drop unneeded columns

df = df.explode('months')
df['Revenue'] = df['Revenue'] / df.groupby(['Product_ID','ys'])['months'].transform('count')
df = df.drop(columns = ['goliveyear','endyear','ye','ps','pe'])
df['ys'] = df['ys'].dt.year

to get


      Product_ID    Revenue    ys    months
--  ------------  ---------  ----  --------
 0             1    30       2020        10
 0             1    30       2020        11
 0             1    30       2020        12
 1             1    11.6667  2021         1
 1             1    11.6667  2021         2
 1             1    11.6667  2021         3
 1             1    11.6667  2021         4
 1             1    11.6667  2021         5
 1             1    11.6667  2021         6
 1             1    11.6667  2021         7
 1             1    11.6667  2021         8
 1             1    11.6667  2021         9
 1             1    11.6667  2021        10
 1             1    11.6667  2021        11
 1             1    11.6667  2021        12
 2             1    30       2022         1
 2             1    30       2022         2
user_145514260

Try this:

import pandas as pd
from io import StringIO

s = """
Product ID,goliveyear,endyear,Revenue
1,2020-10,2022-02,90
1,2020-10,2022-02,140
1,2020-10,2022-02,60"""

df = pd.read_csv(StringIO(s))

# generate the months list between thest two months
df['rng'] = df.apply(lambda x: pd.date_range(x['goliveyear'], x['endyear'], 
freq='MS'), axis=1)

# explode the dataframe by months list
df_exploded = df.explode('rng')

df_exploded['Year'] = df_exploded['rng'].dt.year
df_exploded['Month'] = df_exploded['rng'].dt.month

# the (index,year) pair to fliter rows
filter_year = list(zip(df.index, df_exploded.Year.unique()))

# used columns
use_cols = ['Product ID', 'goliveyear', 'endyear', 'Revenue', 'Month']

# filter rows
df_filter = df_exploded.set_index([df_exploded.index, 
df_exploded.Year]).loc[filter_year, 
use_cols].reset_index().drop(columns='level_0')

# calculate the average Revenue
result = df_filter.set_index(['Year', "Month"]).assign(
    Revenue=(df_filter.groupby(['Year', 'Month'])['Revenue'].sum() / 
     df_filter.groupby('Year')['Month'].count())
).reset_index()

result

Output

    Year    Month   Product ID  goliveyear  endyear Revenue
0   2020    10  1   2020-10 2022-02 30.000000
1   2020    11  1   2020-10 2022-02 30.000000
2   2020    12  1   2020-10 2022-02 30.000000
3   2021    1   1   2020-10 2022-02 11.666667
4   2021    2   1   2020-10 2022-02 11.666667
5   2021    3   1   2020-10 2022-02 11.666667
6   2021    4   1   2020-10 2022-02 11.666667
7   2021    5   1   2020-10 2022-02 11.666667
8   2021    6   1   2020-10 2022-02 11.666667
9   2021    7   1   2020-10 2022-02 11.666667
10  2021    8   1   2020-10 2022-02 11.666667
11  2021    9   1   2020-10 2022-02 11.666667
12  2021    10  1   2020-10 2022-02 11.666667
13  2021    11  1   2020-10 2022-02 11.666667
14  2021    12  1   2020-10 2022-02 11.666667
15  2022    1   1   2020-10 2022-02 30.000000
16  2022    2   1   2020-10 2022-02 30.000000
user_50147000
Related