Write dictionary values to excel column B with corresponding dictionary keys in column A
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a dictionary formatted similarly to the following (the actual dictionary is quite long):
myDictionary = {'Rent' : ['+100'], 'Other Income' : ['+10+20']}
I also have an excel file with the keys copied into column A.
What I am trying to do is iterate through the column of my excel workbook to find matching keys, and paste their corresponding values into column B of the same workbook.
Below is the snippet of code I have gotten closest with:
for row in destinationSheet.iter_rows(min_row=1, max_col=1):
for cell in row:
for key, value in myDictionary.items():
if cell.value == key:
for row in destinationSheet.iter_rows(min_row=1, min_col=2):
for cell in row:
cell.value = str(value)
The problem I am running into is that my code is placing the value of the final entry in my dictionary in every row in column B. So my output looks like:
Column A Column B
Rent ['+10+20']
Other Income ['+10+20']
I'd also like to strip off the brackets and apostrophes when I export to excel so that excel will retain the formula, but make the calculation, but I am trying to tackle my project one step at a time.
Any help would be greatly appreciated. I'm very new to this, so please be kind!
python excel dictionary openpyxl
edited Nov 24 '18 at 19:05
Ron Rosenfeld
24.1k41641
asked Nov 24 '18 at 3:03
SmockrunSmockrun
133
add a comment |
I have a dictionary formatted similarly to the following (the actual dictionary is quite long):
myDictionary = {'Rent' : ['+100'], 'Other Income' : ['+10+20']}
I also have an excel file with the keys copied into column A.
What I am trying to do is iterate through the column of my excel workbook to find matching keys, and paste their corresponding values into column B of the same workbook.
Below is the snippet of code I have gotten closest with:
for row in destinationSheet.iter_rows(min_row=1, max_col=1):
for cell in row:
for key, value in myDictionary.items():
if cell.value == key:
for row in destinationSheet.iter_rows(min_row=1, min_col=2):
for cell in row:
cell.value = str(value)
The problem I am running into is that my code is placing the value of the final entry in my dictionary in every row in column B. So my output looks like:
Column A Column B
Rent ['+10+20']
Other Income ['+10+20']
I'd also like to strip off the brackets and apostrophes when I export to excel so that excel will retain the formula, but make the calculation, but I am trying to tackle my project one step at a time.
Any help would be greatly appreciated. I'm very new to this, so please be kind!
python excel dictionary openpyxl
edited Nov 24 '18 at 19:05
Ron Rosenfeld
24.1k41641
asked Nov 24 '18 at 3:03
SmockrunSmockrun
133
Hey, where are you getting the data you want to write from? How did it end up in the form['+10+20']? Wondering if we can save some steps here.
– cpage
Nov 24 '18 at 4:11
For example, do you want the formulas in column B to edited or would you be fine with the resulting values being written to column B (i.e. ="+10 + 20" or =30).
– cpage
Nov 24 '18 at 4:19
You need search-a-string-in-a-column-and-return-another-column-value-from-that-sa and using-formulae
– stovfl
Nov 24 '18 at 9:31
Usezipto loop over the rows and the dictionary.
– Charlie Clark
Nov 26 '18 at 10:15
add a comment |
I have a dictionary formatted similarly to the following (the actual dictionary is quite long):
myDictionary = {'Rent' : ['+100'], 'Other Income' : ['+10+20']}
I also have an excel file with the keys copied into column A.
What I am trying to do is iterate through the column of my excel workbook to find matching keys, and paste their corresponding values into column B of the same workbook.
Below is the snippet of code I have gotten closest with:
for row in destinationSheet.iter_rows(min_row=1, max_col=1):
for cell in row:
for key, value in myDictionary.items():
if cell.value == key:
for row in destinationSheet.iter_rows(min_row=1, min_col=2):
for cell in row:
cell.value = str(value)
The problem I am running into is that my code is placing the value of the final entry in my dictionary in every row in column B. So my output looks like:
Column A Column B
Rent ['+10+20']
Other Income ['+10+20']
I'd also like to strip off the brackets and apostrophes when I export to excel so that excel will retain the formula, but make the calculation, but I am trying to tackle my project one step at a time.
Any help would be greatly appreciated. I'm very new to this, so please be kind!
python excel dictionary openpyxl
edited Nov 24 '18 at 19:05
Ron Rosenfeld
24.1k41641
asked Nov 24 '18 at 3:03
SmockrunSmockrun
133
I have a dictionary formatted similarly to the following (the actual dictionary is quite long):
myDictionary = {'Rent' : ['+100'], 'Other Income' : ['+10+20']}
I also have an excel file with the keys copied into column A.
What I am trying to do is iterate through the column of my excel workbook to find matching keys, and paste their corresponding values into column B of the same workbook.
Below is the snippet of code I have gotten closest with:
for row in destinationSheet.iter_rows(min_row=1, max_col=1):
for cell in row:
for key, value in myDictionary.items():
if cell.value == key:
for row in destinationSheet.iter_rows(min_row=1, min_col=2):
for cell in row:
cell.value = str(value)
The problem I am running into is that my code is placing the value of the final entry in my dictionary in every row in column B. So my output looks like:
Column A Column B
Rent ['+10+20']
Other Income ['+10+20']
I'd also like to strip off the brackets and apostrophes when I export to excel so that excel will retain the formula, but make the calculation, but I am trying to tackle my project one step at a time.
Any help would be greatly appreciated. I'm very new to this, so please be kind!
python excel dictionary openpyxl
python excel dictionary openpyxl
edited Nov 24 '18 at 19:05
Ron Rosenfeld
24.1k41641
asked Nov 24 '18 at 3:03
SmockrunSmockrun
133
edited Nov 24 '18 at 19:05
Ron Rosenfeld
24.1k41641
asked Nov 24 '18 at 3:03
SmockrunSmockrun
133
edited Nov 24 '18 at 19:05
Ron Rosenfeld
24.1k41641
edited Nov 24 '18 at 19:05
Ron Rosenfeld
24.1k41641
edited Nov 24 '18 at 19:05
Ron Rosenfeld
24.1k41641
24.1k41641
asked Nov 24 '18 at 3:03
SmockrunSmockrun
133
asked Nov 24 '18 at 3:03
SmockrunSmockrun
133
asked Nov 24 '18 at 3:03
SmockrunSmockrun
133
133
Hey, where are you getting the data you want to write from? How did it end up in the form['+10+20']? Wondering if we can save some steps here.
– cpage
Nov 24 '18 at 4:11
For example, do you want the formulas in column B to edited or would you be fine with the resulting values being written to column B (i.e. ="+10 + 20" or =30).
– cpage
Nov 24 '18 at 4:19
You need search-a-string-in-a-column-and-return-another-column-value-from-that-sa and using-formulae
– stovfl
Nov 24 '18 at 9:31
Usezipto loop over the rows and the dictionary.
– Charlie Clark
Nov 26 '18 at 10:15
add a comment |
Hey, where are you getting the data you want to write from? How did it end up in the form['+10+20']? Wondering if we can save some steps here.
– cpage
Nov 24 '18 at 4:11
For example, do you want the formulas in column B to edited or would you be fine with the resulting values being written to column B (i.e. ="+10 + 20" or =30).
– cpage
Nov 24 '18 at 4:19
You need search-a-string-in-a-column-and-return-another-column-value-from-that-sa and using-formulae
– stovfl
Nov 24 '18 at 9:31
Usezipto loop over the rows and the dictionary.
– Charlie Clark
Nov 26 '18 at 10:15
Hey, where are you getting the data you want to write from? How did it end up in the form
['+10+20']? Wondering if we can save some steps here.– cpage
Nov 24 '18 at 4:11
Hey, where are you getting the data you want to write from? How did it end up in the form
['+10+20']? Wondering if we can save some steps here.– cpage
Nov 24 '18 at 4:11
For example, do you want the formulas in column B to edited or would you be fine with the resulting values being written to column B (i.e. ="+10 + 20" or =30).
– cpage
Nov 24 '18 at 4:19
For example, do you want the formulas in column B to edited or would you be fine with the resulting values being written to column B (i.e. ="+10 + 20" or =30).
– cpage
Nov 24 '18 at 4:19
You need search-a-string-in-a-column-and-return-another-column-value-from-that-sa and using-formulae
– stovfl
Nov 24 '18 at 9:31
You need search-a-string-in-a-column-and-return-another-column-value-from-that-sa and using-formulae
– stovfl
Nov 24 '18 at 9:31
Use
zip to loop over the rows and the dictionary.– Charlie Clark
Nov 26 '18 at 10:15
Use
zip to loop over the rows and the dictionary.– Charlie Clark
Nov 26 '18 at 10:15
add a comment |
0
active
oldest
votes
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',
autoActivateHeartbeat: false,
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53454814%2fwrite-dictionary-values-to-excel-column-b-with-corresponding-dictionary-keys-in%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53454814%2fwrite-dictionary-values-to-excel-column-b-with-corresponding-dictionary-keys-in%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Hey, where are you getting the data you want to write from? How did it end up in the form
['+10+20']? Wondering if we can save some steps here.– cpage
Nov 24 '18 at 4:11
For example, do you want the formulas in column B to edited or would you be fine with the resulting values being written to column B (i.e. ="+10 + 20" or =30).
– cpage
Nov 24 '18 at 4:19
You need search-a-string-in-a-column-and-return-another-column-value-from-that-sa and using-formulae
– stovfl
Nov 24 '18 at 9:31
Use
zipto loop over the rows and the dictionary.– Charlie Clark
Nov 26 '18 at 10:15