Styjun

Why Should I Care That Fully Meshed Peering Leads to Exponential Growth of Total Connections?

French license plates

PhD Length: are shorter PhD degrees (from different countries) valued differently in other counter countries where PhD Is a longer process?

Can I bring this power bank on board the aircraft?

How to interpret the challenge rating of creatures?

Can a passenger predict that an airline or a tour operator is about to go bankrupt?

Caro-Kann c4-c5 push

MaxCounters solution in C# from Codility

Would an object shot from earth fall into the sun?

How to "Start as close to the end as possible", and why to do so?

What action is recommended if your accommodation refuses to let you leave without paying additional fees?

Replace zeros in a list with last nonzero value

Re-entering the UK after overstaying in 2008

How can Germany increase investments in Russia while EU economic sanctions against Russia are still in place?

Short story about a potato hotel that makes its guests into potatoes throughout the night

How dangerous are my worn rims?

Why the first octet of a MAC address always end with a binary 0?

Why such a singular place for bird watching?

Giving a good fancy look to a simple table

What does a textbook look like while you are writing it?

How to identify whether a publisher is genuine or not?

Is "weekend warrior" derogatory?

Job interview by video at home and privacy concerns

Could Boris Johnson face criminal charges for illegally proroguing Parliament?

Splitting up a CSV into separate excel files based on a columns values, then change formatting of excel before saving

Select rows from a DataFrame based on values in a column in pandasDeleting DataFrame row in Pandas based on column valueCSV format data manipulation: why use python scripts instead of MS excel functions?Extracting and manipulating data from excel worksheet with pythonTransfer data from one excel worksheet to anotherSplit dataframes in groups and sub-groups and store the output in a CSV fileSplitting a single column in python into multiple separate columnsPandas and Openpyxl overwrites excelWhen I save as CSV, the file contains two separators which are comma and pipehow to save output to csv file python

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty
margin-bottom:0;

0

Hi I am vey new to python but have been tasked with creating a tool that does the following:
1) opens a csv file
2) splits the data frame up by the values of a single column
3) It will then save those groupings to individual excel workbooks and manipulate the formatting (may add a chart to one of the worksheets based on the newly added Data)

I have found this code, which groups and saves to csv. I can change to the excel format, but I’m really struggling I do the formatting and chart bit. Any help would be very appreciated.

gp = df.groupby('CloneID')
for g in gp.groups:
 path = 'CloneID' + str(g) + '.txt'
 gp.get_group(g).to_csv(path)

python pandas openpyxl

share|improve this question

asked Mar 28 at 20:47

jfleet_ukjfleet_uk

1

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You ask to save excel file but your sample is to_csv. Did you try to excel?
  
  – Quang Hoang
  Mar 28 at 21:32
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi Quang, thank you for replying, yes I can convert it to xlsx, but the issue I have is using the functunality of the various packages ie openpyxls. Im not sure qhere to include the functions in the code for the loop to pick them up and include the formatting
  
  – jfleet_uk
  Mar 28 at 21:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Could you elaborate a bit on what formatting you're exactly trying to do? and any code you have now that attempt to format it?
  
  – Richard Stoeffel
  Mar 28 at 21:52
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi Richard, after it has added in the data from one of the groups, I want the script to add in a freeze pane on the header and have a chart on a separate worksheet
  
  – jfleet_uk
  Mar 28 at 21:59
  

  
  
  
  

add a comment
 | 

0

Hi I am vey new to python but have been tasked with creating a tool that does the following:
1) opens a csv file
2) splits the data frame up by the values of a single column
3) It will then save those groupings to individual excel workbooks and manipulate the formatting (may add a chart to one of the worksheets based on the newly added Data)

I have found this code, which groups and saves to csv. I can change to the excel format, but I’m really struggling I do the formatting and chart bit. Any help would be very appreciated.

gp = df.groupby('CloneID')
for g in gp.groups:
 path = 'CloneID' + str(g) + '.txt'
 gp.get_group(g).to_csv(path)

python pandas openpyxl

share|improve this question

asked Mar 28 at 20:47

jfleet_ukjfleet_uk

