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