Pandas dataframe replace does not replace values

Question

I wrote a code that takes value in cell, changes it and the it should replace value in that cell with new value. I have .replace function fit dummy dataframe, and it works, but for my example it does not work. Old value and new value are very long strings. I have tried with inplace=True and without it.

My goal is to change ServiceDirection value.

You can download the data from here:

https://www.sendspace.com/file/7soufd

old value looks like this: ...ype=1|ServiceDirection=2|CmtsMdIfIn...

new value looks like this: ype=1|ServiceDirection=DS|CmtsMdIfIn

This is the code:

data = pd.read_csv('data.csv')

def third_task():

    new_data = data

    for column in data:

        for row in data[column]:

            if 'ServiceDirection=1' in str(row):

                new_row = str(row).replace('ServiceDirection=1', 'ServiceDirection=DS')
                new_data = data.replace(str(row), new_row)

            elif 'ServiceDirection=2' in str(row):

                new_row = str(row).replace('ServiceDirection=2', 'ServiceDirection=US')
                new_data = data.replace(str(row), new_row)


    export_csv = new_data.to_csv(r'C:\Users\Pc\Desktop\export_dataframe1.csv', index = None, header=False)

    return new_data

print(third_task())

I have also tried to do this:

df.replace(row, result)

Instead of this: data[column] = data[column].replace(str(row), str(result), inplace=True)

But still does not work, it always returns dataframe with old values

@Terry I updated the question, you have the database in there — taga
– taga, Commented Jun 28, 2019 at 22:43
your .csv is so messy, What are you trying to do with this data? — Terry
– Terry, Commented Jun 28, 2019 at 23:04

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

I do not know if you are trying to structure your data. If so, I did it this way.

df = pd.read_csv('data.csv', sep = '|', header = None)
df.columns = df.iloc[0, :].apply(lambda x: x.split('=')[0])
df = df.apply(lambda x: x.str.split('=').str.get(1))
df.head()

    ServiceSlaDelayPkts ServiceTimeCreated  CmtsMdIfName    ServiceSlaDropPkts  ServiceGateId   ServiceClassName    CmtsSysUpTime   ServicePktsPassed   ServiceIdentifier   ServiceDsMulticast  ... ServiceTimeActive   CmMacAddr   ServiceOctetsPassed ServiceAppId    CmtsHostName    RecCreationTime RecType ServiceDirection    CmtsMdIfIndex   ,,,
0   0   4199286300  Cable1/0/0  0   0   USXnet  4294746100  7710    13  0   ... 954374  aaaa.bbbb.cccc  1033134 7   ibis-instruments-1.com  1555675968867   1   2   1001    NaN
1   0   4199286300  Cable0/0/0  0   0   DSXnet  4294746100  287 14  0   ... 954374  aaaa.bbbb.cccc  96868   7   ibis-instruments-1.com  1555675968867   1   1   1001    NaN
2   0   4199290300  Cable1/0/0  0   0   USXnet  4294746100  9527731 15  0   ... 954284  dddd.bbbb.cccc  1471545334  7   ibis-instruments-1.com  1555675968867   1   2   1001    NaN
3   0   4199290300  Cable0/0/0  0   0   DSXnet  4294746100  128871002   16  0   ... 2968    dddd.bbbb.cccc  188935852314    7   ibis-instruments-1.com  1555675968867   1   1   1001    NaN
4   0   4260449700  Cable0/0/0  0   0   USXnet  4294746100  452297  17  0   ... 342739  dddd.bbbb.mmmm  77459364    7   ibis-instruments-2.com  1555675968868   1   2   1001    NaN

Edit: Add dots and upper case on CmMacAddr column

df['CmMacAddr'] = df['CmMacAddr'].str.replace('.', '').str.upper()

df['CmMacAddr'] = df['CmMacAddr'].apply(lambda x: '.'.join(x[i:i+2] for i in range(0,len(x), 2)))

Explanation

The first line of code read the .csv separating the columns by '|' (the default is comma(,)).
The second line i rename the columns names, because your csv dont have header, i selected only one row (df.iloc[0, :]) and then I cover all values(apply) spliting by '=' and taking the values from index 0.
The last line of code is very similar to the second, but i take se second value from each split and replace all rows with this new value.

If you execute row by row my code and adding df.head() between them you will see the evolution :)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 28, 2019 at 23:20

Terry

2,8212 gold badges16 silver badges30 bronze badges

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

4 Comments

taga Over a year ago

CmMacAddr column is like this dddd.bbbb.mmmm, and needs to be like this DD.DD.BB.BB.MM.MM Can you please update your answer and explain me this df.columns = df.iloc[0, :].apply(lambda x: x.split('=')[0]) df = df.apply(lambda x: x.str.split('=').str.get(1)) Your answer is amazing, but I do not know why did you do this?

taga Over a year ago

Your answer is amazing, but my data needs to have the same structure like on the start.

taga Over a year ago

As I said, your answer is amazing , and Ill put it as a correct, but If you can please help me without changing the data structure, because thats how I need to solve this. Thanks

Terry Over a year ago

I do not know how to do it that way mate. I hope someone shows up with the correct answer

Collectives™ on Stack Overflow

Pandas dataframe replace does not replace values

1 Answer 1

Edit: Add dots and upper case on CmMacAddr column

Explanation

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit: Add dots and upper case on CmMacAddr column

Explanation

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related