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!
CodePudding user response：
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
---------------------------------