Group by date and sum columns

Question

I have a dataframe that has a poor design choice that looks like this:

df = pd.DataFrame({
    'Day': ['2021-03-01', '2021-03-01', '2021-03-02', '2021-03-02', '2021-03-03'],
    'Name': ['Sam', 'Sam', 'Jack', 'Steve', 'Steve'],
    'Fruit':['Apple', 'Apple', 'Banana', 'Apple', 'Banana'],
    '2021-03-01':['2', '1', '0', '1', '1'],
    '2021-03-02': ['3', '5', '4', '2', '1'],
    '2021-03-03':['7', '3', '2', '1', '4']})

df
          Day   Name   Fruit 2021-03-01 2021-03-02 2021-03-03
0  2021-03-01    Sam   Apple          2          3          7
1  2021-03-01    Sam   Apple          1          5          3
2  2021-03-02   Jack  Banana          0          4          2
3  2021-03-02  Steve   Apple          1          2          1
4  2021-03-03  Steve  Banana          1          1          4

I want, for each date in Day, corresponding to the date in the columns that have the same column name as the value of Day, grouping the other variables and aggregating them by sum. For example, if the Day value is '2021-03-01', then in the '2021-03-01' column, I'll sum the values (nr of apples and nbr of bananas in this column).

It want the result dataframe to look like this :

         Date   Name  nrApples  nrBananas
0  2021-03-01    Sam         3          0
1  2021-03-02   Jack         0          4
2  2021-03-02  Steve         2          0
3  2021-03-03  Steve         0          4

The problem is that I have a lot of these date columns, and I don't know how to check if the value in the Day columns is the same as the name of a column, and aggregate the values of it. Thanks for your help.

user_7175713 · Answer

Melt the columns, compare the Day column to the variable column that contains the Days, groupby, aggregate(sum), unstack, and reset index:

(df
 .melt(['Day', 'Name', 'Fruit'])
 .loc[lambda df: df.Day.eq(df.variable)]
 .astype({"value": int})
 .groupby(['Day', 'Name', 'Fruit'])['value']
 .sum()
 .unstack(fill_value = 0)
 .add_prefix("nr")
 .reset_index()
 .rename_axis(columns = None)
 )
 
Fruit         Day   Name  nrApple  nrBanana
0      2021-03-01    Sam        3         0
1      2021-03-02   Jack        0         4
2      2021-03-02  Steve        2         0
3      2021-03-03  Steve        0         4

user_10035985 · Answer

def fn(x):
    return (
        x.set_index("Fruit")
        .loc[:, x.Day.iat[0]]
        .astype(int)
        .groupby(level=0)
        .sum()
        .to_dict()
    )


df = (
    df.groupby(["Day", "Name"])
    .apply(fn)
    .apply(pd.Series)
    .add_prefix("nr")
    .reset_index()
    .fillna(0)
)
print(df)

Prints:

          Day   Name  nrApple  nrBanana
0  2021-03-01    Sam      3.0       0.0
1  2021-03-02   Jack      0.0       4.0
2  2021-03-02  Steve      2.0       0.0
3  2021-03-03  Steve      0.0       4.0

Group by date and sum columns

2 Answers