Clash Royale CLAN TAG

Separate text from a csv row into multiple ones?

I am having a .csv file with rows: NAME, LOCATION, IP Address. Row IP Address has many IPs in it and they are separated by a whitespace, up to 5 characters. For example:

NAME

LOCATION

IP Address

IP Address

EDITED:

Name,Location,IP Address
Router,China,10.10.10.1 10.10.10.2 10.10.10.3 10.10.10.4 10.10.10.5
Switch,USA,192.168.1.1 192.168.1.2 192.168.1.3


and so on..

I want all the IPs to be on the separate rows under the IP Address column. This is how I want my output to look like:

IP Address

Name,Location,IP Address
Router,China,10.10.10.1
Router,China,10.10.10.2
Router,China,10.10.10.3
Router,China,10.10.10.4
Router,China,10.10.10.5
Switch,USA,192.168.1.1
Switch,USA,192.168.1.2
Switch,USA,192.168.1.3


I have been trying to do .split() but it is giving me an error. Can anyone please help me?

.split()

Using csv.DictReader to read my csv files here.

csv.DictReader

2 Answers
2

I assume that the fields are indeed separated by comma, without whitespace padding, and that the IP addresses are separated by multiple spaces. Then this will work:

RE_SEPARATOR = re.compile(' +')
with open(path) as f:
for row in csv.DictReader(f):
addresses = RE_SEPARATOR.split(row["IP Address"])
for address in addresses:
print(row["Name"], row["Location"], address)


You will have to adjust the print function call so that it delivers the output format you need (which is not entirely clear based on your description).

print

The basic idea is to split the IP address string with its whitespaces into a list containing a list/array of IP addresses. Then explode this into a long format whereas each combination (value) is represented as a single row. I did this in one step by utilizing Pandas DataFrame with its groupby function followed by a custom apply function. For your question this code works:

DataFrame

groupby

apply

import pandas as pd

columns = ['Name', 'Location', 'IP Address']

data = [
['Router', 'China', '10.10.10.1 10.10.10.2 10.10.10.3'],
['Switch', 'USA', '192.168.1.1 192.168.1.2 192.168.1.3']
]

#df = pd.read_csv(your_file_name)
df = pd.DataFrame(data, columns=columns)

def extract_ip_addresses(df_group):
row = df_group.iloc[0]
name = row['Name']
location = row['Location']
grouped_data = [[name, location, ip] for ip in row['IP Address'].split()]
return pd.DataFrame(grouped_data, columns=columns)

df.groupby(['Name', 'Location'], group_keys=False).apply(extract_ip_addresses).reset_index(drop=True)


This yields this result:

Name Location IP Address
0 Router China 10.10.10.1
1 Router China 10.10.10.2
2 Router China 10.10.10.3
3 Switch USA 192.168.1.1
4 Switch USA 192.168.1.2
5 Switch USA 192.168.1.3


In your case you could read the initial data by pd.read_csv(your_file_name).

pd.read_csv(your_file_name)

Here are some detailed examples:

For more details, please refer to the Pandas documentation.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.