Efficiently replace values from a column to another column Pandas DataFrame

by

Using np.where is faster. Using a similar pattern as you used with replace:

df['col1'] = np.where(df['col1'] == 0, df['col2'], df['col1'])
df['col1'] = np.where(df['col1'] == 0, df['col3'], df['col1'])

However, using a nested np.where is slightly faster:

df['col1'] = np.where(df['col1'] == 0, 
                      np.where(df['col2'] == 0, df['col3'], df['col2']),
                      df['col1'])

Timings

Using the following setup to produce a larger sample DataFrame and timing functions:

df = pd.concat([df]*10**4, ignore_index=True)

def root_nested(df):
    df['col1'] = np.where(df['col1'] == 0, np.where(df['col2'] == 0, df['col3'], df['col2']), df['col1'])
    return df

def root_split(df):
    df['col1'] = np.where(df['col1'] == 0, df['col2'], df['col1'])
    df['col1'] = np.where(df['col1'] == 0, df['col3'], df['col1'])
    return df

def pir2(df):
    df['col1'] = df.where(df.ne(0), np.nan).bfill(axis=1).col1.fillna(0)
    return df

def pir2_2(df):
    slc = (df.values != 0).argmax(axis=1)
    return df.values[np.arange(slc.shape[0]), slc]

def andrew(df):
    df.col1[df.col1 == 0] = df.col2
    df.col1[df.col1 == 0] = df.col3
    return df

def pablo(df):
    df['col1'] = df['col1'].replace(0,df['col2'])
    df['col1'] = df['col1'].replace(0,df['col3'])
    return df

I get the following timings:

%timeit root_nested(df.copy())
100 loops, best of 3: 2.25 ms per loop

%timeit root_split(df.copy())
100 loops, best of 3: 2.62 ms per loop

%timeit pir2(df.copy())
100 loops, best of 3: 6.25 ms per loop

%timeit pir2_2(df.copy())
1 loop, best of 3: 2.4 ms per loop

%timeit andrew(df.copy())
100 loops, best of 3: 8.55 ms per loop

I tried timing your method, but it’s been running for multiple minutes without completing. As a comparison, timing your method on just the 6 row example DataFrame (not the much larger one tested above) took 12.8 ms.

More Related Contents:

  1. Renaming column names in Pandas
  2. How to replace text in a string column of a Pandas dataframe?
  3. Replacing few values in a pandas dataframe column with another value
  4. replace() method not working on Pandas DataFrame
  5. Pandas: replace substring in string
  6. Replace all occurrences of a string in a pandas dataframe (Python)
  7. Removing prefix from column names in Pandas
  8. How to replace negative numbers in Pandas Data Frame by zero
  9. Replace invalid values with None in Pandas DataFrame
  10. How to iterate over rows in a DataFrame in Pandas
  11. Difference between map, applymap and apply methods in Pandas
  12. Pandas filtering for multiple substrings in series
  13. Python pandas insert list into a cell
  14. Combine two pandas Data Frames (join on a common column)
  15. Pandas DataFrame Groupby two columns and get counts
  16. Pandas dataframe get first row of each group
  17. pandas .at versus .loc
  18. Selection with .loc in python
  19. How to flatten a hierarchical index in columns
  20. Add column to dataframe with constant value
  21. python pandas – creating a column which keeps a running count of consecutive values
  22. Turn Pandas Multi-Index into column
  23. What is the fastest and most efficient way to append rows to a DataFrame?
  24. Creating an empty Pandas DataFrame, and then filling it
  25. Check if rows in one dataframe exist in another dataframe
  26. Pretty print a pandas dataframe in VS Code
  27. Pandas – Interleave / Zip two DataFrames by row
  28. Pandas custom function to find whether it is the 1st, 2nd etc Monday, Tuesday, etc – all suggestions welcome
  29. How to use str.contains() with multiple expressions in pandas dataframes
  30. how to understand closed and label arguments in pandas resample method?

Leave a Comment