Adding categories to a column group by id in pandas

Question

I have a df which looks like below. There are 4 political parties and they send emails to voters and note if they are opened by the voters.

    ID  Party   Emails_Sent   Emails_Opened
   A21    2         3               1
   A24    4         1               0
   A21    1         5               0
   A21    3         1               1
   A27    3         2               1
   A27    2         1               0

Not all parties send emails to all voters(ID). But, I want to include all the parties in the party column and assign'0' as Emails and Opened when they not reached a voter like below.

  ID  Party   Emails_Sent   Emails_Opened
   A21    1        5              0
   A21    2        3              1
   A21    3        1              1
   A21    4        0              0
   A24    1        0              0
   A24    2        0              0
   A24    3        0              0
   A24    4        1              0
   A27    1        0              0
   A27    2        1              0
   A27    3        2              1
   A27    4        0              0

I'm not sure how to approach this, any help is appreciated.

Answer 1

Let's try a pivot_table then stack:

import pandas as pd

df = pd.DataFrame(
    {'ID': {0: 'A21', 1: 'A24', 2: 'A21', 3: 'A21', 4: 'A27', 5: 'A27'},
     'Party': {0: 2, 1: 4, 2: 1, 3: 3, 4: 3, 5: 2},
     'Emails_Sent': {0: 3, 1: 1, 2: 5, 3: 1, 4: 2, 5: 1},
     'Emails_Opened': {0: 1, 1: 0, 2: 0, 3: 1, 4: 1, 5: 0}})

# Pivot Then Stack
df = df.pivot_table(index='ID', columns='Party',
                    values=['Emails_Sent', 'Emails_Opened'],
                    fill_value=0).stack().reset_index()

# Reorder Columns
df = df[['ID', 'Party', 'Emails_Sent', 'Emails_Opened']]

print(df.to_string(index=False))

Output:

 ID  Party  Emails_Sent  Emails_Opened
A21      1            5              0
A21      2            3              1
A21      3            1              1
A21      4            0              0
A24      1            0              0
A24      2            0              0
A24      3            0              0
A24      4            1              0
A27      1            0              0
A27      2            1              0
A27      3            2              1
A27      4            0              0