Trying to compare the contents of two excel files and save the difference by python

up vote
0
down vote

favorite

I have two excel files containing multiple lines of excel from a datalogger, and I need to compare the two files with 3 similar columns (anum,bnum,date,time) but with different column durations, and then save the difference into a third excel file.

***excel file 1:

anum            bnum duration   date     time

02473082424 0969755655  12  2018-08-04  10:53:04

02473082424 02435543470 17  2018-08-04  10:53:04

02473082424 01653559999 19  2018-08-04  10:53:06

02473082424 02437633476 63  2018-08-04  10:52:46

02473082424 02432262638 23  2018-08-04  10:53:26

02473082424 02435537928 40  2018-08-04  10:53:18

02473082424 0936467084  20  2018-08-04  10:53:42

***excel file 2:

   anum       bnum   duration   date     time

    02473082424 0969755655  16  2018-08-04  10:53:04

    02473082424 02435543470 17  2018-08-04  10:53:04

    02473082424 01653559999 23  2018-08-04  10:53:06

    02473082424 02437633476 63  2018-08-04  10:52:46

    02473082424 02432262638 23  2018-08-04  10:53:26

    02473082424 02435537928 10  2018-08-04  10:53:18

    02473082424 0936467084  20  2018-08-04  10:53:42

edited Nov 8 at 3:52

Killerrabbit

335

asked Nov 8 at 2:56

Phong Doan

add a comment |

up vote
0
down vote

favorite

***excel file 1:

anum            bnum duration   date     time

02473082424 0969755655  12  2018-08-04  10:53:04

02473082424 02435543470 17  2018-08-04  10:53:04

02473082424 01653559999 19  2018-08-04  10:53:06

02473082424 02437633476 63  2018-08-04  10:52:46

02473082424 02432262638 23  2018-08-04  10:53:26

02473082424 02435537928 40  2018-08-04  10:53:18

02473082424 0936467084  20  2018-08-04  10:53:42

***excel file 2:

   anum       bnum   duration   date     time

    02473082424 0969755655  16  2018-08-04  10:53:04

    02473082424 02435543470 17  2018-08-04  10:53:04

    02473082424 01653559999 23  2018-08-04  10:53:06

    02473082424 02437633476 63  2018-08-04  10:52:46

    02473082424 02432262638 23  2018-08-04  10:53:26

    02473082424 02435537928 10  2018-08-04  10:53:18

    02473082424 0936467084  20  2018-08-04  10:53:42

edited Nov 8 at 3:52

Killerrabbit

335

asked Nov 8 at 2:56

Phong Doan

add a comment |

up vote
0
down vote

favorite

***excel file 1:

anum            bnum duration   date     time

02473082424 0969755655  12  2018-08-04  10:53:04

02473082424 02435543470 17  2018-08-04  10:53:04

02473082424 01653559999 19  2018-08-04  10:53:06

02473082424 02437633476 63  2018-08-04  10:52:46

02473082424 02432262638 23  2018-08-04  10:53:26

02473082424 02435537928 40  2018-08-04  10:53:18

02473082424 0936467084  20  2018-08-04  10:53:42

***excel file 2:

   anum       bnum   duration   date     time

    02473082424 0969755655  16  2018-08-04  10:53:04

    02473082424 02435543470 17  2018-08-04  10:53:04

    02473082424 01653559999 23  2018-08-04  10:53:06

    02473082424 02437633476 63  2018-08-04  10:52:46

    02473082424 02432262638 23  2018-08-04  10:53:26

    02473082424 02435537928 10  2018-08-04  10:53:18

    02473082424 0936467084  20  2018-08-04  10:53:42

edited Nov 8 at 3:52

Killerrabbit

335

asked Nov 8 at 2:56

Phong Doan

***excel file 1:

anum            bnum duration   date     time

02473082424 0969755655  12  2018-08-04  10:53:04

02473082424 02435543470 17  2018-08-04  10:53:04

02473082424 01653559999 19  2018-08-04  10:53:06

02473082424 02437633476 63  2018-08-04  10:52:46

02473082424 02432262638 23  2018-08-04  10:53:26

02473082424 02435537928 40  2018-08-04  10:53:18

