Dynamic Column Data In Pandas Dataframe (Populated By Dataframe)

There are quite a few concepts you need at once here.

First you dont yet have the count. From your desired output I took you want it yearly but you can specify any time frame you want. Then just count with groupby() and count()

In [66]: df2 = df.groupby([pd.to_datetime(df.date).dt.year, "action", "department"]).count().squeeze().rename("count")                                                                                            
Out[66]: 
date  action  department
2017  close   marketing     2
      open    sales         1
2018  close   marketing     1
      open    marketing     1
      other   marketing     1
2019  close   marketing     1
              sales         1
      open    sales         1
Name: count, dtype: int64

The squeeze() and rename() are there because afterwards both the count column and the year would be called date and you get a name conflict. You could equivalently use rename(columns={'date': 'count'}) and not cast to a Series.

The second step is a pivot_table. This creates column names from values. Because there are combinations of date and action without a corresponding value, you need pivot_table.

In [62]: df2.pivot_table(index="department", columns=["date", "action"])                                                                                                                                    
Out[62]: 
           count                                 
date        2017       2018             2019     
action     close open close open other close open
department                                       
marketing    2.0  NaN   1.0  1.0   1.0   1.0  NaN
sales        NaN  1.0   NaN  NaN   NaN   1.0  1.0

Because NaN is internally representated as floating piont, your counts were also converted to floating point. To fix that, just append fillna and convert back to int.

In [65]: df2.reset_index().pivot_table(index="department", columns=["date", "action"]).fillna(0).astype(int)                                                                                                              
Out[65]: 
           count                                 
date        2017       2018             2019     
action     close open close open other close open
department                                       
marketing      2    0     1    1     1     1    0
sales          0    1     0    0     0     1    1

To get exactly you output you would need to modify pd.to_datetime(df.date).dt.year. You can do this with strftime (https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.dt.strftime.html). Furthermore the column ["2017", "other"] was dropped because there was no value. If this creates problems you need to include the values beforehand. After the first step a reindex and a fillna should do the trick.

EDIT: Yes it does

In [77]: new_index = pd.MultiIndex.from_product([[2017, 2018, 2019], ["close", "open", "other"], ['marketing', 'sales']], names=['date', 'action', 'department'])
    ...:

In [78]: df3 = df2.reindex(new_index).fillna(0).astype(int).reset_index()
Out[78]: 
    date action department  count
0   2017  close  marketing      2
1   2017  close      sales      0
2   2017   open  marketing      0
3   2017   open      sales      1
4   2017  other  marketing      0
5   2017  other      sales      0
6   2018  close  marketing      1
..   ...    ...        ...    ...
11  2018  other      sales      0
12  2019  close  marketing      1
13  2019  close      sales      1
14  2019   open  marketing      0
15  2019   open      sales      1
16  2019  other  marketing      0
17  2019  other      sales      0


In [79]: df3.pivot_table(index="department", columns=["date", "action"])                                                                                                                                    
Out[79]: 
           count                                             
date        2017             2018             2019           
action     close open other close open other close open other
department                                                   
marketing      2    0     0     1    1     1     1    0     0
sales          0    1     0     0    0     0     1    1     0