import pandas as pd
import json

# Load the initial CSV datasets
df1 = pd.read_csv('dataset1.csv')
df2 = pd.read_csv('dataset2.csv')

# Combine the datasets into a single DataFrame
df = pd.concat([df1, df2])

# Convert the DataFrame to JSON (or XML)
json_data = df.to_json(orient='records')


# Save the JSON data to a file
with open('data_backup.json', 'w') as file:
    file.write(json_data)

# Remove data for certain DAB radio stations
stations_to_remove = ['NZ02553847', 'SE213515', 'NT05399374', 'NT252675908']
df = df[~df['NGR'].isin(stations_to_remove)]

# Extract 'EID' information into a new column
df['DAB_multiplexes'] = df['EID'].apply(lambda x: 'C18A' if 'C18A' in x else 'C18F' if 'C18F' in x else 'C188' if 'C188' in x else x)

# Renaming columns
df.rename(columns={'In-Use Ae Ht': 'Aerial height (m)', 'In-Use ERP Total': 'Power (kW)'}, inplace=True)