String to Data frame column

Question

I have 2 column in data frame, X and Y. And I have some string values stored in text, which I want to put in X, Y as shown in the example.

Example :

text=9 10 13 110 14 16
     12 1 6   1  1  2

X    Y

9    12

10   1

13   6

110  1

14   1

16   2

Answer 1

If you are looking to hard-code it for only 2 columns, this can be achieved as follows:

import pandas as pd
df = pd.DataFrame()

text = '9 10 13 110 14 16 12 1 6   1  1  2'
text = text.split()

df['X'] = text[:int(len(text)/2)]
df['Y'] = text[int(len(text)/2):]

Answer 2

I will assume your text is in two strings like this:

In [1]: import pandas as pd                                                                            

In [2]: text1 = "9 10 13 110 14 16"                                                                    

In [3]: text2 = "12 1 6 1 1 2" 

A one-liner solution would be:

In [4] df = pd.DataFrame.from_records(zip(text1.split(" "), text2.split(" ")))

A Pandas Dataframe can be created by passing it one list (or tuple) for each row that you want in the table. This is done by using the from_records() method you see above.

So the steps that make the above line work:

1. split() each string on the spaces, to get a list of strings - one per value.
2. create each row that we want in the dataframe, which is each matched pair from the two lists of values. zip does exactly that for us.
3. Put the result into the from_records() method.

The final result:

In [7]: df
Out[7]:

0   1
0    9  12
1   10   1
2   13   6
3  110   1
4   14   1
5   16   2

Because we just gave the dataframe lists of strings, the values are still strings in teh dataframe. If you want to actually use them as number, you can use the astype() method, like this

df_integers = df.astype(int)    # now contains integers
df_floats = df.astype(float)    # now contains floats, i.e. decimal values

Answer 3

If I am understanding the question correctly, the solution should be like this:

import pandas as pd
text = "9 10 13 110 14 16 12 1 6 1 1 2"
text = text.split()
X_part = text[:int(len(text)/2)]
Y_part = text[int(len(text)/2):]

df = pd.DataFrame(columns=['X', 'Y'])
df['X'] = X_part
df['Y'] = Y_part

Output: