Adding counts from one dataframe to another dataframe on corresponding row

user_13426231 0
Viewed 38

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!

pandas dataframe sql python
1 Answers

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
---------------------------------
user_139213990
Related