How to Add a New Column to a CSV File

How to add a new column to a CSV file?

This should give you an idea of what to do:

>>> v = open('C:/test/test.csv')
>>> r = csv.reader(v)
>>> row0 = r.next()
>>> row0.append('berry')
>>> print row0
['Name', 'Code', 'berry']
>>> for item in r:
...     item.append(item[0])
...     print item
...     
['blackberry', '1', 'blackberry']
['wineberry', '2', 'wineberry']
['rasberry', '1', 'rasberry']
['blueberry', '1', 'blueberry']
['mulberry', '2', 'mulberry']
>>> 

Edit, note in py3k you must use next(r)

Thanks for accepting the answer. Here you have a bonus (your working script):

import csv

with open('C:/test/test.csv','r') as csvinput:
    with open('C:/test/output.csv', 'w') as csvoutput:
        writer = csv.writer(csvoutput, lineterminator='\n')
        reader = csv.reader(csvinput)

        all = []
        row = next(reader)
        row.append('Berry')
        all.append(row)

        for row in reader:
            row.append(row[0])
            all.append(row)

        writer.writerows(all)

Please note

1. the lineterminator parameter in csv.writer. By default it is
   set to '\r\n' and this is why you have double spacing.
2. the use of a list to append all the lines and to write them in
   one shot with writerows. If your file is very, very big this
   probably is not a good idea (RAM) but for normal files I think it is
   faster because there is less I/O.

3. As indicated in the comments to this post, note that instead of
   nesting the two with statements, you can do it in the same line:

   with open('C:/test/test.csv','r') as csvinput, open('C:/test/output.csv', 'w') as csvoutput:

Add columns to existing CSV file

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

Add new column in existing csv file in c#

This is my attempt, trying to make it a little more flexible and more robust, also allowing you to insert values in the data rows while keeping the alignment.

string inputFile = @"import.csv";
string outputFile = @"output.csv";

bool startInserting = false;
string getLine(string endDateText, string lengthText) => $"{endDateText,-19}|{lengthText,-8}|";

var fileContents = File.ReadAllLines(inputFile);
for (int i = 0; i < fileContents.Length; i++)
{
    var line = fileContents[i];

    if (line.Contains("Table1"))
    {
        startInserting = true;
        continue;
    }

    if (line.Contains("Table2"))
        break;

    if (line.Length == 0 || line[0] == '|')
        continue;

    if (startInserting)
    {
        int columnIndex = 33;
        if (line[0] == 'N')
        {
            string headerLine = getLine("END DATE HOUR", "LENGHT"); // I keep the typo
            fileContents[i] = line.Insert(columnIndex, headerLine);
        }
        else if (char.IsDigit(line[0]))
        {
            string dataLine = getLine("", "");
            fileContents[i] = line.Insert(columnIndex, dataLine);
        }
    }

}

File.WriteAllLines(outputFile, fileContents);

This is the output:

||||||||||||||||||||||||||||||||||||||||||||||||||
|Table1|||||||||||||||||||||||||||||||||||||||||||||||||            
||||||||||||||||||||||||||||||||||||||||||||||||||          
N|IDI  |TEST|START DATE HOUR    |END DATE HOUR      |LENGHT  |CAUSE|KIND|NUMB|NAMES|         
1|10704|    |21/07/2020 15:05:54|                   |        |L    |MT  |2786|NAV  |         
2|10660|    |21/07/2020 09:27:31|                   |        |L    |MT  |4088|PIS  |     
||||||||||||||||||||||||||||||||||||||||||||||||||          
|Table2|||||||||||||||||||||||||||||||||||||||||||||||||            
||||||||||||||||||||||||||||||||||||||||||||||||||          
N|IDI  |TEST|START DATE HOUR    |END DATE HOUR      |LENGHT  |RETURNS         |CAUSE|KIND|NUMB|NAMES|           
1|10710|    |21/07/2020 19:34:00|21/07/2020 20:19:09|00:45:09| -              |L    |MT  |7806|ACC  |
2|10708|    |21/07/2020 18:28:12|21/07/2020 18:28:13|00:00:01| -              |T    |MT  |2600|LIT  |       
3|10700|    |21/07/2020 14:16:37|21/07/2020 15:19:13|01:02:36|21/07/2020 17:00|L    |MT  |4435|UHI  |       
4|10698|    |21/07/2020 14:06:45|21/07/2020 14:07:22|00:00:37|-               |B    |MT  |5789|TYK  |
5|10674|    |21/07/2020 10:21:04|21/07/2020 10:44:41|00:23:37|21/07/2020 12:30|T    |MT  |6699|FGR  |
||||||||||||||||||||||||||||||||||||||||||||||||||

Note: it's using some relatively new C# features, so if you get compilation errors you will have to do some adjustments.

Also note that I'm not inserting RETURNS, you talk about missing columns but for some reason skip that one.

How to append a new column to a CSV file using Python?

Probably the simplest solution would be to use Pandas. It's overkill, but it generally is much cleaner for CSV manipulation that extends beyond straight reading/writing.

Say I have a CSV file as follows:

ASSETNUM    ASSETTAG    ASSETTYPE   AUTOWOGEN
cent45  9164        0
cent45  9164        0

Then, the relevant code to add a column would be as follows:

import pandas as pd

df = pd.read_csv('path/to/csv.csv', delimiter='\t')
# this line creates a new column, which is a Pandas series.
new_column = df['AUTOWOGEN'] + 1
# we then add the series to the dataframe, which holds our parsed CSV file
df['NewColumn'] = new_column
# save the dataframe to CSV
df.to_csv('path/to/file.csv', sep='\t')

