Python: Copy and pasting to specific row and column

Question

I have 2 input files and 1 main file in excel. I want to use python to copy values from the input file and paste into the main file of the corresponding row and column as such:

Input file 1 (COPIED)
Col Row extra1 extra2 value

A    70                6.1
B    87                5.0
G    90                4.4

Input file 2 (COPIED)
Col Row extra1 extra2 value

C    10                0.9
F    17                3.3
G    20                1.1

Main file (PASTED)
Col Row value
A   70   6.1
B   87   5.0 
C   10   0.9
F   17   3.3
G   20   1.1
G   90   4.4

The row and col columns are already filled in the first place. Anyone has any solutions or insights into how to go about doing this? thank you in advance

Manoj · Accepted Answer · 2020-11-22 12:47:50Z

1

Copying columns between two different workbooks using openpyxl could be done as follows:

import openpyxl

wb1 = openpyxl.load_workbook('B.xlsx')
ws1 = wb1.active

wb2 = openpyxl.load_workbook('E.xlsx')
ws2 = wb2.active

for src, dst in zip(ws1['B:B'], ws2['AN:AN']):
    dst.value = src.value

wb2.save('E.xlsx')

For a range of columns, the following would work:

import openpyxl

wb1 = openpyxl.load_workbook('B.xlsx')
ws1 = wb1.active

wb2 = openpyxl.load_workbook('E.xlsx')
ws2 = wb2.active

for src, dst in zip(ws1['A:I'], ws2['AN:AV']):
    for cell_src, cell_dst in zip(src, dst):
        cell_dst.value = cell_src.value

wb2.save('E.xlsx')

answered Nov 22, 2020 at 12:47

Manoj

3292 gold badges4 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gilf0yle · Accepted Answer · 2020-11-23 08:18:47Z

1

if your main file is empyty

df1=pd.DataFrame({"Col":[1,2,3],"Row":[2,3,4],"value":[6,5,7],"ree":[0,0,0]})


    Col Row value ree
0   1   2   6   0
1   2   3   5   0
2   3   4   7   0

df2=pd.DataFrame({"Col":[9,8,0],"Row":[6,4,22],"value":[26,55,27]})


    Col Row value ree
0   1   2   6   0
1   2   3   5   0
2   3   4   7   0

df1=df1[["Col","Row","value"]]
df2=df2[["Col","Row","value"]]

main=pd.concat((df1,df2))

    Col Row value
0   1   2   6
1   2   3   5
2   3   4   7
0   9   6   26
1   8   4   55
2   0   22  27

so after this you can create a main file or save it in the existing csv this works if main is empty if its filled then concat as done above.

edited Nov 23, 2020 at 8:18

answered Nov 22, 2020 at 15:45

gilf0yle

1,1026 silver badges13 bronze badges

Collectives™ on Stack Overflow

Python: Copy and pasting to specific row and column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related