python - Pandas DataFrame - Recently registered clients orders count per registration day

Question

I have a table Clients ['client_uuid', 'registered_at'] (['some_uuid', '2020-01-01']). And an Orders table ['order_created_at', 'client_uuid']. I need to aggregate this data like this:

Column 1: Day (date)
Column 2: The number of clients, that registered that day, who have at least one order that day
Column 2: The number of orders made by clients who registered that day
Column 3: The numbers of all clients, who have at least one order that day
Column 3: The number of total orders by all clients in that day

Probably there is a solution using loops and so on, but I would like a more beautiful solution with maybe a groupby, agg functions. But I haven't ideas.

For example: Orders Table

order_created_at | client_uuid
2020-01-01       | client1
2020-01-01       | client2
2020-01-01       | client2
2020-01-01       | client4
2020-01-02       | client2
2020-01-02       | client3

Clients Table

registered_at    | client_uuid
2020-01-01       | client1
2020-01-01       | client2
2020-01-02       | client3

Output Table

[date, new_clients_first_orders, new_clients_total_orders, total_clients, total_orders]
[2020-01-01, 2, 3, 3, 4]
[2020-01-02, 1, 1, 2, 2]

question from:https://stackoverflow.com/questions/65888639/pandas-dataframe-recently-registered-clients-orders-count-per-registration-day

Answer 1

Try:

pd.concat((orders.groupby('order_created_at')['client_uuid']
                 .agg(['count','nunique'])
                 .add_prefix('total_'),
           clients.groupby(['registered_at'])['client_uuid']
                 .agg(['count','nunique'])
                 .add_prefix('new_total_')
          ), axis=1
)

Output:

            total_count  total_nunique  new_total_count  new_total_nunique
2020-01-01            4              3                2                  2
2020-01-02            2              2                1                  1