1

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You ask to save excel file but your sample is to_csv. Did you try to excel?
  
  – Quang Hoang
  Mar 28 at 21:32
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi Quang, thank you for replying, yes I can convert it to xlsx, but the issue I have is using the functunality of the various packages ie openpyxls. Im not sure qhere to include the functions in the code for the loop to pick them up and include the formatting
  
  – jfleet_uk
  Mar 28 at 21:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Could you elaborate a bit on what formatting you're exactly trying to do? and any code you have now that attempt to format it?
  
  – Richard Stoeffel
  Mar 28 at 21:52
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi Richard, after it has added in the data from one of the groups, I want the script to add in a freeze pane on the header and have a chart on a separate worksheet
  
  – jfleet_uk
  Mar 28 at 21:59
  

  
  
  
  

add a comment
 | 

Hi I am vey new to python but have been tasked with creating a tool that does the following:
1) opens a csv file
2) splits the data frame up by the values of a single column
3) It will then save those groupings to individual excel workbooks and manipulate the formatting (may add a chart to one of the worksheets based on the newly added Data)

I have found this code, which groups and saves to csv. I can change to the excel format, but I’m really struggling I do the formatting and chart bit. Any help would be very appreciated.

gp = df.groupby('CloneID')
for g in gp.groups:
 path = 'CloneID' + str(g) + '.txt'
 gp.get_group(g).to_csv(path)

python pandas openpyxl

share|improve this question

asked Mar 28 at 20:47

jfleet_ukjfleet_uk

1

gp = df.groupby('CloneID')
for g in gp.groups:
 path = 'CloneID' + str(g) + '.txt'
 gp.get_group(g).to_csv(path)

share|improve this question

asked Mar 28 at 20:47

jfleet_ukjfleet_uk

1

share|improve this question

asked Mar 28 at 20:47

jfleet_ukjfleet_uk

1

asked Mar 28 at 20:47

jfleet_ukjfleet_uk

1

asked Mar 28 at 20:47

jfleet_ukjfleet_uk

1

1 Answer
1

active

oldest

votes

1

One easy way to create nicely formatted excel sheets is to pre-format a template, and use openpyxl to fill in the rows as you need.

At a high level, your project should include a template, which will be an xlsx file (excel). If you named your project my_project, for example, the structure of your project should look like this:

my_project
--__init__.py
--templates
----formated_excel.xlsx
--main.py

where templates is a directory, formatted_excel is an xlsx file, and main.py is your code.

In main.py, the basic logic of your code would work like this:

import os
import openpyxl

TEMPLATE = os.path.join(os.path.dirname(os.path.abspath(__file__)),
 'templates', 'formated_excel.xlsx')

wb = openpyxl.load_workbook(TEMPLATE)
# to use wb[VALUE], your template must have a sheet called VALUE
data_sheet = wb['Data'] 

# have enumerate start at 2, as in most cases row 1 of a sheet 
# is the header
for row, value in enumerate(data, start=2): 
 data_sheet[f'Arow'] = value

wb.save('my_output.xlsx')

This example is a very, very basic explanation of how to use openpyxl.

Note that I've assumed you are using python3, if not, you'll have to use the appropriate string formatting when setting the data_sheet row that you are writing to. Openpyxl also has Chart Support, which you can read up on to hep you in formatting your chart.

You haven't provided much detail in exactly what you want to do or the data you are using, so you will have to extend this example to fit your dataset.

share|improve this answer

answered Mar 28 at 22:14

Richard StoeffelRichard Stoeffel

57466 silver badges1313 bronze badges

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/4.0/"u003ecc by-sa 4.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');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55406605%2fsplitting-up-a-csv-into-separate-excel-files-based-on-a-columns-values-then-cha%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

One easy way to create nicely formatted excel sheets is to pre-format a template, and use openpyxl to fill in the rows as you need.

At a high level, your project should include a template, which will be an xlsx file (excel). If you named your project my_project, for example, the structure of your project should look like this:

my_project
--__init__.py
--templates
----formated_excel.xlsx
--main.py

where templates is a directory, formatted_excel is an xlsx file, and main.py is your code.

In main.py, the basic logic of your code would work like this:

import os
import openpyxl

TEMPLATE = os.path.join(os.path.dirname(os.path.abspath(__file__)),
 'templates', 'formated_excel.xlsx')

wb = openpyxl.load_workbook(TEMPLATE)
# to use wb[VALUE], your template must have a sheet called VALUE
data_sheet = wb['Data'] 

# have enumerate start at 2, as in most cases row 1 of a sheet 
# is the header
for row, value in enumerate(data, start=2): 
 data_sheet[f'Arow'] = value

wb.save('my_output.xlsx')

This example is a very, very basic explanation of how to use openpyxl.

