If you don't know which are your file encoding, I think that the fastest approach is to open the file on a text editor, like Notepad++ to check how your file are encoding.
Then you go to the python documentation and look for the correct codec to use.
In your case , ANSI, the codec is 'mbcs', so your code will look like these
df_a = pd.read_csv('file.csv',sep=';',encoding='mbcs')
If you don't know which are your file encoding, I think that the fastest approach is to open the file on a text editor, like Notepad++ to check how your file are encoding.
Then you go to the python documentation and look for the correct codec to use.
In your case , ANSI, the codec is 'mbcs', so your code will look like these
df_a = pd.read_csv('file.csv',sep=';',encoding='mbcs')
When EmpfängerStraße shows up as Empf„ngerStraáe when decoded as ”ANSI”, or more correctly cp1250 in this case, then the actual encoding of the data is most likely cp850:
print 'Empf„ngerStraáe'.decode('utf8').encode('cp1250').decode('cp850')
Or Python 3, where literal strings are already unicode strings:
print("Empf„ngerStraáe".encode("cp1250").decode("cp850"))
python - ANSI Encoding for pandas on Google Colab? - Stack Overflow
python - Pandas- enabling ANSI encoding for read excel - Stack Overflow
python - pandas to_csv: ascii can't encode character - Stack Overflow
python Convert Encoding:LookupError: unknown encoding: ansi - Stack Overflow
Check the answer here
It's a much simpler solution:
newdf.to_csv('filename.csv', encoding='utf-8')
You have some characters that are not ASCII and therefore cannot be encoded as you are trying to do. I would just use utf-8 as suggested in a comment.
To check which lines are causing the issue you can try something like this:
def is_not_ascii(string):
return string is not None and any([ord(s) >= 128 for s in string])
df[df[col].apply(is_not_ascii)]
You'll need to specify the column col you are testing.
There's no ansi encoding in Python Standard Encodings.
Choose appropriate encodings from following link: Standard Encodings
OK,I find the answer.Thanks to @falsetru
#coding:utf-8
import chardet
def convertEncoding(from_encode,to_encode,old_filepath,target_file):
f1=file(old_filepath)
content2=[]
while True:
line=f1.readline()
content2.append(line.decode(from_encode).encode(to_encode))
if len(line) ==0:
break
f1.close()
f2=file(target_file,'w')
f2.writelines(content2)
f2.close()
convertFile = open('1234.csv','r')
data = convertFile.read()
convertFile.close()
convertEncoding(chardet.detect(data)['encoding'], "utf-8", "1234.csv", "1234_bak.csv")
I have a Python Code that someone wrote for me to use, but they're now gone - and it's now broken - for who knows what reason, and I CANNOT seem to figure out this Python stuffs to save my damn life! Can SOMEONE please, please, PLEASE tell me - in real NON-CODING TYPE PERSON, step by step terms - how to fix this?? I included the code and then the error below.
(The code is suppose to be taking a bunch of data from spreadsheets and relabeling and combining it to one sheet for the entire month in question.)
Here is the coding:
import pandas as pd
import numpy as np
import glob
import pyautogui as pg
current_file = "2024 02"
location1 = fr"C:\Users\megan\Documents\Deposits\{current_file}\*.txt"
location2 = fr"C:\Users\megan\Documents\Deposits\{current_file}\retail orders.csv"
location3 = fr"C:\Users\megan\Documents\Deposits\{current_file}\wholesale orders.csv"
location4 = fr"C:\Users\megan\Documents\Sales Tax\zReports\{current_file} Sales Tax report - full.xlsx"
files = glob.glob(location1)
df_list = []
for f in files:
print(f)
txt = pd.read_csv(f)
df_list.append(txt)
ccfile = pd.concat(df_list)
ccoriginal = ccfile
ccfile["category"] = ccfile["Transaction Status"].map({
"Settled Successfully":"Settled Successfully",
"Credited":"Credited",
"Declined":"Other",
"General Error":"Other"}).fillna("Other")
ccfile = ccfile[ccfile["category"] != "Other"]
ccfile = ccfile[["Transaction ID","Transaction Status","Settlement Amount","Submit Date/Time","Authorization Code","Reference Transaction ID","Address Verification Status","Card Number","Customer First Name","Customer Last Name","Address","City","State","ZIP","Country","Ship-To First Name","Ship-To Last Name","Ship-To Address","Ship-To City","Ship-To State","Ship-To ZIP","Ship-To Country","Settlement Date/Time","Invoice Number","L2 - Freight","Email"]]
ccfile.rename(columns= {"Invoice Number":"Order Number"}, inplace=True)
ccfile["Order Number"] = ccfile["Order Number"].fillna(999999999).astype(np.int64)
ccfile.rename(columns= {"L2 - Freight":"Freight"}, inplace=True)
def catego(x):
if x["Transaction Status"] == "Credited":
return
if x["Order Number"] < 103000:
return "Wholesale"
if x["Order Number"] == 999999999:
return "Clinic"
return "Retail"
ccfile["type"] = ccfile.apply(lambda x: catego(x), axis=1)
def values(x):
if x["Transaction Status"] == "Credited":
return -1.0
return 1.0
ccfile["deposited"] = ccfile.apply(lambda x: values(x), axis=1) * ccfile["Settlement Amount"]
ccfile.sort_values(by="type", inplace=True)
# work with excel files from website downloads
# work with excel files from website downloads
columns_to_use = ["Order Number","Order Date","Order Status","First Name (Billing)","Last Name (Billing)","Company (Billing)","Address 1&2 (Billing)","City (Billing)","State Code (Billing)","Postcode (Billing)","Country Code (Billing)","Email (Billing)","Phone (Billing)","First Name (Shipping)","Last Name (Shipping)","Address 1&2 (Shipping)","City (Shipping)","State Code (Shipping)","Postcode (Shipping)","Country Code (Shipping)","Payment Method Title","Cart Discount Amount","Order Subtotal Amount","Shipping Method Title","Order Shipping Amount","Order Refund Amount","Order Total Amount","Order Total Tax Amount","SKU","Item #","Item Name","Quantity","Item Cost","Coupon Code","Discount Amount"]
retail_orders = pd.read_csv(location2)
retail_orders = retail_orders[columns_to_use]
wholesale_orders = pd.read_csv(location3)
wholesale_orders = wholesale_orders[columns_to_use]
details = pd.concat([retail_orders, wholesale_orders]).fillna(0.00)
details.rename(columns= {"Order Total Tax Amount":"SalesTax"}, inplace=True)
details.rename(columns= {"State Code (Billing)":"State - billling"}, inplace=True)
print(details)
# details["Item Cost"] = details["Item Cost"].str.replace(",","") # I don't know if needs to be done yet or not
#details["Item Cost"] = pd.to_numeric(details.Invoiced)
details["Category"] = details.SKU.map({"CT3-A-LA-2":"CT","CT3-A-ME-2":"CT","CT3-A-SM-2":"CT","CT3-A-XS-2":"CT","CT3-P-LA-1":"CT","CT3-P-ME-1":"CT",
"CT3-P-SM-1":"CT","CT3-P-XS-1":"CT","CT3-C-LA":"CT","CT3-C-ME":"CT","CT3-C-SM":"CT","CT3-C-XS":"CT","CT3-A":"CT","CT3-C":"CT","CT3-P":"CT",
"CT - Single - Replacement - XS":"CT","CT - Single - Replacement - S":"CT","CT - Single - Replacement - M":"CT","CT - Single - Replacement - L":"CT"}).fillna("OTC")
details["Row Total"] = details["Quantity"] * details["Item Cost"]
taxed = details[["Order Number","SalesTax","State - billling"]]
taxed = taxed.drop_duplicates(subset=["Order Number"])
ct = details.loc[(details["Category"] == "CT")]
otc = details.loc[(details["Category"]=="OTC")]
ct_sum = ct.groupby(["Order Number"])["Row Total"].sum()
ct_sum = ct_sum.reset_index()
ct_count = ct.groupby(["Order Number"])["Quantity"].sum()
ct_count = ct_count.reset_index()
otc_sum = otc.groupby(["Order Number"])["Row Total"].sum()
otc_sum = otc_sum.reset_index()
otc_count = otc.groupby(["Order Number"])["Quantity"].sum()
otc_count = otc_count.reset_index()
# combine CT and OTC columns together
count_merge = ct_count.merge(otc_count, on="Order Number", how="outer").fillna(0.00)
count_merge.rename(columns= {"Quantity_x":"CT Count"}, inplace = True)
count_merge.rename(columns = {"Quantity_y":"OTC Count"}, inplace = True)
merged = ct_sum.merge(otc_sum, on="Order Number", how="outer").fillna(0.00)
merged.rename(columns = {"Row Total_x":"CT"}, inplace = True)
merged.rename(columns = {"Row Total_y":"OTC"}, inplace = True)
merged = merged.merge(taxed, on="Order Number", how="outer").fillna(0.00)
merged = merged.merge(count_merge, on="Order Number", how="outer").fillna(0.00)
merged["Order Number"] = merged["Order Number"].astype(int)
# merge CT, OTC amounts with ccfile
complete = ccfile.merge(merged, on="Order Number", how="left")
complete = complete.sort_values(by=["Transaction Status","Order Number"])
complete["check"] = complete.apply(lambda x: x.deposited - x.CT - x.OTC - x.Freight - x.SalesTax, axis=1).round(2)
# save file
# save file
with pd.ExcelWriter(location4) as writer:
complete.to_excel(writer,sheet_name="cc Deposit split")
ccfile.to_excel(writer, sheet_name="cc deposit")
taxed.to_excel(writer, sheet_name="taxes detail")
retail_orders.to_excel(writer, sheet_name="Retail data")
wholesale_orders.to_excel(writer, sheet_name="wholesale data")
details.to_excel(writer, sheet_name="Full Details")
This is the error I get:
PS C:\Users\megan> & C:/Users/megan/AppData/Local/Microsoft/WindowsApps/python3.8.exe "c:/Users/megan/Documents/Python scripts/TEST - Monthly Sales Tax"
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 01.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 02.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 03.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 04.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 05.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 06.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 07.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 08.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 09.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 10.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 11.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 12.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 13.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 14.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 15.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 16.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 17.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 18.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 19.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 20.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 21.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 22.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 23.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 24.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 25.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 26.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 27.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 28.txt
C:\Users\megan\Documents\Deposits\2024 02\dep 2024 02 29.txt
Traceback (most recent call last):
File "c:/Users/megan/Documents/Python scripts/TEST - Monthly Sales Tax", line 60, in <module>
retail_orders = pd.read_csv(location2)
File "C:\Users\megan\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\pandas\io\parsers\readers.py", line 912, in read_csv
return _read(filepath_or_buffer, kwds)
File "C:\Users\megan\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\pandas\io\parsers\readers.py", line 577, in _read
parser = TextFileReader(filepath_or_buffer, **kwds)
File "C:\Users\megan\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\pandas\io\parsers\readers.py", line 1407, in __init__
self._engine = self._make_engine(f, self.engine)
File "C:\Users\megan\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\pandas\io\parsers\readers.py", line 1679, in _make_engine
return mapping[engine](f, **self.options)
File "C:\Users\megan\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\pandas\io\parsers\c_parser_wrapper.py", line 93, in __init__
self._reader = parsers.TextReader(src, **kwds)
File "pandas\_libs\parsers.pyx", line 550, in pandas._libs.parsers.TextReader.__cinit__
File "pandas\_libs\parsers.pyx", line 639, in pandas._libs.parsers.TextReader._get_header
File "pandas\_libs\parsers.pyx", line 850, in pandas._libs.parsers.TextReader._tokenize_rows
File "pandas\_libs\parsers.pyx", line 861, in pandas._libs.parsers.TextReader._check_tokenize_status
File "pandas\_libs\parsers.pyx", line 2021, in pandas._libs.parsers.raise_parser_error
UnicodeDecodeError: 'utf-8' codec can't decode byte 0x92 in position 22209: invalid start byte