Calculating field in Python pandas for multiple months based on different parameters

user_15088001 0
Viewed 39

I am trying to create calculated_field for every every month from Year 1 to Year 10 based on parameter values in Data Frame 1. From the dataframe given below we need create Data Frame 2 from Data Frame 1. Basically each id will have 120 rows( 12 months * 10 Years) and this needs to be done for thousands of unique id. What would be the best way to do this using What is the best way to do this in pandas with optimal runtime:-

Data Frame 1:-

ID VAR1 VAR2
1 ABC XYZ
2 MNP QRS
3 UVW STU

Data Frame 2:-

ID YEAR_MONTH VAR1 VAR2 calculated_field
1 1_1 ABC XYZ 5
1 1_2 ABC XYZ 6
1 1_3 ABC XYZ 7
2 1_1 MNP QRS 11
2 1_2 MNP QRS 12
2 1_3 MNP QRS 13
3 1_1 UVW STU 43
3 1_2 UVW STU 44
3 1_3 UVW STU 45
pandas dataframe join python
1 Answers

A simple way to do this is a Cartesian product. You have not provided a formula for calculated_field so I've just provided an example. More years, change parameters to range()

# staright cartesian product to get combinations
# don't know formula for calculated field... just a demo calc
df2 = (pd.DataFrame({"YEAR_MONTH":[f"{y+1}_{m+1}" for y in range(10) for m in range(12)]})
 .assign(foo=1)
 .merge(df.assign(foo=1)).drop(columns="foo")
 .assign(calculated_field=lambda d: d.YEAR_MONTH.str.replace("_","+").apply(pd.eval)+d.ID)
)

# head & tail of DF
df2.iloc[np.r_[0:4,-4:0]]
YEAR_MONTH ID VAR1 VAR2 calculated_field
0 1_1 1 ABC XYZ 3
1 1_1 2 MNP QRS 4
2 1_1 3 UVW STU 5
3 1_2 1 ABC XYZ 4
356 10_11 3 UVW STU 24
357 10_12 1 ABC XYZ 23
358 10_12 2 MNP QRS 24
359 10_12 3 UVW STU 25
user_94414040
Related