Manipulate string data into excel file

Question

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)

Is str1 correctly specified? Does it have only one round bracket? — medium-dimensional
– medium-dimensional, Commented Nov 23, 2022 at 21:02

Timeless · Accepted Answer · 2022-11-24 07:54:36Z

1

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)

edited Nov 24, 2022 at 7:54

answered Nov 23, 2022 at 21:16

Timeless

38.3k6 gold badges33 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ahmad Over a year ago

it's extracting the data but there is one issue. I have very large data, like these values [0.55,-2.1375],[0.55,-1.9625]. it should extract X: Y: 0.55, -1.1375, 0.55, -1.9625, but I am getting wrong like this X: Y: 55, -1.1375, 55, -1.9625,

Timeless Over a year ago

I updated the regex pattern ;)

ahmad Over a year ago

it should be 0.55 in X col but I am getting 55 instead

Timeless Over a year ago

Have you checked my updated answer ?

Timeless Over a year ago

It's an Excel problem if this one get rid of the leading zero, I updated my answer to handle this ;)

Collectives™ on Stack Overflow

Manipulate string data into excel file

1 Answer 1

# Output :

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

# Output :

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related