I need your kind suggestions with a table creation.

I have a table that looks like this.

And this goes on till May-22 for DOJ and 12 other departments.

I cannot pivot this table, because having duplicates in the first column where the date is missing Tableau will add the numbers up, and Python will throw an error such as

ValueError: Index contains duplicate entries, cannot reshape

Every month has 12 entries only (12 days a month). I want to add numbers 1 to 12 at the beginning of each Month column so the entries have a unique ID that i can use for df1.pivot(index='Month',columns='Department', values='Total Expenditure Estimate')

The new table should look like

I will appreciate your kind suggestions. Please kindly help me out and advise how to fix this column. Thank you in advance.

CodePudding user response：

Try:

df["Month"] = (
    (df.groupby(["Month", "Department"])["Month"].cumcount()   1).astype(str)
      "-"
      df["Month"]
)

Prints:

        Month Department  Total Expenditure
0    1-Jan-18        DOJ             625000
1    2-Jan-18        DOJ             975000
2    3-Jan-18        DOJ             650000
3    4-Jan-18        DOJ             625000
4    5-Jan-18        DOJ             975000
5    6-Jan-18        DOJ             650000
6    7-Jan-18        DOJ             625000
7    8-Jan-18        DOJ             975000
8    9-Jan-18        DOJ             650000
9   10-Jan-18        DOJ             625000
10  11-Jan-18        DOJ             975000
11  12-Jan-18        DOJ             650000
12   1-Feb-18        DOJ             625000
13   2-Feb-18        DOJ             975000
14   3-Feb-18        DOJ             650000
15   4-Feb-18        DOJ             625000
16   5-Feb-18        DOJ             975000
17   6-Feb-18        DOJ             650000
18   7-Feb-18        DOJ             625000
19   8-Feb-18        DOJ             975000
20   9-Feb-18        DOJ             650000