CODEWITHSUNDEEP
excelpython-3.xpandas
Sunni
Sunni

Reputation: 365

Exporting a pandas DataFrame into excel based on cell values

I've a Dataframe as below. (resulted from pivot_table() method)

Location Loc 2  Loc 3   Loc 5   Loc 8   Loc 9
Item                    
1   404     317     272     113      449 
3   1,205   870     846     371     1,632 
5   208     218     128     31       268 
7   107     54       57     17       179 
9   387     564     245     83       571 
10  364     280     115     34       252 
16  104     80      72      22       143 
17  111     85      44      10       209 
18  124     182     67      27       256 
19  380     465     219     103      596

if you take a closer look at it, there are missing Locations (eg, Loc 1, Loc, 4, etc) and missing Items (eg, 2, 4,8, etc)

I want to export this to my Excel pre-defined Template which has all the Locations & Items & fill the table based on Items & Values.

I know I can export the dataframe to a different excel sheet & use SUMIFS() or INDEX(), MATCH() formulas. but, I want to do this directly from Python/Panda to excel.

Below should be the result after exporting

Loc 1   Loc 2   Loc 3   Loc 4   Loc 5   Loc 6   Loc 7   Loc 8   Loc 9
1   0   404 317 0   272 0   0   113 449
2   0   0   0   0   0   0   0   0   0
3   0   1205    870 0   846 0   0   371 1632
4   0   0   0   0   0   0   0   0   0
5   0   208 218 0   128 0   0   31  268
6   0   0   0   0   0   0   0   0   0
7   0   107 54  0   57  0   0   17  179
8   0   0   0   0   0   0   0   0   0
9   0   387 564 0   245 0   0   83  571
10  0   364 280 0   115 0   0   34  252
11  0   0   0   0   0   0   0   0   0
12  0   0   0   0   0   0   0   0   0
13  0   0   0   0   0   0   0   0   0
14  0   0   0   0   0   0   0   0   0
15  0   0   0   0   0   0   0   0   0
16  0   104 80  0   72  0   0   22  143
17  0   111 85  0   44  0   0   10  209
18  0   124 182 0   67  0   0   27  256
19  0   380 465 0   219 0   0   103 596
20  0   0   0   0   0   0   0   0   0

Upvotes: 2

Views: 167

Answers (1)

jezrael
jezrael

Reputation: 862511

Use DataFrame.reindex with new index and columns values in arrays or lists:

idx = np.arange(1, 21)
cols = [f'Loc {x}' for x in np.arange(1, 10)]

df = df.reindex(index=idx, columns=cols, fill_value=0)
print (df)
    Loc 1  Loc 2  Loc 3  Loc 4  Loc 5  Loc 6  Loc 7  Loc 8  Loc 9
1       0    404    317      0    272      0      0    113    449
2       0      0      0      0      0      0      0      0      0
3       0  1,205    870      0    846      0      0    371  1,632
4       0      0      0      0      0      0      0      0      0
5       0    208    218      0    128      0      0     31    268
6       0      0      0      0      0      0      0      0      0
7       0    107     54      0     57      0      0     17    179
8       0      0      0      0      0      0      0      0      0
9       0    387    564      0    245      0      0     83    571
10      0    364    280      0    115      0      0     34    252
11      0      0      0      0      0      0      0      0      0
12      0      0      0      0      0      0      0      0      0
13      0      0      0      0      0      0      0      0      0
14      0      0      0      0      0      0      0      0      0
15      0      0      0      0      0      0      0      0      0
16      0    104     80      0     72      0      0     22    143
17      0    111     85      0     44      0      0     10    209
18      0    124    182      0     67      0      0     27    256
19      0    380    465      0    219      0      0    103    596
20      0      0      0      0      0      0      0      0      0

Upvotes: 3

Related Questions