pandas replace dataframe value by other columns value in the same row

670
February 07, 2017, at 9:09 PM

I have this pandas dataframe

BU       |   DATA1      DATA2
01-TT        zone 01   noData
02-FF        noData    zone 02
....

and I need to replace the "noData" string by the corresponding row in the column BU, but only using the two first characters and adding "zone" word

BU       |   DATA1      DATA2
01-TT        zone 01    zone 01
02-FF        zone 02    zone 02
....

Thanks a lot

Answer 1

You can use mask for replace True values by numpy array created by numpy.repeat:

df = df.set_index('BU')
arr = np.repeat('zone ' + df.index.str[:2], len(df.columns)).values.reshape(df.shape)
print (arr)
[['zone 01' 'zone 01']
 ['zone 02' 'zone 02']]
df = df.mask(df == 'noData', arr)
print (df.reset_index())
      BU    DATA1    DATA2
0  01-TT  zone 01  zone 01
1  02-FF  zone 02  zone 02

Timings:

#[20000 rows x 3 columns]
df = pd.concat([df]*10000).reset_index(drop=True)
print (df)
df1 = df.copy()
def jez(df):
    df = df.set_index('BU')
    df = df.mask(df == 'noData', np.repeat('zone ' + df.index.str[:2], len(df.columns)).values.reshape(df.shape))
    return (df.reset_index())
def ed(df):
    cols = df.columns[df.columns.str.contains('DATA')]
    df[cols] = df[cols].mask(df[cols].apply(lambda x: x.str.contains('noData')), 'zone ' + df['BU'].str[:2], axis=0)
    return df

print (jez(df))
print (ed(df1))
In [219]: %timeit (jez(df))
100 loops, best of 3: 14.2 ms per loop
In [220]: %timeit (ed(df1))
10 loops, best of 3: 46.3 ms per loop
Answer 2

General solution:

In [135]:
cols = df.columns[df.columns.str.contains('DATA')]
df[cols] = df[cols].mask(df[cols].apply(lambda x: x.str.contains('noData')), 'zone ' + df['BU'].str[:2], axis=0)
df
Out[135]:
      BU    DATA1    DATA2
0  01-TT  zone 01  zone 01
1  02-FF  zone 02  zone 02

Here we first determine the cols that contain DATA, then we call mask just on these cols and using a boolean mask, replace just those rows that meet the condition and overwrite

Rent Charter Buses Company
READ ALSO
Processing objects in order in docx

Processing objects in order in docx

I want to process objects in the order they are written in a word documentObjects I have encountered are paragraphs, text in paragraphs, runs in paragraphs, text in runs, tables, and paragraphs in a table's cells

436
Change package import path

Change package import path

I have the following situation:

315
Asking the user for input until they give a valid response

Asking the user for input until they give a valid response

I am writing a program that must accept input from the user

377
Local Backup of MySQL DB with Sequel Pro

Local Backup of MySQL DB with Sequel Pro

I have a MySQL database stored on AWSI'm using Sequel Pro to manage it

401