My goal is to group a data frame DF by values of column Name and aggregate specific column as sum.
Current data frame
| Name | Val1 | val2 | val3 | |
|---|---|---|---|---|
| 0 | Test | NaN | 5 | NaN |
| 1 | Test | 30 | NaN | 3 |
| 2 | Test | 30 | NaN | 3 |
Output excepted
| Name | Val1 | val2 | val3 | |
|---|---|---|---|---|
| 0 | Test | 60 | 5 | 3 |
What I tried
DF.groupby(['Name'], as_index=False)[["Val1"]].sum()
returns
| Name | Val1 | |
|---|---|---|
| 0 | Test | 60 |
Issue
I want to take val2 and val3 as unique values and then group them but I don’t know how to do so.
Maybe introducing an intermediary DF
| Name | Val1 | val2 | val3 | |
|---|---|---|---|---|
| 0 | Test | NaN | 5 | 3 |
| 1 | Test | 30 | 5 | 3 |
| 2 | Test | 30 | 5 | 3 |
so that following code can work:
DF.groupby(['Name','val2','val3'], as_index=False)[["Val1"]].sum()
Keep in mind that my data frame has several values for Name in it.
What is the best way to do ?
>Solution :
If I understand correctly, there is only one unique non-missing value in each of the val2 and val3 columns per group. Otherwise your question does not make much sense, because you did not specify how to decide which value to take from these columns.
Given these constraints, you can use:
result = df.groupby('Name', as_index=False).agg({'Val1': 'sum', 'val2': 'first', 'val3': 'first'})