Brian Powell Brian Powell - 1 year ago 65
Python Question

pandas fill created sheet row by row

I have the following python code

sheet_a = pd.read_excel(open('c:\\upload\\' + f,'rb'), skiprows=1, sheetname='a')
sheet_b = pd.read_excel(open('c:\\upload\\' + f,'rb'), skiprows=1, sheetname='b')

Within these two sheets, I have two columns that I am creating from scratch,
Full Name
. The team name will be the same for all rows, so:

sheet_a['Team'] = "Team A"
sheet_b['Team'] = "Team B"

but I can't figure out how to create a value row by row. In order to populate each user's
Full Name
column, I'm trying to join the
First Name
column with the
Last Name
column from that same row to create
Bob Smith

sheet_a['Full Name'] = row['First Name'] + ' ' + row['Last Name']
sheet_b['Full Name'] = row['First Name'] + ' ' + row['Last Name']

Then I finish things off with:

sheets = [ sheet_a, sheet_b ]
all_sheets = pd.concat(sheets,ignore_index=True).drop_duplicates().reset_index(drop=True)
all_sheets.to_csv('c:\\upload\\temp2.csv', columns=fields, index=False, sep=',', encoding='utf-8')

The creation of the
column/rows works fine, but I'm getting the following error for my attempt at creating a
Full Name
row by row:

Traceback (most recent call last):
File "", line 77, in <module>
sheet_a['Full Name'] = row['First Name'] + ' ' + row['Last Name']
NameError: name 'row' is not defined

Apart from this concatenation row by row thing, everything else in the script works flawlessly.

Answer Source

This should work:

In [1]: import pandas as pd

In [2]: df = pd.DataFrame({'First Name': ['Harry', 'Hermione'], 'Last Name': ['P
   ...: otter', 'Granger']})

In [3]: df
  First Name Last Name
0      Harry    Potter
1   Hermione   Granger

In [4]: df['Full Name'] = df['First Name'] + ' ' + df['Last Name']

In [5]: df
  First Name Last Name         Full Name
0      Harry    Potter      Harry Potter
1   Hermione   Granger  Hermione Granger

The trick here is that you refer to the entire column from your existing DataFrame using something like sheet_a['First Name'] that gives you a column of first names. You can then concatenate the column of first names to a column of last names and get a column of full names.