Modify pandas dataframe within iterrows loop

Question

I'm new to Python.

I am trying to add prefix (Serial number) to an element within a data frame using for loop, to do with data cleaning/preparation before analysis.

The code is

a=pd.read_excel('C:/Users/HP/Desktop/WFH/PowerBI/CMM data.xlsx','CMM_unclean')
a['Serial Number'] = a['Serial Number'].apply(str)
print(a.iloc[72,1])

for index,row in a.iterrows():
    if len(row['Serial Number']) == 6:
        row['Serial Number'] = 'SR0' + row['Serial Number']
        print(row['Serial Number'])

print(a.iloc[72,1])

The output is

C:\Users\HP\anaconda3\envs\test\python.exe C:/Users/HP/PycharmProjects/test/first.py
101306
SR0101306
101306

I don't understand why this is happening inside the for loop, value is changing, however outside it is the same.

seralouk · Accepted Answer · 2020-05-09 12:13:45Z

4

This will never change the actual dataframe named a.

TL;DR: The rows you get back from iterrows are copies that are no longer connected to the original data frame, so edits don't change your dataframe. However, you can use the index to access and edit the relevant row of the dataframe.

EXPLANATION

Why?

The rows you get back from iterrows are copies that are no longer connected to the original data frame, so edits don't change your dataframe. However, you can use the index to access and edit the relevant row of the dataframe.

The solution is this:

import pandas as pd

a = pd.read_excel("Book1.xlsx")
a['Serial Number'] = a['Serial Number'].apply(str)

a.head()
#    ID    Serial Number
# 0   1     SR0101306
# 1   2       1101306

print(a.iloc[0,1])
#101306

for index,row in a.iterrows():
    row = row.copy()
    if len(row['Serial Number']) == 6:
        # use the index and .loc method to alter the dataframe
        a.loc[index, 'Serial Number'] = 'SR0' + row['Serial Number']

print(a.iloc[0,1])
#SR0101306

edited May 9, 2020 at 12:13

answered May 9, 2020 at 12:08

seralouk

33.6k10 gold badges127 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Saurabh Arya Over a year ago

Thank you very much. That's great explanation

seralouk Over a year ago

glad that I could help. consider upvoting my answer if it helped

Wolf · Accepted Answer · 2020-05-09 12:00:22Z

1

In the documentation, I read (emphasis from there)

You should never modify something you are iterating over. This is not guaranteed to work in all cases. Depending on the data types, the iterator returns a copy and not a view, and writing to it will have no effect.

Maybe this means in your case that a copy is made and no reference used. So the change applies temporarily to the copy but not to the data in the data frame.

answered May 9, 2020 at 12:00

Wolf

10.3k8 gold badges72 silver badges116 bronze badges

1 Comment

Saurabh Arya Over a year ago

Thanks Mr Wolf this was very useful

MatsLindh · Accepted Answer · 2020-05-09 12:00:36Z

0

Since you're already using apply, you could do this straight inside the function you call apply with:

def fix_serial(n):
    n_s = str(n)

    if len(n_s) == 6:
        n_s = 'SR' + n_s

    return n_s

a['Serial Number'] = a['Serial Number'].apply(fix_serial)

answered May 9, 2020 at 12:00

MatsLindh

53.5k5 gold badges70 silver badges97 bronze badges

Collectives™ on Stack Overflow

Modify pandas dataframe within iterrows loop

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related