Search code examples
pythonpandasdocxpython-docx

Python [Pandas/docx]: Merging two rows based on common name

I am trying to write a script using docx-python and pandas in Python3 which perform following action:

  1. Take input from csv file
  2. Merge common value of Column C and add each value into docx
  3. Export docx

My raw csv is as below:

SN. Name    Instance    Severity
1   Name1   file1:line1 CRITICAL
2   Name1   file2:line3 CRITICAL
3   Name2   file1:line1 Low
4   Name2   file1:line3 Low
and so on...

and i want my docx outpur as: ` [1]: https://i.sstatic.net/1xNc0.png

I am not able to figure it out how can i filter "Instances" based on "Name" using pandas and later print then into docx.

Thanks in advance.

Solution

  • Below code will select the relevant columns,group by based on 'Name' and 'Severity' and add Instances together

    df2 = df[["Name","Instance","Severity"]]
df2["Instance"] = df2.groupby(['Name','Severity'])['Instance'].transform(lambda x: '\n'.join(x))

    Finally, remove the duplicates and transform to get the desired output

    df2 = df2.drop_duplicates()
df2 = df2.T