This adds a new column, scales well, and is easy to use. The resulting CSV file then would look as follows:

    ASSETNUM    ASSETTAG    ASSETTYPE   AUTOWOGEN   NewColumn
0   cent45  9164        0   1
1   cent45  9164        0   1

Compare this to the CSV module code for the same purpose (modified from here):

with open('path/to/csv.csv', 'r') as fin:
    reader = csv.reader(fin, delimiter='\t')
    with open('new_'+csvfile, 'w') as fout:
        writer = csv.writer(fout, delimiter='\t')
        # set headers here, grabbing headers from reader first
        writer.writerow(next(reader) + ['NewColumn']

        for row in reader:
            # use whatever index for the value, or however you want to construct your new value
            new_value = reader[-1] + 1
            row.append(new_value)
            writer.writerow(row)

How can I add string and create new column in my csv file using PowerShell

Using calculated properties with Select-Object this is how it could look:

$add = "@GMAIL.COM"

$expression = {
    switch($_.'SharePoint ID')
    {
        {[string]::IsNullOrWhiteSpace($_) -or $_ -match 'MSTeam'}
        {
            # Null value or mathces MSTeam, leave this Null
            break
        }
        Default # We can assume these are IDs, append $add
        {
            $_.Trim() + $add
        }
    }
}

Import-Csv $file | Select-Object *, @{
    Name = 'SharePoint Email'
    Expression = $expression
} | Export-Csv $file2 -NoTypeInformation

Sample Output

Index SharePoint ID SharePoint Email
----- ------------- ----------------
1     ylkbq         ylkbq@GMAIL.COM
2     KlMNO         KlMNO@GMAIL.COM
3                   
4     MSTeam        
5                   
6     MSTEAM        
7     LMNO83        LMNO83@GMAIL.COM

---

A more concise expression, since I misread the point, it can be reduced to just one if statement:

$expression = {
    if(-not [string]::IsNullOrWhiteSpace($_.'SharePoint ID') -and $_ -notmatch 'MSTeam')
    {
        $_.'SharePoint ID'.Trim() + $add
    }
}

Add a column to a csv file and fill up new column based on an existing column powershell

You've got the right idea. Import-Csv will produce an array of objects and you can use Select-Object to add calculated properties, then pipe again to Export-Csv. However, it's not exactly clear from the description or the example code what the expression should be. How do you want to define the new "data" property?

For now I'll work with what we have. The array variables $a & $b will never match anything. Also you can't use ForEach-Object like that, nor will assigning to $data work. The returning value of the Expression script block gets assigned to the property you named data. The following example demonstrates the point:

$a = ( "1", "2", "3")
$b = ( "4", "5", "6")

Import-Csv -Path "C:\temp\12-22-20.csv"|
Select-Object number, state, desc,
    @{Name = 'Data'; Expression = { If( $_.Number -in $a ){ 'x' } elseif( $_.Number -in $b ){ 'y' } Else { $null }}} |
Export-Csv -Path "C:\temp\12-22-20_New.csv" -NoTypeInformation

The resulting Csv file will look something like:

number state desc Data
------ ----- ---- ----
1      n     i    x
2      n     j    x
3      l     j    x
4      m     k    y

Update: Example Using Add-Member

You do not need to use a loop to add the property:

$a = ( "1", "2", "3")
$b = ( "4", "5", "6")

Import-Csv -Path "C:\temp\12-22-20.csv" |
Add-Member -MemberType ScriptProperty -Name "data" -Value { If( $this.Number -in $a ){ 'x' } elseif( $this.Number -in $b ){ 'y' } Else { $null }} -PassThru |
Export-Csv -Path C:\temp\12-22-20_New.csv -NoTypeInformation

By using a MemberType of ScriptProperty we can make a slight modification to script block, replacing $_ with $this The pipe is an implicit loop. I'm not sure if there are any detractions to using a ScriptProperty, but this exports as expected. This approach doesn't require storing the output in $c, but -PassThru would facilitate that if preferred.

99% of the time Select-Object is used for this. The only difference I'm aware of it Select-Object converts the objects to PSCustomObjects. Get-Member will preserve the underlying type, however Import-Csv only outputs PSCustomObjects in the first place, so there's no impact here.

Adding data to csv using pandas dataframe, by adding new column

Add the following and try it out:

records=cur.fetchall()

# Create a dataframe of the SQL query's result 
column_names = ['emp_name','login_count']
df = pd.DataFrame(records, columns = column_names)
df.head()

Now create another dataframe for the daily login counts csv file

df_daily = pd.read_csv('<INSERT the path_to_csv here>')
df_daily.head()

Merge the two dataframes on the 'emp_name' column

result = df.merge(df_daily, on='emp_name')
result.head()

After the join, you can rename the 'login_count' column to today's date

result.rename(columns = {'login_count':'< INSERT date here>'}, inplace = True)

You can then save the new data into a csv file again:

pd.to_csv('<INSERT name of file.csv>', index=False)

How to insert a new column and export it to CSV in Python?

Not sure about the best way. But I suggest you can do it like this:

...
array6 = []
for e in range(len(result)):
    array6.append([e+1] + list(nresult[e]))
...
w.writerow(['Item NO', 'ID', 'Type', 'Project', 'Name', 'Location'])
...

---

Related Topics