How to convert a Python list to into pandas DataFrame :-CodePudding

I have below list which I have simplified :

my_list = ['select', 'fruit1', 'fruit2, 'fruit3', 'from', 'basket1',
           'select', 'fruit4', 'from', 'basket2',
           'select', 'fruit5', 'fruit6' 'from', 'basket3', ..... so on]

Note how my list has 'select' and 'from' statements.

The output I am trying to achieve is a DataFrame or let's say Excel output:

Fruit number      Basket number
fruit1            basket1
fruit2            basket1
fruit3            basket1
fruit4            basket2
fruit5            basket3
fruit6            basket3
.                 .
.                 .
.                 .
.                 .

is there a way to achieve this result? I have tried many things but it won't work.. :(

CodePudding user response：

something like the below (use a simple "state machine")

import pandas as pd
lst = ['select', 'fruit1', 'fruit2', 'fruit3', 'from', 'basket1',
       'select', 'fruit4', 'from', 'basket2',
       'select', 'fruit5', 'fruit6', 'from', 'basket3']

data = []
fruits = []
state = 'select'
for word in lst:
  if word == 'select':
    state = 'select'
    continue
  if word == 'from':
    state = 'basket'
    continue
  if state == 'select':
    fruits.append(word)
  if state == 'basket':
    for f in fruits:
      data.append({'fruit':f,'basket':word})
    fruits = []

df = pd.DataFrame(data)
print(df)

output

    fruit   basket
0  fruit1  basket1
1  fruit2  basket1
2  fruit3  basket1
3  fruit4  basket2
4  fruit5  basket3
5  fruit6  basket3

CodePudding user response：

data = {'Select' : {'Fruit_Number': 
['fruit1','fruit2','fruit3']},'From' : {'Basket_Number': 
['basket1','basket2','basket3']}}

data2 = data['Select']
data3 = data['From']

df2 = pd.DataFrame.from_dict(data2)
df3 = pd.DataFrame.from_dict(data3)

l = [df2,df3]
df_all = pd.concat(l,axis=1)


      Fruit_Number Basket_Number
0       fruit1       basket1
1       fruit2       basket2
2       fruit3       basket3

CodePudding user response：

There are a lot of ways to do this. This approach gets the index of all the 'from', and splits 2 spaces ahead using np.split so that the start of each new array is a 'select'. The last one is empty, so we will drop that.

Then you can build a dict by slicing up each array, and make a dataframe out of it.

import numpy as np
import pandas as pd
my_list = ['select', 'fruit1', 'fruit2', 'fruit3', 'from', 'basket1',
           'select', 'fruit4', 'from', 'basket2',
          'select', 'fruit5', 'fruit6', 'from', 'basket3']

f = [i 2 for i, x in enumerate(my_list) if x == "from"][:-1]
s = np.split(my_list,f)

df = pd.DataFrame([{'basket':q[-1],'fruits':q[1:-2]} for q in s])
df = df.explode('fruits')

Output

    basket  fruits
0  basket1  fruit1
0  basket1  fruit2
0  basket1  fruit3
1  basket2  fruit4
2  basket3  fruit5
2  basket3  fruit6