Transferring data from excel to word in python 3
I'm attempting to write a script that allows me to read data from an input excel file (saved in .csv format because someone told me to do it that way), and write selected portions of this data to a word document.
It is a script to create personalised delivery notes for participants' meal choices (the choices are cumulatively added to an input spreadsheet).
So far I have created a dummy input spreadsheet, and saved a blank dummy output word file (dummy.csv and dummy.txt, respectively).
The code I have written so far reads the spreadsheet into the terminal, with some formatting to tidy it up.
import csv
f = open("dummy.csv")
csv_f = csv.reader(f)
for row in csv_f:
print('{:<15} {:<15} {:<20} {:<25}'.format(*row))
The output looks like this: (Dummy meal choices kept the same for ease)
Participant ID Breakfasts Lunches/dinners Snacks
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
My next challenge is to somehow write this data to a word file for participant 1111, another for participant 2222, and so on. I don't want the script to necessarily write the exact data from these rows to the word file, but rather whatever data could be on these rows should the food choices in the input file be different.
It would be good to keep the meals split into Breakfasts, Lunches/dinners, and Snacks on the output delivery note.
I can tidy up font etc later, I just want the meal selections to be there for now.
I'll also want to have it say "7 x Full english", rather than "Full english, Full english, Full english etc."
Thank you for reading, any help would be hugely appreciated!
Kieran
python excel python-3.x csv file-handling
asked Nov 20 '18 at 12:32
KieranKieran
11
add a comment |
I'm attempting to write a script that allows me to read data from an input excel file (saved in .csv format because someone told me to do it that way), and write selected portions of this data to a word document.
It is a script to create personalised delivery notes for participants' meal choices (the choices are cumulatively added to an input spreadsheet).
So far I have created a dummy input spreadsheet, and saved a blank dummy output word file (dummy.csv and dummy.txt, respectively).
The code I have written so far reads the spreadsheet into the terminal, with some formatting to tidy it up.
import csv
f = open("dummy.csv")
csv_f = csv.reader(f)
for row in csv_f:
print('{:<15} {:<15} {:<20} {:<25}'.format(*row))
The output looks like this: (Dummy meal choices kept the same for ease)
Participant ID Breakfasts Lunches/dinners Snacks
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
My next challenge is to somehow write this data to a word file for participant 1111, another for participant 2222, and so on. I don't want the script to necessarily write the exact data from these rows to the word file, but rather whatever data could be on these rows should the food choices in the input file be different.
It would be good to keep the meals split into Breakfasts, Lunches/dinners, and Snacks on the output delivery note.
I can tidy up font etc later, I just want the meal selections to be there for now.
I'll also want to have it say "7 x Full english", rather than "Full english, Full english, Full english etc."
Thank you for reading, any help would be hugely appreciated!
Kieran
python excel python-3.x csv file-handling
asked Nov 20 '18 at 12:32
KieranKieran
11
If you want to read in csv-files but then also make some calculations like counts as you stated, I'd recommend usingpandas. With that also separate write-outs of sub groups into different files is no problem. But honestly, up to now I did not really understand what exactly you want to do....
– SpghttCd
Nov 20 '18 at 12:37
Thanks for your response - I really am extremely new to this so pardon me for not being clear. I am attempting to have a python script that will, when executed, open a master spreadsheet (into which the meal choices of all participants for that week have been entered), select all of participant 1111's choices and output a delivery note listing all of the meals to be delivered to participant 1111 that week. The output will have Participant ID (1111), Breakfasts: 7 x Full english, Lunches/Dinners: 14 x Risotto, and so on. Then, the same task repeated for all participants.
– Kieran
Nov 20 '18 at 12:45
No reason to pardon - I think you really should have a closer look at pandas. It's already a very powerful tool for data analysis and is actively developedon. And if I look at your profile, I think you'll be happy to use it in the future again and again...
– SpghttCd
Nov 20 '18 at 12:55
Thanks very much, I'll look into it!
– Kieran
Nov 20 '18 at 13:05
add a comment |
I'm attempting to write a script that allows me to read data from an input excel file (saved in .csv format because someone told me to do it that way), and write selected portions of this data to a word document.
It is a script to create personalised delivery notes for participants' meal choices (the choices are cumulatively added to an input spreadsheet).
So far I have created a dummy input spreadsheet, and saved a blank dummy output word file (dummy.csv and dummy.txt, respectively).
The code I have written so far reads the spreadsheet into the terminal, with some formatting to tidy it up.
import csv
f = open("dummy.csv")
csv_f = csv.reader(f)
for row in csv_f:
print('{:<15} {:<15} {:<20} {:<25}'.format(*row))
The output looks like this: (Dummy meal choices kept the same for ease)
Participant ID Breakfasts Lunches/dinners Snacks
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
My next challenge is to somehow write this data to a word file for participant 1111, another for participant 2222, and so on. I don't want the script to necessarily write the exact data from these rows to the word file, but rather whatever data could be on these rows should the food choices in the input file be different.
It would be good to keep the meals split into Breakfasts, Lunches/dinners, and Snacks on the output delivery note.
I can tidy up font etc later, I just want the meal selections to be there for now.
I'll also want to have it say "7 x Full english", rather than "Full english, Full english, Full english etc."
Thank you for reading, any help would be hugely appreciated!
Kieran
python excel python-3.x csv file-handling
asked Nov 20 '18 at 12:32
KieranKieran
11
I'm attempting to write a script that allows me to read data from an input excel file (saved in .csv format because someone told me to do it that way), and write selected portions of this data to a word document.
It is a script to create personalised delivery notes for participants' meal choices (the choices are cumulatively added to an input spreadsheet).
So far I have created a dummy input spreadsheet, and saved a blank dummy output word file (dummy.csv and dummy.txt, respectively).
The code I have written so far reads the spreadsheet into the terminal, with some formatting to tidy it up.
import csv
f = open("dummy.csv")
csv_f = csv.reader(f)
for row in csv_f:
print('{:<15} {:<15} {:<20} {:<25}'.format(*row))
The output looks like this: (Dummy meal choices kept the same for ease)
Participant ID Breakfasts Lunches/dinners Snacks
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Full english Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
1111 Risotto Granola
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Avocado toast Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
2222 Bean chilli Apple
My next challenge is to somehow write this data to a word file for participant 1111, another for participant 2222, and so on. I don't want the script to necessarily write the exact data from these rows to the word file, but rather whatever data could be on these rows should the food choices in the input file be different.
It would be good to keep the meals split into Breakfasts, Lunches/dinners, and Snacks on the output delivery note.
I can tidy up font etc later, I just want the meal selections to be there for now.
I'll also want to have it say "7 x Full english", rather than "Full english, Full english, Full english etc."
Thank you for reading, any help would be hugely appreciated!
Kieran
python excel python-3.x csv file-handling
python excel python-3.x csv file-handling
asked Nov 20 '18 at 12:32
KieranKieran
11
asked Nov 20 '18 at 12:32
KieranKieran
11
asked Nov 20 '18 at 12:32
KieranKieran
11
asked Nov 20 '18 at 12:32
KieranKieran
11
asked Nov 20 '18 at 12:32
KieranKieran
11
11
If you want to read in csv-files but then also make some calculations like counts as you stated, I'd recommend usingpandas. With that also separate write-outs of sub groups into different files is no problem. But honestly, up to now I did not really understand what exactly you want to do....
– SpghttCd
Nov 20 '18 at 12:37
Thanks for your response - I really am extremely new to this so pardon me for not being clear. I am attempting to have a python script that will, when executed, open a master spreadsheet (into which the meal choices of all participants for that week have been entered), select all of participant 1111's choices and output a delivery note listing all of the meals to be delivered to participant 1111 that week. The output will have Participant ID (1111), Breakfasts: 7 x Full english, Lunches/Dinners: 14 x Risotto, and so on. Then, the same task repeated for all participants.
– Kieran
Nov 20 '18 at 12:45
No reason to pardon - I think you really should have a closer look at pandas. It's already a very powerful tool for data analysis and is actively developedon. And if I look at your profile, I think you'll be happy to use it in the future again and again...
– SpghttCd
Nov 20 '18 at 12:55
Thanks very much, I'll look into it!
– Kieran
Nov 20 '18 at 13:05
add a comment |
If you want to read in csv-files but then also make some calculations like counts as you stated, I'd recommend usingpandas. With that also separate write-outs of sub groups into different files is no problem. But honestly, up to now I did not really understand what exactly you want to do....
– SpghttCd
Nov 20 '18 at 12:37
Thanks for your response - I really am extremely new to this so pardon me for not being clear. I am attempting to have a python script that will, when executed, open a master spreadsheet (into which the meal choices of all participants for that week have been entered), select all of participant 1111's choices and output a delivery note listing all of the meals to be delivered to participant 1111 that week. The output will have Participant ID (1111), Breakfasts: 7 x Full english, Lunches/Dinners: 14 x Risotto, and so on. Then, the same task repeated for all participants.
– Kieran
Nov 20 '18 at 12:45
No reason to pardon - I think you really should have a closer look at pandas. It's already a very powerful tool for data analysis and is actively developedon. And if I look at your profile, I think you'll be happy to use it in the future again and again...
– SpghttCd
Nov 20 '18 at 12:55
Thanks very much, I'll look into it!
– Kieran
Nov 20 '18 at 13:05
If you want to read in csv-files but then also make some calculations like counts as you stated, I'd recommend using
pandas. With that also separate write-outs of sub groups into different files is no problem. But honestly, up to now I did not really understand what exactly you want to do....– SpghttCd
Nov 20 '18 at 12:37
If you want to read in csv-files but then also make some calculations like counts as you stated, I'd recommend using
pandas. With that also separate write-outs of sub groups into different files is no problem. But honestly, up to now I did not really understand what exactly you want to do....– SpghttCd
Nov 20 '18 at 12:37
Thanks for your response - I really am extremely new to this so pardon me for not being clear. I am attempting to have a python script that will, when executed, open a master spreadsheet (into which the meal choices of all participants for that week have been entered), select all of participant 1111's choices and output a delivery note listing all of the meals to be delivered to participant 1111 that week. The output will have Participant ID (1111), Breakfasts: 7 x Full english, Lunches/Dinners: 14 x Risotto, and so on. Then, the same task repeated for all participants.
– Kieran
Nov 20 '18 at 12:45
Thanks for your response - I really am extremely new to this so pardon me for not being clear. I am attempting to have a python script that will, when executed, open a master spreadsheet (into which the meal choices of all participants for that week have been entered), select all of participant 1111's choices and output a delivery note listing all of the meals to be delivered to participant 1111 that week. The output will have Participant ID (1111), Breakfasts: 7 x Full english, Lunches/Dinners: 14 x Risotto, and so on. Then, the same task repeated for all participants.
– Kieran
Nov 20 '18 at 12:45
No reason to pardon - I think you really should have a closer look at pandas. It's already a very powerful tool for data analysis and is actively developedon. And if I look at your profile, I think you'll be happy to use it in the future again and again...
– SpghttCd
Nov 20 '18 at 12:55
No reason to pardon - I think you really should have a closer look at pandas. It's already a very powerful tool for data analysis and is actively developedon. And if I look at your profile, I think you'll be happy to use it in the future again and again...
– SpghttCd
Nov 20 '18 at 12:55
Thanks very much, I'll look into it!
– Kieran
Nov 20 '18 at 13:05
Thanks very much, I'll look into it!
– Kieran
Nov 20 '18 at 13:05
add a comment |
1 Answer
1
active
oldest
votes
Just to show where you exemplary could go using pandas:
import pandas as pd
df = pd.read_csv('whereverfilemayroam/filename')
Participant ID Breakfasts Lunches/dinners Snacks
0 1111 Full english Risotto Granola
1 1111 Full english Risotto Granola
2 1111 Full english Risotto Granola
3 1111 Full english Risotto Granola
4 1111 Full english Risotto Granola
5 1111 Full english Risotto Granola
6 1111 Full english Risotto Granola
7 1111 None Risotto Granola
8 1111 None Risotto Granola
9 1111 None Risotto Granola
10 1111 None Risotto Granola
11 1111 None Risotto Granola
12 1111 None Risotto Granola
13 1111 None Risotto Granola
14 2222 Avocado toast Bean chilli Apple
15 2222 Avocado toast Bean chilli Apple
16 2222 Avocado toast Bean chilli Apple
17 2222 Avocado toast Bean chilli Apple
18 2222 Avocado toast Bean chilli Apple
19 2222 Avocado toast Bean chilli Apple
20 2222 Avocado toast Bean chilli Apple
21 2222 None Bean chilli Apple
22 2222 None Bean chilli Apple
23 2222 None Bean chilli Apple
24 2222 None Bean chilli Apple
25 2222 None Bean chilli Apple
26 2222 None Bean chilli Apple
27 2222 None Bean chilli Apple
That's your file in a pandas dataframe, the standard container in pandas, if you like. And now you can make a plenty of statistical things with it... Just dig a little through the docs
Examples:
df.groupby(['Participant ID', 'Breakfasts']).Breakfasts.count()
Participant ID Breakfasts
1111 Full english 7
2222 Avocado toast 7
Name: Breakfasts, dtype: int64
df.groupby(['Participant ID', 'Lunches/dinners'])['Lunches/dinners'].count()
Participant ID Lunches/dinners
1111 Risotto 14
2222 Bean chilli 14
Name: Lunches/dinners, dtype: int64
Of course you can separate by Participant ID:
oneoneoneone = df[df['Participant ID'] == 1111]
oneoneoneone
Participant ID Breakfasts Lunches/dinners Snacks
0 1111 Full english Risotto Granola
1 1111 Full english Risotto Granola
2 1111 Full english Risotto Granola
3 1111 Full english Risotto Granola
4 1111 Full english Risotto Granola
5 1111 Full english Risotto Granola
6 1111 Full english Risotto Granola
7 1111 None Risotto Granola
8 1111 None Risotto Granola
9 1111 None Risotto Granola
10 1111 None Risotto Granola
11 1111 None Risotto Granola
12 1111 None Risotto Granola
13 1111 None Risotto Granola
oneoneoneone.to_csv('target_file')
and so would perhaps
twotwotwotwo.to_csv('another_target_file')
One can also iterate over groups, and then apply always the same operations on each group.
e.g.:
for name, group in df.groupby('Participant ID'):
print(name)
print(group.groupby('Breakfasts').Breakfasts.count().to_string())
print(group.groupby('Lunches/dinners')['Lunches/dinners'].count().to_string())
print(group.groupby('Snacks').Snacks.count().to_string(), 'n')
returns:
1111
Breakfasts
Full english 7
Lunches/dinners
Risotto 14
Snacks
Granola 14
2222
Breakfasts
Avocado toast 7
Lunches/dinners
Bean chilli 14
Snacks
Apple 14
answered Nov 20 '18 at 12:43
SpghttCdSpghttCd
4,8022313
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',
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%2f53393072%2ftransferring-data-from-excel-to-word-in-python-3%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Just to show where you exemplary could go using pandas:
import pandas as pd
df = pd.read_csv('whereverfilemayroam/filename')
Participant ID Breakfasts Lunches/dinners Snacks
0 1111 Full english Risotto Granola
1 1111 Full english Risotto Granola
2 1111 Full english Risotto Granola
3 1111 Full english Risotto Granola
4 1111 Full english Risotto Granola
5 1111 Full english Risotto Granola
6 1111 Full english Risotto Granola
7 1111 None Risotto Granola
8 1111 None Risotto Granola
9 1111 None Risotto Granola
10 1111 None Risotto Granola
11 1111 None Risotto Granola
12 1111 None Risotto Granola
13 1111 None Risotto Granola
14 2222 Avocado toast Bean chilli Apple
15 2222 Avocado toast Bean chilli Apple
16 2222 Avocado toast Bean chilli Apple
17 2222 Avocado toast Bean chilli Apple
18 2222 Avocado toast Bean chilli Apple
19 2222 Avocado toast Bean chilli Apple
20 2222 Avocado toast Bean chilli Apple
21 2222 None Bean chilli Apple
22 2222 None Bean chilli Apple
23 2222 None Bean chilli Apple
24 2222 None Bean chilli Apple
25 2222 None Bean chilli Apple
26 2222 None Bean chilli Apple
27 2222 None Bean chilli Apple
That's your file in a pandas dataframe, the standard container in pandas, if you like. And now you can make a plenty of statistical things with it... Just dig a little through the docs
Examples:
df.groupby(['Participant ID', 'Breakfasts']).Breakfasts.count()
Participant ID Breakfasts
1111 Full english 7
2222 Avocado toast 7
Name: Breakfasts, dtype: int64
df.groupby(['Participant ID', 'Lunches/dinners'])['Lunches/dinners'].count()
Participant ID Lunches/dinners
1111 Risotto 14
2222 Bean chilli 14
Name: Lunches/dinners, dtype: int64
Of course you can separate by Participant ID:
oneoneoneone = df[df['Participant ID'] == 1111]
oneoneoneone
Participant ID Breakfasts Lunches/dinners Snacks
0 1111 Full english Risotto Granola
1 1111 Full english Risotto Granola
2 1111 Full english Risotto Granola
3 1111 Full english Risotto Granola
4 1111 Full english Risotto Granola
5 1111 Full english Risotto Granola
6 1111 Full english Risotto Granola
7 1111 None Risotto Granola
8 1111 None Risotto Granola
9 1111 None Risotto Granola
10 1111 None Risotto Granola
11 1111 None Risotto Granola
12 1111 None Risotto Granola
13 1111 None Risotto Granola
oneoneoneone.to_csv('target_file')
and so would perhaps
twotwotwotwo.to_csv('another_target_file')
One can also iterate over groups, and then apply always the same operations on each group.
e.g.:
for name, group in df.groupby('Participant ID'):
print(name)
print(group.groupby('Breakfasts').Breakfasts.count().to_string())
print(group.groupby('Lunches/dinners')['Lunches/dinners'].count().to_string())
print(group.groupby('Snacks').Snacks.count().to_string(), 'n')
returns:
1111
Breakfasts
Full english 7
Lunches/dinners
Risotto 14
Snacks
Granola 14
2222
Breakfasts
Avocado toast 7
Lunches/dinners
Bean chilli 14
Snacks
Apple 14
answered Nov 20 '18 at 12:43
SpghttCdSpghttCd
4,8022313
add a comment |
Just to show where you exemplary could go using pandas:
import pandas as pd
df = pd.read_csv('whereverfilemayroam/filename')
Participant ID Breakfasts Lunches/dinners Snacks
0 1111 Full english Risotto Granola
1 1111 Full english Risotto Granola
2 1111 Full english Risotto Granola
3 1111 Full english Risotto Granola
4 1111 Full english Risotto Granola
5 1111 Full english Risotto Granola
6 1111 Full english Risotto Granola
7 1111 None Risotto Granola
8 1111 None Risotto Granola
9 1111 None Risotto Granola
10 1111 None Risotto Granola
11 1111 None Risotto Granola
12 1111 None Risotto Granola
13 1111 None Risotto Granola
14 2222 Avocado toast Bean chilli Apple
15 2222 Avocado toast Bean chilli Apple
16 2222 Avocado toast Bean chilli Apple
17 2222 Avocado toast Bean chilli Apple
18 2222 Avocado toast Bean chilli Apple
19 2222 Avocado toast Bean chilli Apple
20 2222 Avocado toast Bean chilli Apple
21 2222 None Bean chilli Apple
22 2222 None Bean chilli Apple
23 2222 None Bean chilli Apple
24 2222 None Bean chilli Apple
25 2222 None Bean chilli Apple
26 2222 None Bean chilli Apple
27 2222 None Bean chilli Apple
That's your file in a pandas dataframe, the standard container in pandas, if you like. And now you can make a plenty of statistical things with it... Just dig a little through the docs
Examples:
df.groupby(['Participant ID', 'Breakfasts']).Breakfasts.count()
Participant ID Breakfasts
1111 Full english 7
2222 Avocado toast 7
Name: Breakfasts, dtype: int64
df.groupby(['Participant ID', 'Lunches/dinners'])['Lunches/dinners'].count()
Participant ID Lunches/dinners
1111 Risotto 14
2222 Bean chilli 14
Name: Lunches/dinners, dtype: int64
Of course you can separate by Participant ID:
oneoneoneone = df[df['Participant ID'] == 1111]
oneoneoneone
Participant ID Breakfasts Lunches/dinners Snacks
0 1111 Full english Risotto Granola
1 1111 Full english Risotto Granola
2 1111 Full english Risotto Granola
3 1111 Full english Risotto Granola
4 1111 Full english Risotto Granola
5 1111 Full english Risotto Granola
6 1111 Full english Risotto Granola
7 1111 None Risotto Granola
8 1111 None Risotto Granola
9 1111 None Risotto Granola
10 1111 None Risotto Granola
11 1111 None Risotto Granola
12 1111 None Risotto Granola
13 1111 None Risotto Granola
oneoneoneone.to_csv('target_file')
and so would perhaps
twotwotwotwo.to_csv('another_target_file')
One can also iterate over groups, and then apply always the same operations on each group.
e.g.:
for name, group in df.groupby('Participant ID'):
print(name)
print(group.groupby('Breakfasts').Breakfasts.count().to_string())
print(group.groupby('Lunches/dinners')['Lunches/dinners'].count().to_string())
print(group.groupby('Snacks').Snacks.count().to_string(), 'n')
returns:
1111
Breakfasts
Full english 7
Lunches/dinners
Risotto 14
Snacks
Granola 14
2222
Breakfasts
Avocado toast 7
Lunches/dinners
Bean chilli 14
Snacks
Apple 14
answered Nov 20 '18 at 12:43
SpghttCdSpghttCd
4,8022313
add a comment |
Just to show where you exemplary could go using pandas:
import pandas as pd
df = pd.read_csv('whereverfilemayroam/filename')
Participant ID Breakfasts Lunches/dinners Snacks
0 1111 Full english Risotto Granola
1 1111 Full english Risotto Granola
2 1111 Full english Risotto Granola
3 1111 Full english Risotto Granola
4 1111 Full english Risotto Granola
5 1111 Full english Risotto Granola
6 1111 Full english Risotto Granola
7 1111 None Risotto Granola
8 1111 None Risotto Granola
9 1111 None Risotto Granola
10 1111 None Risotto Granola
11 1111 None Risotto Granola
12 1111 None Risotto Granola
13 1111 None Risotto Granola
14 2222 Avocado toast Bean chilli Apple
15 2222 Avocado toast Bean chilli Apple
16 2222 Avocado toast Bean chilli Apple
17 2222 Avocado toast Bean chilli Apple
18 2222 Avocado toast Bean chilli Apple
19 2222 Avocado toast Bean chilli Apple
20 2222 Avocado toast Bean chilli Apple
21 2222 None Bean chilli Apple
22 2222 None Bean chilli Apple
23 2222 None Bean chilli Apple
24 2222 None Bean chilli Apple
25 2222 None Bean chilli Apple
26 2222 None Bean chilli Apple
27 2222 None Bean chilli Apple
That's your file in a pandas dataframe, the standard container in pandas, if you like. And now you can make a plenty of statistical things with it... Just dig a little through the docs
Examples:
df.groupby(['Participant ID', 'Breakfasts']).Breakfasts.count()
Participant ID Breakfasts
1111 Full english 7
2222 Avocado toast 7
Name: Breakfasts, dtype: int64
df.groupby(['Participant ID', 'Lunches/dinners'])['Lunches/dinners'].count()
Participant ID Lunches/dinners
1111 Risotto 14
2222 Bean chilli 14
Name: Lunches/dinners, dtype: int64
Of course you can separate by Participant ID:
oneoneoneone = df[df['Participant ID'] == 1111]
oneoneoneone
Participant ID Breakfasts Lunches/dinners Snacks
0 1111 Full english Risotto Granola
1 1111 Full english Risotto Granola
2 1111 Full english Risotto Granola
3 1111 Full english Risotto Granola
4 1111 Full english Risotto Granola
5 1111 Full english Risotto Granola
6 1111 Full english Risotto Granola
7 1111 None Risotto Granola
8 1111 None Risotto Granola
9 1111 None Risotto Granola
10 1111 None Risotto Granola
11 1111 None Risotto Granola
12 1111 None Risotto Granola
13 1111 None Risotto Granola
oneoneoneone.to_csv('target_file')
and so would perhaps
twotwotwotwo.to_csv('another_target_file')
One can also iterate over groups, and then apply always the same operations on each group.
e.g.:
for name, group in df.groupby('Participant ID'):
print(name)
print(group.groupby('Breakfasts').Breakfasts.count().to_string())
print(group.groupby('Lunches/dinners')['Lunches/dinners'].count().to_string())
print(group.groupby('Snacks').Snacks.count().to_string(), 'n')
returns:
1111
Breakfasts
Full english 7
Lunches/dinners
Risotto 14
Snacks
Granola 14
2222
Breakfasts
Avocado toast 7
Lunches/dinners
Bean chilli 14
Snacks
Apple 14
answered Nov 20 '18 at 12:43
SpghttCdSpghttCd
4,8022313
Just to show where you exemplary could go using pandas:
import pandas as pd
df = pd.read_csv('whereverfilemayroam/filename')
Participant ID Breakfasts Lunches/dinners Snacks
0 1111 Full english Risotto Granola
1 1111 Full english Risotto Granola
2 1111 Full english Risotto Granola
3 1111 Full english Risotto Granola
4 1111 Full english Risotto Granola
5 1111 Full english Risotto Granola
6 1111 Full english Risotto Granola
7 1111 None Risotto Granola
8 1111 None Risotto Granola
9 1111 None Risotto Granola
10 1111 None Risotto Granola
11 1111 None Risotto Granola
12 1111 None Risotto Granola
13 1111 None Risotto Granola
14 2222 Avocado toast Bean chilli Apple
15 2222 Avocado toast Bean chilli Apple
16 2222 Avocado toast Bean chilli Apple
17 2222 Avocado toast Bean chilli Apple
18 2222 Avocado toast Bean chilli Apple
19 2222 Avocado toast Bean chilli Apple
20 2222 Avocado toast Bean chilli Apple
21 2222 None Bean chilli Apple
22 2222 None Bean chilli Apple
23 2222 None Bean chilli Apple
24 2222 None Bean chilli Apple
25 2222 None Bean chilli Apple
26 2222 None Bean chilli Apple
27 2222 None Bean chilli Apple
That's your file in a pandas dataframe, the standard container in pandas, if you like. And now you can make a plenty of statistical things with it... Just dig a little through the docs
Examples:
df.groupby(['Participant ID', 'Breakfasts']).Breakfasts.count()
Participant ID Breakfasts
1111 Full english 7
2222 Avocado toast 7
Name: Breakfasts, dtype: int64
df.groupby(['Participant ID', 'Lunches/dinners'])['Lunches/dinners'].count()
Participant ID Lunches/dinners
1111 Risotto 14
2222 Bean chilli 14
Name: Lunches/dinners, dtype: int64
Of course you can separate by Participant ID:
oneoneoneone = df[df['Participant ID'] == 1111]
oneoneoneone
Participant ID Breakfasts Lunches/dinners Snacks
0 1111 Full english Risotto Granola
1 1111 Full english Risotto Granola
2 1111 Full english Risotto Granola
3 1111 Full english Risotto Granola
4 1111 Full english Risotto Granola
5 1111 Full english Risotto Granola
6 1111 Full english Risotto Granola
7 1111 None Risotto Granola
8 1111 None Risotto Granola
9 1111 None Risotto Granola
10 1111 None Risotto Granola
11 1111 None Risotto Granola
12 1111 None Risotto Granola
13 1111 None Risotto Granola
oneoneoneone.to_csv('target_file')
and so would perhaps
twotwotwotwo.to_csv('another_target_file')
One can also iterate over groups, and then apply always the same operations on each group.
e.g.:
for name, group in df.groupby('Participant ID'):
print(name)
print(group.groupby('Breakfasts').Breakfasts.count().to_string())
print(group.groupby('Lunches/dinners')['Lunches/dinners'].count().to_string())
print(group.groupby('Snacks').Snacks.count().to_string(), 'n')
returns:
1111
Breakfasts
Full english 7
Lunches/dinners
Risotto 14
Snacks
Granola 14
2222
Breakfasts
Avocado toast 7
Lunches/dinners
Bean chilli 14
Snacks
Apple 14
answered Nov 20 '18 at 12:43
SpghttCdSpghttCd
4,8022313
edited Nov 20 '18 at 16:01
answered Nov 20 '18 at 12:43
SpghttCdSpghttCd
4,8022313
answered Nov 20 '18 at 12:43
SpghttCdSpghttCd
4,8022313
answered Nov 20 '18 at 12:43
SpghttCdSpghttCd
4,8022313
4,8022313
add a comment |
add a comment |
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%2f53393072%2ftransferring-data-from-excel-to-word-in-python-3%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
If you want to read in csv-files but then also make some calculations like counts as you stated, I'd recommend using
pandas. With that also separate write-outs of sub groups into different files is no problem. But honestly, up to now I did not really understand what exactly you want to do....– SpghttCd
Nov 20 '18 at 12:37
Thanks for your response - I really am extremely new to this so pardon me for not being clear. I am attempting to have a python script that will, when executed, open a master spreadsheet (into which the meal choices of all participants for that week have been entered), select all of participant 1111's choices and output a delivery note listing all of the meals to be delivered to participant 1111 that week. The output will have Participant ID (1111), Breakfasts: 7 x Full english, Lunches/Dinners: 14 x Risotto, and so on. Then, the same task repeated for all participants.
– Kieran
Nov 20 '18 at 12:45
No reason to pardon - I think you really should have a closer look at pandas. It's already a very powerful tool for data analysis and is actively developedon. And if I look at your profile, I think you'll be happy to use it in the future again and again...
– SpghttCd
Nov 20 '18 at 12:55
Thanks very much, I'll look into it!
– Kieran
Nov 20 '18 at 13:05