Adding counts from one dataframe to another dataframe on corresponding row

I would like to count the number of record in dataframe2 and add the count to the corresponding rows in dataframe1.

The first one (df1)

Road	RoadNo	Count
A	1	0
A	2	0
B	1	0
B	2	0

The second one (df2)

Road	RoadNo
A	1
A	1
A	1
A	2
A	2
B	1

The expected output is to count the number of records in dataframe2 and add that number to the corresponding Road and RoadNo rows in dataframe1.

Expected Output:

Road	RoadNo	Count
A	1	3
A	2	2
B	1	1
B	2	0

Is there a function in Pandas or Python that can help me with this operation? Do I need to create two tables in the database and execute the SQL query to perform this operation? Many Thanks!

Answer

You could first count the values from df2 for all Road and RoadNo pairs and then join the resulting data frame to df1:

df1 = pd.DataFrame(data={"Road":["A", "A", "B", "B"],   "RoadNo": [1, 2, 1, 2], "Count":[0, 0, 0, 0]})

df2 = pd.DataFrame(data={"Road":["A", "A", "A", "A", "A", "B"], "RoadNo": [1, 1, 1, 2, 2, 1]})


merge_df = df2.value_counts(["Road", "RoadNo"]).reset_index().rename(columns={0: "Count"})
expected_df = pd.merge(left=df1.drop(columns=["Count"]), right=merge_df, on=["Road", "RoadNo"], how="left").fillna(0).astype({"Count": "int32"})
expected_df

---------------------------------
    Road RoadNo Count
0   A    1      3
1   A    2      2
2   B    1      1
3   B    2      0
---------------------------------