Skip to content
Advertisement

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!

Advertisement

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
---------------------------------
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement