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 a string using regex

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

Replace a part of a string using regex and ignoring the case

df['Your field'] = df['Your field'].replace({'OlD valUe': 'New value'}, regex=True, case=False)

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'

Replace comma with point

df['myField'] = df['myField'].replace({'(?<=\d),(?=\d)': '.'}, regex=True)

Localize and remove rows

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

Localize and remove rows starting with ...

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

Localize and remove rows ending with ...

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

Localize and replace full rows

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

Localize rows according a regex and edit another field

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

Replace a field with values from another field with a condition

df['Field1'] = np.where(df['Field1']
    .apply(lambda x: x.startswith('A string in condition...')),
    df['Field2'], df['Field1'])

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)

Not start with

~df[phone].str.startswith('A')

Or:

~df[phone].str.startswith(('A', 'B'))

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'] = ''

Remove potential spaces before and after a string (trim)

Use .str.strip(), example:

df.loc[df['My field'].astype(str).str.isdigit() == False, 'My field'] = df['My field'].astype(str).str.strip()

Remove with a function (def)

def MyDeletion():
 
    # Eventually if your df does not exist when you create the function
    # global df
 
    ind_drop = df[df['My field'].apply(lambda x: x == ('My value'))].index
    df = df.drop(ind_drop)
 
...
 
MyDeletion()

Decode HTML special char

import html
df['My field'] = df['My field'].apply(lambda x: html.unescape(x))

Liens ou pièces jointes

France-Departements-Deformation.zip	[France-Departements-Deformation]	335 Ko
simple_countries.zip	[simple_countries]	1880 Ko