Pandas insert empty column between existing columns

Question

I'm trying to make a csv converter with Pandas. Currently it reads a file (export.csv) and writes a new file (google.csv). I've selected the columns from the export.csv file but for the new file I want to insert a few new columns.

I'd like to insert one at the beginning of the dataframe called Date and have the same value entered (11-23-20) for each row, I'd like to add another one between ProductUrl and RetailPrice called Shippinged with no values.

Here's my code:

import pandas as pd

ed = pd.read_csv('export.csv')
df = ed[['ItemID', 'ProductUrl', 'RetailPrice', 'ItemName', 'ItemName', 'ItemPrimaryImageUrl', 'CategoryList', 'Manufacturer', 'GTIN', 'ProductStatus','CategoryList', 'ItemNumber']]
df.columns =['id', 'link', 'price', 'title', 'description','image_link', 'product_type','brand', 'gtin', 'availability', 'google_product_category','mpn'] 
df.to_csv('google.csv')

df.insert(0, 'col_name', value_you_want_to_input) should help you add to the dataframe. This will repeat the value at column 1 with column name as col_name. — Joe Ferndz
– Joe Ferndz, Commented Nov 24, 2020 at 1:10

Joe Ferndz · Accepted Answer · 2020-11-24 01:37:18Z

To insert a column at the specific location, you can use:

df.insert(loc, column, value)

Let's assume, your dataset is as follows:

>>> df

   ItemID ProductUrl  RetailPrice ItemName
0       1       aUrl          1.0    aItem
1       2       bUrl          2.0    bItem
2       3       cUrl          3.0    cItem

You can add a column to the beginning by using:

df.insert(0, 'Date', pd.Timestamp.now().strftime('%m-%d-%y'))

This will give you:

>>> df.insert(0, 'Date', pd.Timestamp.now().strftime('%m-%d-%y'))
>>> df
       Date  ItemID ProductUrl  RetailPrice ItemName
0  11-23-20       1       aUrl          1.0    aItem
1  11-23-20       2       bUrl          2.0    bItem
2  11-23-20       3       cUrl          3.0    cItem

Then you can add the column to the specific location with a similar insert statement.

You have two options: You can search for the column name where you want to insert, then insert. OR, you can insert to a specific location if you already know the location.

In your case, you want to insert the column between 'ProductUrl' and 'RetailPrice'. The original location for 'RetailPrice' was 3. We added a column to the beginning. So the current location for 'RetailPrice' is 4. So you can insert the new column at position 4. Remember, columns starts from 0. So you need to insert at location 3 (0 = Date, 1 = ItemID, 2 = ProductUrl, 3 = newColumn, 4 = RetailPrice).

>>> df.insert(3, 'Shippinged', '')
>>> df
       Date  ItemID ProductUrl Shippinged  RetailPrice ItemName
0  11-23-20       1       aUrl                     1.0    aItem
1  11-23-20       2       bUrl                     2.0    bItem
2  11-23-20       3       cUrl                     3.0    cItem

Alternatively, you can use columns.get_loc() to get the index of any column.

Since you want to insert the column in between 'ProductUrl' and 'RetailPrice', you can choose to search for the position of 'RetailPrice' and insert at that location.

>>> idx = df.columns.get_loc('RetailPrice')
>>> df.insert(idx, 'Shippinged', '')
>>> df
       Date  ItemID ProductUrl Shippinged  RetailPrice ItemName
0  11-23-20       1       aUrl                     1.0    aItem
1  11-23-20       2       bUrl                     2.0    bItem
2  11-23-20       3       cUrl                     3.0    cItem

This will give you the new dataframe to write to 'google.csv'

Cainã Max Couto da Silva · Accepted Answer · 2020-11-24 01:40:41Z

1

Use pd.DataFrame.insert:

df.insert(0, 'Date', pd.Timestamp.now().strftime('%m-%d-%y'))

idx = df.columns.get_loc('ProductUrl')
df.insert(idx+1, 'Shippinged', '')

edited Nov 24, 2020 at 1:40

answered Nov 24, 2020 at 1:15

Cainã Max Couto da Silva

4,9691 gold badge15 silver badges39 bronze badges

3 Comments

Joe Ferndz Over a year ago

i think the new column has to be between 'ProductUrl' and 'RetailPrice' so you need to search for 'RetailPrice' and insert at that location.

Cainã Max Couto da Silva Over a year ago

Yep, or you can just add 1 to the index. Updated. Thx.

kyrlon Dec 14, 2024 at 1:16

THis is great to insert after a named column rather than guessing the index number!

Collectives™ on Stack Overflow

Pandas insert empty column between existing columns

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related