CODEWITHSUNDEEP
pythonpandasexceldataframe
ahmad
ahmad

Reputation: 29

Manipulate string data into excel file

I have data in string format like:

str1 = "[0,-1.5],[-12.5,1.5],[12.5,1.5],[12.5,-1.5],[-12.5,-1.5])"

I want to put this data into an excel file. means 1st value from the array will go in x Col and 2nd value will go in Y col. this will be repeated until the whole string will be added to the x and y columns. I am attempting like, first convert the string into dataframe and then dataframe to an excel file. but it's giving me an error of "Empty DataFrame".

    bad_chars = [';', ':', '(', ')', '[', ']']
s = ""
for i in str1:
    if i not in bad_chars:
        s += i
print(s)

StringData = StringIO(s)


df = pd.read_csv(StringData, sep=",")

# Print the dataframe
print(df)

enter image description here

Upvotes: 0

Views: 354

Answers (1)

Timeless
Timeless

Reputation: 37827

You can use pandas.Series.str.extractall :

out = (
        pd.Series([str1])
            .str.extractall(r"(-?\d+\.?\d*,-?\d+\.?\d*)")
            .reset_index(drop=True)
            [0].str.split(",", expand=True)
            .rename(columns= {0: "X", 1: "Y"})
            .applymap('="{}"'.format)
      )
​

# Output :

print(out)

          X        Y
0      ="0"  ="-1.5"
1  ="-12.5"   ="1.5"
2   ="12.5"   ="1.5"
3   ="12.5"  ="-1.5"
4  ="-12.5"  ="-1.5"

Then, you can use pandas.DataFrame.to_excel to put this dataframe in a spreadsheet:
out.to_excel("path_to_the_file.xlsx", index=False)

Upvotes: 1

Related Questions