I have a list of lists, containing country names and 5 numeric values e.g.
[['Korea, Republic of 0.07 40 13 13 153'], ['United States 0.22 8 3 4 109'],['Argentina 0.19 10 14 17 33']]
I'm basically just trying to turn this into a Pandas Dataframe, with the country in the first column, and the following 5 individual ints/floats split into their own columns.
Is there any easy way to go about this? My regex is pretty rusty but I'm sure this could be done in a few lines of code!
Many thanks :)
You don't need regex, if your data is consistent:
df = pd.DataFrame(list(map(lambda x: x[0].rsplit(' ', 5), data)),
columns=['country', 'a','b','c','d','e'])
# convert to numeric
df.iloc[:, 1:] = df.iloc[:, 1:].astype(float)
Output:
country a b c d e
0 Korea, Republic of 0.07 40.0 13.0 13.0 153.0
1 United States 0.22 8.0 3.0 4.0 109.0
2 Argentina 0.19 10.0 14.0 17.0 33.0
Quang Hoang's solution is far more elegant, but — in case you're looking for the regex — here's one approach:
import re
import pandas as pd
# Given data
data: list = [['Korea, Republic of 0.07 40 13 13 153'], ['United States 0.22 8 3 4 109'],
['Argentina 0.19 10 14 17 33']]
# Split off discrete components and assemble into frame
reg = re.compile(pattern=r"(?<=[a-z])((\s)(?=\d))")
data = [list(filter(str.strip, reg.split(sub[0]))) for sub in data]
data_cleaned = {"country": [elem[0] for elem in data], "numeric_value": [elem[1] for elem in data]}
df = pd.DataFrame(data=data_cleaned)
# Expand numeric values and drop source variable
df[[col for col in range(1, 6)]] = df["numeric_value"].str.split(expand=True)
df = df.drop(columns="numeric_value")
| country | 1 | 2 | 3 | 4 | 5 |
|---|---|---|---|---|---|
| Korea, Republic of | 0.07 | 40 | 13 | 13 | 153 |
| United States | 0.22 | 8 | 3 | 4 | 109 |
| Argentina | 0.19 | 10 | 14 | 17 | 33 |