Note that I've assumed you are using python3, if not, you'll have to use the appropriate string formatting when setting the data_sheet row that you are writing to. Openpyxl also has Chart Support, which you can read up on to hep you in formatting your chart.

You haven't provided much detail in exactly what you want to do or the data you are using, so you will have to extend this example to fit your dataset.

share|improve this answer

answered Mar 28 at 22:14

Richard StoeffelRichard Stoeffel

57466 silver badges1313 bronze badges

add a comment
 | 

1

One easy way to create nicely formatted excel sheets is to pre-format a template, and use openpyxl to fill in the rows as you need.

At a high level, your project should include a template, which will be an xlsx file (excel). If you named your project my_project, for example, the structure of your project should look like this:

my_project
--__init__.py
--templates
----formated_excel.xlsx
--main.py

where templates is a directory, formatted_excel is an xlsx file, and main.py is your code.

In main.py, the basic logic of your code would work like this:

import os
import openpyxl

TEMPLATE = os.path.join(os.path.dirname(os.path.abspath(__file__)),
 'templates', 'formated_excel.xlsx')

wb = openpyxl.load_workbook(TEMPLATE)
# to use wb[VALUE], your template must have a sheet called VALUE
data_sheet = wb['Data'] 

# have enumerate start at 2, as in most cases row 1 of a sheet 
# is the header
for row, value in enumerate(data, start=2): 
 data_sheet[f'Arow'] = value

wb.save('my_output.xlsx')

This example is a very, very basic explanation of how to use openpyxl.

Note that I've assumed you are using python3, if not, you'll have to use the appropriate string formatting when setting the data_sheet row that you are writing to. Openpyxl also has Chart Support, which you can read up on to hep you in formatting your chart.

You haven't provided much detail in exactly what you want to do or the data you are using, so you will have to extend this example to fit your dataset.

share|improve this answer

answered Mar 28 at 22:14

Richard StoeffelRichard Stoeffel

57466 silver badges1313 bronze badges

add a comment
 | 

One easy way to create nicely formatted excel sheets is to pre-format a template, and use openpyxl to fill in the rows as you need.

At a high level, your project should include a template, which will be an xlsx file (excel). If you named your project my_project, for example, the structure of your project should look like this:

my_project
--__init__.py
--templates
----formated_excel.xlsx
--main.py

where templates is a directory, formatted_excel is an xlsx file, and main.py is your code.

In main.py, the basic logic of your code would work like this:

import os
import openpyxl

TEMPLATE = os.path.join(os.path.dirname(os.path.abspath(__file__)),
 'templates', 'formated_excel.xlsx')

wb = openpyxl.load_workbook(TEMPLATE)
# to use wb[VALUE], your template must have a sheet called VALUE
data_sheet = wb['Data'] 

# have enumerate start at 2, as in most cases row 1 of a sheet 
# is the header
for row, value in enumerate(data, start=2): 
 data_sheet[f'Arow'] = value

wb.save('my_output.xlsx')

This example is a very, very basic explanation of how to use openpyxl.

Note that I've assumed you are using python3, if not, you'll have to use the appropriate string formatting when setting the data_sheet row that you are writing to. Openpyxl also has Chart Support, which you can read up on to hep you in formatting your chart.

You haven't provided much detail in exactly what you want to do or the data you are using, so you will have to extend this example to fit your dataset.

share|improve this answer

answered Mar 28 at 22:14

Richard StoeffelRichard Stoeffel

57466 silver badges1313 bronze badges

my_project
--__init__.py
--templates
----formated_excel.xlsx
--main.py

import os
import openpyxl

TEMPLATE = os.path.join(os.path.dirname(os.path.abspath(__file__)),
 'templates', 'formated_excel.xlsx')

wb = openpyxl.load_workbook(TEMPLATE)
# to use wb[VALUE], your template must have a sheet called VALUE
data_sheet = wb['Data'] 

# have enumerate start at 2, as in most cases row 1 of a sheet 
# is the header
for row, value in enumerate(data, start=2): 
 data_sheet[f'Arow'] = value

wb.save('my_output.xlsx')

share|improve this answer

answered Mar 28 at 22:14

Richard StoeffelRichard Stoeffel

57466 silver badges1313 bronze badges

answered Mar 28 at 22:14

Richard StoeffelRichard Stoeffel

57466 silver badges1313 bronze badges

answered Mar 28 at 22:14

Richard StoeffelRichard Stoeffel

57466 silver badges1313 bronze badges