Merge multiple rows in pandas Dataframe based on multiple column values

Question

I have this table in pandas

      | id      |      date | freq| year |        c1|         c2|    c3|
0     |C35600010|   20080922|    A| 2004 | d20040331|        NaN|   NaN|
1     |C35600010|   20080922|    A| 2004 |       NaN|      s2003|   NaN|
2     |C35600010|   20080922|    A| 2004 |       NaN|        NaN|    s3|
3     |C35600010|   20080922|    Q| 2004 |       NaN|        NaN|    s3|
4     |C35600010|   20080923|    A| 2004 |       NaN|        NaN|    s3|

and I want to merge it into

      |       id|      date | freq| year |        c1|         c2|    c3|
0     |C35600010|   20080922|    A| 2004 | d20040331|      s2003|    s3|
1     |C35600010|   20080922|    Q| 2004 |       NaN|        NaN|    s3|
2     |C35600010|   20080923|    A| 2004 |       NaN|        NaN|    s3|

Basically where id, date, freq & year are same, merge the rows. It is guaranteed that only one NaN value will exist. Anyway to do it?

I tried Merging same-indexed rows by taking non-NaNs from all of them in pandas dataframe didn't really work as it throws error

df = df.groupby(["id", "date", "freq", "year"]).max()
ValueError: Wrong number of items passed 1, placement implies 4

Edit 1: There can be multiple dates associated with each id, same with freq & year. I don’t want to merge them into single row.

When id, date, freq, year .. all are same then merge the rows for columns c1, c2, c3.

score 2 · Accepted Answer · 2021-11-12 16:25:51Z

2

Does this do what you want?

df.groupby(["id", "date", "freq", "year"]).first().reset_index()

Output:

          id      date freq  year         c1     c2  c3
0  C35600010  20080922    A  2004  d20040331  s2003  s3
1  C35600010  20080922    Q  2004       None   None  s3
2  C35600010  20080923    A  2004       None   None  s3

edited Nov 12, 2021 at 16:25

answered Nov 12, 2021 at 16:15

user17242583

Sign up to request clarification or add additional context in comments.

2 Comments

Shashank Gandham Over a year ago

No, it won’t. I have edited the question to make it more clear. This will merge rows where the date is different but the id is the same. I don’t want that. I want to check rows, unique on id, date, freq & year.

user17242583 Over a year ago

Check the answer now.

Collectives™ on Stack Overflow

Merge multiple rows in pandas Dataframe based on multiple column values

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related