CODEWITHSUNDEEP
pythonpandas
hakukou
hakukou

Reputation: 111

Pandas replace the values of multiple columns

If the match value is equal to the sample_input,The value in the sample_input is replaced. The merge method now used can match, But don't know how to replace it. There are many duplicate values in the sample being replaced.

The sample_data I used upload to the github. sample_data_input

import pandas as pd

#Read file
match = pd.read_excel('match.xlsx', sheet_name='Sheet1')
replace = pd.read_excel('replace.xlsx', sheet_name='Sheet1') #replace value
sample_input = pd.read_excel('sample_input.xlsx', sheet_name='Sheet1') #raw file

#column
match_col_n1 = ['e', 'i', 'j', 'k', 'l', 'n', 'label']
match_col_n2 = ['e', 'i', 'j', 'k', 'l', 'n']
replace_col_n = ['i', 'j', 'k', 'l', 'label'] #replace
sample_input_col_n = ['a', 'b', 'c', 'd', 'e', 'f',
                      'g', 'h', 'i', 'j', 'k', 'l',
                      'm', 'n']

#DataFrame
match_data = pd.DataFrame(match,  columns=match_col_n1)
replace_data = pd.DataFrame(replace, columns=replace_col_n)
sample_input_data = pd.DataFrame(sample_input,  columns=sample_input_col_n)

# tmp
tmp = sample_input_data.merge(match_data, how='left', on=None, 
                 left_on=match_col_n2, right_on=match_col_n2, 
                 left_index=False, right_index=False, sort=False, 
                 suffixes=('_x', '_y'), copy=True, 
                 indicator=False, validate=None)

sample_input_data['label'] = tmp['label']

#for num in match_data.index.values:
#    label = match_data.loc[num, 'label']
#    sample_input_data[sample_input_data['label'] == label][replace_col_n] = replace_data.iloc[num, :].values


sample_input_data = sample_input_data.to_excel('output.xlsx', index=False)

Upvotes: 0

Views: 333

Answers (1)

ASH
ASH

Reputation: 20362

Here's a pretty straightforward way of comparing and contrasting two Excel files.

import pandas as pd
import numpy as np

# Next, read in both of our excel files into dataframes

df1 = pd.read_excel('C:\\your_path\\Book1.xlsx', 'Sheet1', na_values=['NA'])
df2 = pd.read_excel('C:\\your_path\\Book2.xlsx', 'Sheet1', na_values=['NA'])

# Order by account number and reindex so that it stays this way.


df1.sort_index(by=["H1"])
df1=df1.reindex()
df2.sort_index(by=["H1"])
df2=df2.reindex()

# Create a diff function to show what the changes are.

def report_diff(x):
    return x[0] if x[0] == x[1] else '{} ---> {}'.format(*x)

# Merge the two datasets together in a Panel . I will admit that I haven’t fully grokked the panel concept yet but the only way to learn is to keep pressing on!

diff_panel = pd.Panel(dict(df1=df1,df2=df2))

# Once the data is in a panel, we use the report_diff function to highlight all the changes. I think this is a very intuitive way (for this data set) to show changes. It is relatively simple to see what the old value is and the new one. For example, someone could easily check and see why that postal code changed for account number 880043.

diff_output = diff_panel.apply(report_diff, axis=0)
diff_output.tail()


# One of the things we want to do is flag rows that have changes so it is easier to see the changes. We will create a has_change function and use apply to run the function against each row.

def has_change(row):
    if "--->" in row.to_string():
        return "Y"
    else:
        return "N"


diff_output['has_change'] = diff_output.apply(has_change, axis=1)
diff_output.tail()

# It is simple to show all the columns with a change:

diff_output[(diff_output.has_change == 'Y')]


# Finally, let’s write it out to an Excel file:

diff_output[(diff_output.has_change == 'Y')].to_excel('C:\\your_path\\diff.xlsx')

https://pbpython.com/excel-diff-pandas.html

Upvotes: 1

Related Questions