Replace, remove, edit with conditions

Replace empty cells

df['speciality'] = df['speciality'].fillna('Other')

Replace a value (the full value)

df['Your field'] = df['Your field'].replace(['Old value'],'New value')

Replace a part of string

df['Your field'] = df['Your field'].replace({'Old value': 'New value'}, regex=True)

Replace a string if it contains

df.loc[df['speciality'].str.contains('Researcher'), 'speciality'] = 'Research Scientist'

If not contains

Add ~:

df.loc[~df['speciality'].str.contains('Researcher'), 'speciality'] = 'Research Scientist'

Localize and remove a row

ind_drop = df[df['Your field'].apply(lambda x: x == ('A value'))].index
df = df.drop(ind_drop)

Localize and remove a row starting with ...

ind_drop = df[df['Your field'].apply(lambda x: x.startswith('A value'))].index
df = df.drop(ind_drop)

Localize and remove a row ending with ...

ind_drop = df[df['Your field'].apply(lambda x: x.endswith('A value'))].index
df = df.drop(ind_drop)

Localize and replace a full row

df.loc[(df['A field'] == 'TARGET')] = [[NewValue1, NewValue2, NewValue3]]

Localize a row according a regex and other field

df.loc[df[' Field1'].str.contains(pat='^place ', regex=True), 'Field2'] = 'Yes'

Remove some first characters

Here we delete the 2 first characters if the cell starts with a comma then a space.

df['Field'] = df['Field'].apply(lambda x: x[2:] if x.startswith(', ') else x)

Keep only some first characters

df['Field'] = df['Field'].apply(lambda x: x[:10])

Remove some last characters

df['DateInvoice'] = df['DateInvoice'].apply(lambda x: x[:-4] if x.endswith(' UTC') else x)

Remove the content from a field in another field

df['NewField'] = df.apply(lambda x : x['FieldToWork'].replace(str(x['FieldWithStringToRemove']), ''), axis=1)

Or with a regex, example to remove the content only if it is at the beginning of the field:

df['NewField'] = df.apply(lambda x : re.sub('^'+str(x['StringToRemove']), '', str(x['FieldToWork'])) if str(x['FieldToWork']).startswith(str(x['StringToRemove'])) else str(x['FieldToWork']), axis=1)

Edit with a condition

Increment a field if another field is empty.

df.loc[df['My field maybe empty'].notna(), 'Field to increment'] += 1

Fill a field if a field is greater or equal to another field.

df.loc[df['Field A'] >= df['Field B'], 'Field to fill'] = 'Yes'

Edit several fields in the same time.

df.loc[df['Field A'] >= df['Field B'], ['Field A to fill', 'Field B to fill']] = ['Yes', 'No']

Edit with several conditions

Condition "AND" (&)

df.loc[(df['My field maybe empty'].notna()) & (df['An integer field'] == 1) & (df['An string field'] != 'OK'), 'Field to increment'] += 1

Please replace "&" with a simple &.

Condition "OR" (|)

df.loc[(df['My field maybe empty'].notna()) | (df['An integer field'] == 1) | (df['An string field'] != 'OK'), 'Field to fill'] = 'Yes'

Edit with IN or NOT IN condition (as SQL)

Just use isin:

df.loc[df['Id field'].isin([531733,569732,652626]), 'Filed to edit'] = 'Yes'

And for NOT IN:

df.loc[df['Id field'].isin([531733,569732,652626]) == False, 'Filed to edit'] = 'No'

Replace string beginning with

df['id_commune'] = df['id_commune'].str.replace(r'(^75.*$)', '75056', regex=True)

 Remove letters

df['mobile'] = df['mobile'].str.extract('(\d+)', expand=False).fillna('')

Extract before or after a string

Example if Job='IT: DBA'

df['type'] = df['Job'].str.split(': ').str[0]
df['speciality'] = df['Job'].str.split(': ').str[1]

Remove all after a string

df_Files['new field'] = df_Files['old field'].str.replace("(StringToRemoveWithAfterToo).*","", regex=True)

Remove all before a string

df_Files['file'] = df_Files['file'].str.replace("^.*?_","_", regex=True)

Get in title case

df['firstname'] = df['firstname'].str.title()

Remove if contains less of n character (lenght)

df.loc[df['mobile'].str.len() < 6, 'mobile'] = ''

Fix scientific notation for phone numbers in Pandas, Tabulate and Excel export

df['Mobile Phone'] = df['Mobile Phone'].astype(str)
df['Mobile Phone'] = df['Mobile Phone'].fillna('')
df['Mobile Phone'] = df['Mobile Phone'].replace(['nan'], '')
df['Mobile Phone'] = df['Mobile Phone'].apply(lambda x: x[:-2] if x.endswith('.0') else x)