02473082424 0936467084  20  2018-08-04  10:53:42

***excel file 2:

   anum       bnum   duration   date     time

    02473082424 0969755655  16  2018-08-04  10:53:04

    02473082424 02435543470 17  2018-08-04  10:53:04

    02473082424 01653559999 23  2018-08-04  10:53:06

    02473082424 02437633476 63  2018-08-04  10:52:46

    02473082424 02432262638 23  2018-08-04  10:53:26

    02473082424 02435537928 10  2018-08-04  10:53:18

    02473082424 0936467084  20  2018-08-04  10:53:42

excel pandas compare difference difflib

edited Nov 8 at 3:52

Killerrabbit

335

asked Nov 8 at 2:56

Phong Doan

edited Nov 8 at 3:52

Killerrabbit

335

asked Nov 8 at 2:56

Phong Doan

edited Nov 8 at 3:52

Killerrabbit

335

edited Nov 8 at 3:52

Killerrabbit

335

edited Nov 8 at 3:52

Killerrabbit

335

asked Nov 8 at 2:56

Phong Doan

asked Nov 8 at 2:56

Phong Doan

asked Nov 8 at 2:56

Phong Doan

add a comment |

1 Answer
1

active

oldest

votes

up vote
0
down vote

You can first read both excel files using pandas.read_excel
into two dataframes df1 and df2 Then :

df1.rename(columns={'duration':'duration1'},inplace=True)

df2.rename(columns={'duration':'duration2'},inplace=True)

df=df1.merge(df2)

df['duration']=df['duration2']-df['duration1']

writer = pd.ExcelWriter(excel_file_3)

df[['anum','bnum','duration','date','time]].to_excel(writer,'Sheet1')

answered Nov 8 at 13:25

ahmed2512

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53200908%2ftrying-to-compare-the-contents-of-two-excel-files-and-save-the-difference-by-pyt%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
0
down vote

You can first read both excel files using pandas.read_excel
into two dataframes df1 and df2 Then :

df1.rename(columns={'duration':'duration1'},inplace=True)

df2.rename(columns={'duration':'duration2'},inplace=True)

df=df1.merge(df2)

df['duration']=df['duration2']-df['duration1']

writer = pd.ExcelWriter(excel_file_3)

df[['anum','bnum','duration','date','time]].to_excel(writer,'Sheet1')

answered Nov 8 at 13:25

ahmed2512

add a comment |

up vote
0
down vote

You can first read both excel files using pandas.read_excel
into two dataframes df1 and df2 Then :

df1.rename(columns={'duration':'duration1'},inplace=True)

df2.rename(columns={'duration':'duration2'},inplace=True)

df=df1.merge(df2)

df['duration']=df['duration2']-df['duration1']

writer = pd.ExcelWriter(excel_file_3)

df[['anum','bnum','duration','date','time]].to_excel(writer,'Sheet1')

answered Nov 8 at 13:25

ahmed2512

add a comment |

up vote
0
down vote

You can first read both excel files using pandas.read_excel
into two dataframes df1 and df2 Then :

df1.rename(columns={'duration':'duration1'},inplace=True)

df2.rename(columns={'duration':'duration2'},inplace=True)

df=df1.merge(df2)

df['duration']=df['duration2']-df['duration1']

writer = pd.ExcelWriter(excel_file_3)

df[['anum','bnum','duration','date','time]].to_excel(writer,'Sheet1')

answered Nov 8 at 13:25

ahmed2512

You can first read both excel files using pandas.read_excel
into two dataframes df1 and df2 Then :

df1.rename(columns={'duration':'duration1'},inplace=True)

df2.rename(columns={'duration':'duration2'},inplace=True)

df=df1.merge(df2)

df['duration']=df['duration2']-df['duration1']

writer = pd.ExcelWriter(excel_file_3)

df[['anum','bnum','duration','date','time]].to_excel(writer,'Sheet1')

answered Nov 8 at 13:25

ahmed2512

answered Nov 8 at 13:25

ahmed2512

answered Nov 8 at 13:25

ahmed2512

answered Nov 8 at 13:25

ahmed2512

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Wsrtjtyk