open the excel edited and save it

user_16832397 0
Viewed 43

I have an excel file form which is likely below in the same folder for my python program: Note: / is the divider for each cell.

*Commodity/Country/...

1205.10.00.10 — Low erucic/ Canada/

1210.10.00.00 — Hop cones / Spain/*

I want to split the first column to and save it in csv file:

120510/Canada/...

121000/Spain/...

I wrote the code below, Could you do me a favor and correct it??

   import pandas as pd
    excel_file_path = 'Canada_scrapping_result.xlsx'
    df = pd.read_excel(excel_file_path)    
    for A1 in df.columns:
      df[A1] = df[A1].str.replace(r'\W',"")
    df.to_excel("Remove_characters.xlsx")
pandas web-scraping python
1 Answers

I have written an answer which assumes that you want to remove the ".10.00." from the middle of each commodity and that this value does not change. If this is a value which changes let me know.

I have tested this code and it does the job.

import pandas as pd


df = pd.read_excel(excel_file_path) 


for i in range(len(df[df.columns[0]])):
    string = df["commodity"][i]
    split_str = string.split("—")  # Splits string at "—"
    number = split_str[0] 
    df['commodity'][i] = number.replace(".10.00.", "") 
    df.to_csv("Remove_characters.csv")

This gives me an answer in the format that you ask for in your post. Hope this helps! :)

user_132244950
Related