How to write groupby grouped table from dataFrame to word document?

Question

I am new to pandas and python-docx, have a table like this using groupby in pandas:

On printing the df using print(df)

Output:

Change Type      typeA        typeB     typeC    typeD    typeE    typeF
Component
A                  0            2        6         0         0       6
B                  0            3        2         1         1       3
C                  0            1        0         0         0       4
D                  0            2        2         0         0       3
E                  0            0        0         0         0       1
F                  0            3        0         0         1       2
G                  2            1        3         0         2       3
H                  0            0        0         0         0       1
I                  0            1        0         0         0       0

I am using the following to write this dataFrame df to word document:

t = doc.add_table(df.shape[0]+1, df.shape[1])

for j in range(df.shape[-1]):
    t.cell(0,j).text = df.columns[j]


for i in range(df.shape[0]):
    for j in range(df.shape[-1]):
        t.cell(i+1,j).text = str(df.values[i,j])

as specified in the answer: Writing a Python Pandas DataFrame to Word document

I am getting the following printed:

typeA        typeB     typeC    typeD    typeE    typeF
  0            2        6         0         0       6
  0            3        2         1         1       3
  0            1        0         0         0       4
  0            2        2         0         0       3
  0            0        0         0         0       1
  0            3        0         0         1       2
  2            1        3         0         2       3
  0            0        0         0         0       1
  0            1        0         0         0       0

I am newbie and hence unable to figure out where I am getting wrong, I want to print the whole of table?

It looks like you are not writing the index of the dataframe to the word document as you are just iterating over the values of the dataframe. The index values can be accessed by the `df.index` attribute. — FChm, Feb 05 '19 at 10:23
sorry, I forgot to mention it is a groupby table, not a pivoted one. @FChm Does it holds true now? I guess I am writing the whole of table, need not use the df.index. Please suggest. — sudoCoder, Feb 05 '19 at 10:31
If you grouped your original df by the Change Type Component column, this will now be your index and I think my answer (below) will work! You could your print `df.index` and check what these values are. — FChm, Feb 05 '19 at 10:46

score 1 · Accepted Answer · edited Feb 06 '19 at 08:57

1

It looks like you are not writing the index values of the dataframe to file. I have amended your script to show you can use df.index to access these and then write them to the first column.

#...

t = doc.add_table(df.shape[0]+2, df.shape[1]+1) #df.shape[1]+1 to add one extra column for row names

for j in range(df.shape[-1]):
    t.cell(0,j+1).text = df.columns[j] 

for i in range(df.shape[0]):
    for j in range(df.shape[-1]):
        t.cell(i+2,j+1).text = str(df.values[i,j])

# write columns name to file
t.cell(0,0).text = df.columns.name

# write the index values to file
t.cell(1,0).text = df.index.name

for i, my_index in enumerate(df.index):
     t.cell(i+2,0).text = my_index

edited Feb 06 '19 at 08:57

sudoCoder

115
2
13

answered Feb 05 '19 at 10:43

FChm

2,515
1
17
37

Certainly, it does not works, giving very ambiguous results. – sudoCoder Feb 05 '19 at 11:18
Just to mention, I am using : df = df_old.groupby(['Component', 'Change Type']).size().unstack(fill_value= '0') – sudoCoder Feb 05 '19 at 11:19
Ok I understand. `df.columns.name = 'Change Type'` and `df.index.name = 'Component'` for your grouped df. I still think my answer is nearly correct with the edits. The only thing it doesn't write to file is the columns name. – FChm Feb 05 '19 at 11:37
The answer should now work, although the shape of the table may be incorrect - please check this. – FChm Feb 05 '19 at 11:45
This is going wrong somewhere, adding a lot of extra blank rows and columns. Let me figure it out, I am onto it. @FChm – sudoCoder Feb 05 '19 at 12:11
Sorry for taking a while, I was making a typo, also there is some problem to display last column from your answer code. I am updating the same. – sudoCoder Feb 06 '19 at 08:34
No problem, If it works and you are happy with the answer please accept the answer! – FChm Feb 06 '19 at 08:58

How to write groupby grouped table from dataFrame to word document?

1 Answers1

Linked