Add columns to existing CSV file

Question

I am trying to add new columns to an existing csv that already has rows and columns that looks like this:

I would like it to append all the new column names to the columns after column 4.

The code I currently have is adding all the new columns to the bottom of the csv:

def extract_data_from_report3():
    with open('OMtest.csv', 'a', newline='') as f_out:
        writer = csv.writer(f_out)
        writer.writerow(
            ['OMGroup:OMRegister', 'OMGroup', 'OMRegister', 'RegisterType', 'Measures', 'Description', 'GeneratedOn'])

Is there any way to do this effectively?

Does this answer your question? [How to add a new column to a CSV file?](https://stackoverflow.com/questions/11070527/how-to-add-a-new-column-to-a-csv-file) — Pranav Hosangadi, Oct 20 '20 at 18:41
Also see [my answer](https://stackoverflow.com/a/4873050/355230) to a related question — it's relatively easy with a `csv.DictReader` and `csv.DictWriter`. — martineau, Oct 20 '20 at 18:43

Nikaido · Accepted Answer · 2020-10-20T19:07:28.097

1

You can use the pandas lib, without iterating through the values. Here an example

new_header = ['OMGroup:OMRegister', 'OMGroup', 'OMRegister', 'RegisterType', 'Measures', 'Description', 'GeneratedOn']
# Import pandas package  
import pandas as pd 

my_df = pd.read_csv(path_to_csv)
for column_name in new_header:
    new_column = [ ... your values ...] #should be a list of your dataframe size
    my_df[column_name] = new_column

keep in mind that the new column should have the same size of the number of rows of your table to work

If you need only to add the new columns without values, you can do as such:

for column_name in new_header:
    new_column = ["" for i in range(len(mydf.index))] #should be a list of dataframe size
    my_df[column_name] = new_column

Then you can write back the csv in this way:

my_df.to_csv(path_to_csv)

Here details on the read_csv method

Here details on the to_csv method

edited Oct 20 '20 at 19:07

answered Oct 20 '20 at 18:44

Nikaido

4,443
5
30
47

So if I do it this way, it has to the same number of rows as the previous data on the csv? – marcorivera8 Oct 20 '20 at 18:51
1

@marcorivera8 yes, you are adding a new column, not a new row – Nikaido Oct 20 '20 at 18:52
1

@marcorivera8 keep in mind that here `my_df['OMGroup:OMRegister']` I am using the first value of your list as column name. I am assuming that's the column name – Nikaido Oct 20 '20 at 18:54
1

As I can see from the list `['OMGroup:OMRegister', 'OMGroup', 'OMRegister', 'RegisterType', 'Measures', 'Description', 'GeneratedOn']` seems more a new header than actually values in your rows – Nikaido Oct 20 '20 at 18:56
Yes those are headers, I keep getting the `ValueError: Length of values (6) does not match length of index` error though – marcorivera8 Oct 20 '20 at 18:56
`OMGroup:OMRegister', 'OMGroup', ` etc are each a separate column name – marcorivera8 Oct 20 '20 at 18:58
@marcorivera8 check my answer. I have done an update. If you do not any value for the new columns you can fill them with empty string `["", "", "", "", "", ""]` – Nikaido Oct 20 '20 at 18:59
@marcorivera8 I am assuming that you have 6 rows (without header). But it depends on your csv – Nikaido Oct 20 '20 at 19:02
1

I am incorporating this code with BeautifulSoup and XML data which is where the row content is coming from. I'll let u know in a bit if I get it to work properly. Thanks for the help! – marcorivera8 Oct 20 '20 at 19:15
idk if you can help me with this, but I am having issues doing this strategy with BeautifulSoup so I updated the question. – marcorivera8 Oct 20 '20 at 19:58
1

@marcorivera8 I unfortunately don't know beautiful soup. I suggest you to open a new question (it is better to not expand your problem further on this question, to keep the question specific and not too broad). Please don't forget to accept the best answer for your first question – Nikaido Oct 20 '20 at 20:12

Add columns to existing CSV file

1 Answers1