I would like to replace the data using replace after replacing rows and columns in Excel files using pandas.DataFrame.filna in Python 3.
I would like to ask you a question because the content I want to realize is not working well.
Could you give me guidance?
Excel Data
Excel data you want to achieve
If none is blank, I would like to replace the city column with "0".
You can replace it with 0 with the code below, but it is not fixed every time, so
How can I replace it when it changes indefinitely?
df=df.replace({':'0'}, regex=True)
df = df.replace ({'Kyoto':'0'}, regex = True)
df = df.replace ({'Sパo Paulo': '0'}, regex = True)
code
import pandas as pd
from openpyxl import Workbook, load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
pd.set_option('display.unicode.east_asian_width', True)
wb=load_workbook("test.xlsx")
ws=wb ['Sheet1']
# convert to pandas dataframe
data=ws.values
cols=next(data)
df=pd.DataFrame(data,columns=cols)
# fill in the forward
df[['No', 'Country']=df[[['No', 'Country']].fillna(method='ffill')
df.No = df.No.astype(int)
# select data
# dfx = df [df ['Disneyland with/without'] == 'none']
# dfx_2=dfx.replace({dfx:'0', regex=True)
df = df.replace({':'0'}, regex = True)
df = df.replace ({'Kyoto':'0'}, regex = True)
df = df.replace ({'Sパo Paulo': '0'}, regex = True)
print(df.to_markdown(index=False))
# save to a new sheet
wb = Workbook()
ws = wb.active
for r in dataframe_to_rows (df, index=False, header=True):
ws.append(r)
wb.save('testchange.xlsx')
I was able to extract the conditions as I wanted using isin.
You will be able to extract data that matches your criteria from the data frame.
before change
df=df.replace({':'0'}, regex=True)
df = df.replace ({'Kyoto':'0'}, regex = True)
df = df.replace ({'Sパo Paulo': '0'}, regex = True)
modified
df.loc [df['With/With Disneyland'.isin('None',', None))', 'City'] = 0
Reference Page
Describes how to use "isin" in pandas
© 2024 OneMinuteCode. All rights reserved.