Replace Method of python to remove the text from a particular column in python is not working

Question

I want to replace the "magneticfields\nD" in the Magnetic Declination column. I am generating this csv file from API. File look like this.

CSV FILE GENERATE FROM API

import requests
import json
import pandas as pd
import os
import csv
from pathlib import Path

parameters = {
    "latd": 88, # [deg]
    "latm": 00, # [deg]
    "lats": 00, # [deg]
    "lond": 75, # [deg]
    "lonm": 00, # [deg]
    "lons": 00, # [deg]
    "elev" : 00, # [km]
    "year" : None, # [YYYY]
    "month" : '07', # [MM]
    "day": '01', # [DD]
    "Ein": 'D'  # [Model]
}

hostname = "https://api.geomagnetism.ga.gov.au/agrf"
df_1=pd.DataFrame()
for year in range(1985, 2025):
    try:
        parameters["year"] = year
        response = requests.get(hostname, params= dict(parameters, ps=str(year)))
        # extract JSON payload of response as Python dictionary
        json_payload = response.json()
        # raise an Exception if we encoutnered any HTTP error codes like 404
        response.raise_for_status()
    except requests.exceptions.ConnectionError as e:
        # handle any typo errors in url or endpoint, or just patchy internet connection
        print(e)
    except requests.exceptions.HTTPError as e:
        # handle HTTP error codes in the response
        print(e, json_payload['error'])
    except requests.exceptions.RequestException as e:
        # general error handling
        print(e, json_payload['error'])
    else:
        json_payload = response.json()
        #print(json.dumps(json_payload, indent=4, sort_keys=True))
        df = pd.DataFrame(json_payload)
        print(df)
        print(df.loc[['D'],['magneticFields']])
        new_row = {
            "SourceFile": hostname,
            "Year": year,
            "Magnetic Declination": df.loc[['D'],['magneticFields']],
            "Latitude": 88,
            "Longitude": 75
        }
        df_1 = df_1.append(new_row, ignore_index=True)
    df_1 = df_1[['Year', 'Latitude', 'Longitude','Magnetic Declination','SourceFile']]
    #df_1['Magnetic Declination'] = df_1['Magnetic Declination'].apply(lambda x: x.replace(r"magneticFields\nD ", ""))
    #df_1['Magnetic Declination'] = df_1['Magnetic Declination'].str.replace(r"..magneticFields\nD.....","",regex=True)
    #df_1['Magnetic Declination'] = df_1['Magnetic Declination'].str.replace('magneticFields\nD', '').astype(str)
    df_1["Magnetic Declination"] = df_1["Magnetic Declination"].apply(lambda x: x.replace(" deg", ""))
    df_1.to_csv('magnetic_declination_australia_1.csv',index=False)

Tried all the methods but none of them is working.

df_1['Magnetic Declination'] = df_1['Magnetic Declination'].apply(lambda x: x.replace(r"magneticFields\nD ", ""))
df_1['Magnetic Declination'] = df_1['Magnetic Declination'].str.replace(r"..magneticFields\nD.....","",regex=True)
df_1['Magnetic Declination'] = df_1['Magnetic Declination'].str.replace('magneticFields\nD', '').astype(str)

Output should look this.

FinalOutput

Can anyone help me how to fixed this? csv file that is generating from API attached here.

Does this answer your question? [How to replace text in a column of a Pandas dataframe?](https://stackoverflow.com/questions/28986489/how-to-replace-text-in-a-column-of-a-pandas-dataframe) — imxitiz, Jul 08 '21 at 07:11
@kshitiz It is not working. I check all stack overflow answers. Please read the full question. — gupta, Jul 08 '21 at 07:22
Doesn't you just want to delete "magneticfields\nD" from Magnetic Declination column? — imxitiz, Jul 08 '21 at 07:31
@kshitiz yes, I want to delete this from column. But the problem is that replace method is not working. For testing you need to run this code. — gupta, Jul 08 '21 at 07:43
Have you checked spelling mistakes in replace method or some other common mistakes, because `print(df["Magnetic Declination"].str.replace("magneticFields\nD",""))` worked for me. Please check those mistakes and ask if it doesn't worked then also! — imxitiz, Jul 08 '21 at 07:46
I have tried `https://drive.google.com/file/d/1XL3V8yYJZa_3zuXPitUaZV3k0tqErANi/view?usp=sharing` for this file! — imxitiz, Jul 08 '21 at 07:47
@kshitiz, just run the python code. It generate csv and then see weather it removed or not??? — gupta, Jul 08 '21 at 08:35

score 0 · Answer 1 · edited Jul 09 '21 at 08:16

You are doing wrong in df.loc[['D'],['magneticFields']]. You are using pandas.core.frame.DataFrame type as string. df.loc[['D'],['magneticFields']] returns the DataFrame and you are using this this directly without changing it.

df.loc[['D'],['magneticFields']] returns you

    magneticFields
D       69.650 deg

and now we can get magneticFields column and D(First row) from this DataFrame, like from other normal DataFrame by doing df.loc[['D'],['magneticFields']]["magneticFields"][0].

Here's your final code:

df = pd.DataFrame(json_payload)
print(df)
print(df.loc[['D'],['magneticFields']]["magneticFields"][0])
new_row = {
    "SourceFile": hostname,
    "Year": year,
    "Magnetic Declination": df.loc[['D'],['magneticFields']]["magneticFields"][0],
    "Latitude": 88,
    "Longitude": 75
}
df_1 = df_1.append(new_row, ignore_index=True)

And you don't have to do any other fancy things with this like you have done haha.

Also don't forget to mark this as accepted, if it helps you.

Replace Method of python to remove the text from a particular column in python is not working

1 Answers1