-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathconvert_Airtel.py
95 lines (75 loc) · 3.41 KB
/
convert_Airtel.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
"""
Script for processing Bharti Airtel rate table
Author: Ameed Jamous
Company: Telecomsxchange.com
Copyright (c) 2023 Telecomsxchange.com
"""
import pandas as pd
import logging
from datetime import datetime
# Set up logging
logging.basicConfig(level=logging.INFO, filename='airtel_log.log')
console = logging.StreamHandler()
console.setLevel(logging.INFO)
logging.getLogger('').addHandler(console)
logging.info('Starting Airtel rate conversion script.')
# Determine the file type and read the file accordingly
file_name = 'Airtel.xlsx'
logging.info(f'Reading file: {file_name}')
if file_name.endswith('.csv'):
df = pd.read_csv(file_name)
elif file_name.endswith('.xlsx'):
df = pd.read_excel(file_name)
# Find the first row that contains the expected headers
expected_headers = ['Complete Code', 'Rates (USD / Min)', 'Valid From', 'Pulse']
start_row = 0
for i, row in df.iterrows():
if set(expected_headers).issubset(row.values):
start_row = i + 1
break
# Read the file again, this time starting from the correct row
if file_name.endswith('.csv'):
df = pd.read_csv(file_name, skiprows=start_row)
elif file_name.endswith('.xlsx'):
df = pd.read_excel(file_name, skiprows=start_row)
# Step 2: Rename the 'Complete Code' column to 'Prefix'
logging.info('Renaming and adjusting columns.')
df = df.rename(columns={'Complete Code': 'Prefix'})
# Step 3: Duplicate 'Rates (USD / Min)' column to 'Price 1' and 'Price N'
df['Price 1'] = df['Rates (USD / Min)']
df['Price N'] = df['Rates (USD / Min)']
# Step 4: Convert the 'Valid From' column to 'Effective from' in the desired format
df['Valid From'] = pd.to_datetime(df['Valid From'], format='%d/%m/%Y')
df['Effective from'] = df['Valid From'].apply(lambda x: 'ASAP' if x.date() < datetime.now().date() else x.strftime('%Y-%m-%d %H:%M:%S'))
# Step 5: Split 'Pulse' into 'Interval 1' and 'Interval N'
pulse_df = df['Pulse'].str.split('/', expand=True)
df['Interval 1'] = pulse_df[0]
df['Interval N'] = pulse_df[1]
# Step 6: Add new columns and set their default values
df['Country'] = ''
df['Description'] = ''
df['Rate Id'] = ''
df['Forbidden'] = 0
df['Discontinued'] = 0
# Step 7: Remove unnecessary columns
df = df.drop(columns=['Destination', 'Valid From', 'Pulse', 'Rates (USD / Min)', 'Rate Change.1', 'Area Code Change'])
# Step 8: Reorder the columns
cols = ['Country', 'Description', 'Prefix', 'Effective from', 'Rate Id', 'Forbidden', 'Discontinued', 'Price 1', 'Price N', 'Interval 1', 'Interval N']
df = df[cols]
# Get the current timestamp
timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
# Step 9: Write the data to a CSV file with a timestamp in its name
logging.info('Writing to CSV file.')
df.to_csv(f'tcxc_airtel_price_list_{timestamp}.csv', index=False)
logging.info('Airtel rate conversion script completed.')
# Step 7: Remove unnecessary columns
df = df.drop(columns=['Destination', 'Valid From', 'Pulse', 'Rates (USD / Min)', 'Rate Change.1', 'Area Code Change'])
# Step 8: Reorder the columns
cols = ['Country', 'Description', 'Prefix', 'Effective from', 'Rate Id', 'Forbidden', 'Discontinued', 'Price 1', 'Price N', 'Interval 1', 'Interval N']
df = df[cols]
# Get the current timestamp
timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
# Step 9: Write the data to a CSV file with a timestamp in its name
logging.info('Writing to CSV file.')
df.to_csv(f'tcxc_airtel_price_list_{timestamp}.csv', index=False)
logging.info('Airtel rate conversion script completed.')