How to copy multiple columns to single column in excel using python script

user_13268852 0
Viewed 65

I am new to python. I would like to copy multiple columns to single column in a excel using python. And in that single column first heading and then value if the value is not present the that column should not copy.

Example Excel data:

Column A Column B Column C Column D
One Two Three Four
Five Six Seven
xyz hjy abc

Excepted Result:

Column A Column B Column C Column D Column E
One Two Three Column A :One, Column B:Two, Column C:Three
Five Six Seven Column A :Five, Column C:Six, Column D:Seven
xyz hjy abc Column A :xyz, Column B:hjy, Column D:abc

Example and Expected result image

Kindly help to resolve the issue. Thank you.

python-3.x excel python
2 Answers

You can use two for loops, the outer one being for the rows and the inner one for the columns, and for each cell then check whether the cell.value!=null, if not you can add the value to a string and then when you reach the final element of the inner loop you can add the particular content to the respective last column.

user_196084370

it's not quite clear what you expect to get, but with this you'll get something like this:

import pandas as pd

df = pd.read_excel('file.xlsx')
df['col_E'] = df.apply(lambda x: ','.join(f'{k}:{v}' for k,v in x.dropna().to_dict().items()),1)
df.to_excel('new_file.xlsx',index=False)

new_file.xlsx enter image description here

upd

selecting columns to read from excel file:

cols = ['Column A', 'Column C', 'Column D']  # columns to read

df = pd.read_excel('file.xlsx', usecols=cols)
#                               ^^^^^^^^^^^^
df['col_E'] = df.apply(lambda x: ','.join(f'{k}:{v}' for k,v in x.dropna().to_dict().items()),1)
df.to_excel('new_file.xlsx',index=False)

new_file.xlsx

enter image description here

user_183445120
Related