I have a pandas dataframe as:
| ID | Date | Value |
|---|---|---|
| A | 1/1/2000 | 5 |
| A | 2/1/2000 | 10 |
| A | 3/1/2000 | 20 |
| A | 4/1/2000 | 10 |
| B | 1/1/2000 | 100 |
| B | 2/1/2000 | 200 |
| B | 3/1/2000 | 300 |
| B | 4/1/2000 | 400 |
How do I evaluate the monthly fraction of the total yearly value for each ID as the fourth column?
| ID | Date | Value | Fraction |
|---|---|---|---|
| A | 1/1/2000 | 5 | 0.11 |
| A | 2/1/2000 | 10 | 0.22 |
| A | 3/1/2000 | 20 | 0.44 |
| A | 4/1/2000 | 10 | 0.11 |
| B | 1/1/2000 | 100 | 0.11 |
| B | 2/1/2000 | 200 | 0.22 |
| B | 3/1/2000 | 300 | 0.33 |
| B | 4/1/2000 | 400 | 0.44 |
I guess I could use groupby?
You can divide the Value column by the result of a groupby.transform sum, followed by round(2) to match your expected output:
df['Fraction'] = df['Value'] / df.groupby('ID')['Value'].transform('sum')
df['Fraction'] = df['Fraction'].round(2)
print(df)
ID Date Value Fraction
0 A 1/1/2000 5 0.11
1 A 2/1/2000 10 0.22
2 A 3/1/2000 20 0.44
3 A 4/1/2000 10 0.22
4 B 1/1/2000 100 0.10
5 B 2/1/2000 200 0.20
6 B 3/1/2000 300 0.30
7 B 4/1/2000 400 0.40
I think your data is missing another year to be representative, if you do not have just a single year in the DataFrame.
I just added one line for 2001:
import pandas as pd
df['Date'] = pd.to_datetime(df['Date'])
print(df)
ID Date Value
0 A 2000-01-01 5
1 A 2000-02-01 10
2 A 2000-03-01 20
3 A 2000-04-01 10
4 B 2000-01-01 100
5 B 2000-02-01 200
6 B 2000-03-01 300
7 B 2000-04-01 400
8 B 2001-04-01 20
If I understood correctly you can do it like this:
df['Fraction'] = (df['Value'] / df.groupby(['ID', df['Date'].dt.year])['Value'].transform('sum')).round(2)
print(df)
ID Date Value Fraction
0 A 2000-01-01 5 0.11
1 A 2000-02-01 10 0.22
2 A 2000-03-01 20 0.44
3 A 2000-04-01 10 0.22
4 B 2000-01-01 100 0.10
5 B 2000-02-01 200 0.20
6 B 2000-03-01 300 0.30
7 B 2000-04-01 400 0.40
8 B 2001-04-01 20 1.00
