Convert a list to different columns in dataframe

user_1256495 0
Viewed 84

I have a list that look like this.

['interface Eth-Trunk31', 'ssss', 'aaa', 'ddd', 'interface Eth-Trunk32', 'xxx', 'qqq', 'fff', 'hh', 'interface Eth-Trunk33', 'vv']

Would like to convert this to a dataframe like below

interface                   value
interface Eth-Trunk31       ssss
interface Eth-Trunk31       aaa
interface Eth-Trunk31       ddd
interface Eth-Trunk32       xxx
interface Eth-Trunk32       qqq
interface Eth-Trunk32       fff
interface Eth-Trunk32       hh
interface Eth-Trunk33       vv

any one liner(if possible) to convert the above list to the dataframe?

pandas python
4 Answers

One liner using filter

import pandas as pd

a= ['interface Eth-Trunk31', 'ssss', 'aaa', 'ddd', 'interface Eth-Trunk32',
 'xxx', 'qqq', 'fff', 'hh', 'interface Eth-Trunk33', 'vv']

pd.DataFrame(list(zip(list(filter(lambda x:  not x.startswith("interface"),a)),
["interface Eth-Trunk{}".format(i +31) for i in range(len(a))])),
columns= ["interface" , "value"] )

Same steps without one liner:

interface = list(filter(lambda x:  not x.startswith("interface"),a)) 
value = ["interface Eth-Trunk {}".format(i +31) for i in range(len(interface))]


pd.DataFrame(list(zip(value,interface)),columns= ["interface" , "value"] )

Output:

   interface              value
0  interface Eth-Trunk31  ssss
1  interface Eth-Trunk31   aaa
2  interface Eth-Trunk31   ddd
3  interface Eth-Trunk32   xxx
4  interface Eth-Trunk32   qqq
5  interface Eth-Trunk32   fff
6  interface Eth-Trunk32    hh
7  interface Eth-Trunk33    vv
user_112802890

A solution using groupby (from itertools) and explode:

import pandas as pd
from itertools import groupby, chain

data = ["interface Eth-Trunk31", "ssss", "aaa", "ddd", "interface Eth-Trunk32", "xxx", "qqq", "fff", "hh", "interface Eth-Trunk33", "vv"]

grouped_data = [
    list(g) for _, g in groupby(data, key=lambda e: e.startswith("interface"))
]
df = pd.DataFrame(
    zip(chain.from_iterable(grouped_data[::2]), grouped_data[1::2]),
    columns=["interface", "values"],
).explode(column="values")
print(df)
user_94369800

Use list comprehension for list of tuples and pass to DataFrame constructor:

items = []
interface = None
for x in L:
    if x.startswith('interface'):
        interface = x
    else:            
        items.append((interface, x))

df = pd.DataFrame.from_records(items, columns=['interface', 'value'])

print (df)
0  interface Eth-Trunk31  ssss
1  interface Eth-Trunk31   aaa
2  interface Eth-Trunk31   ddd
3  interface Eth-Trunk32   xxx
4  interface Eth-Trunk32   qqq
5  interface Eth-Trunk32   fff
6  interface Eth-Trunk32    hh
7  interface Eth-Trunk33    vv
user_29010020

A combination of where and query could get you the dataframe you want:

df = ['interface Eth-Trunk31', 'ssss', 'aaa', 'ddd', 'interface Eth-Trunk32', 'xxx', 'qqq', 'fff', 'hh', 'interface Eth-Trunk33', 'vv']

(pd.DataFrame(df, columns=['value'])
   .assign(interface = lambda df: df.where(df['value'].str.startswith("interface"))
                                    .ffill())
   .query("value != interface")
 )

   value              interface
1   ssss  interface Eth-Trunk31
2    aaa  interface Eth-Trunk31
3    ddd  interface Eth-Trunk31
5    xxx  interface Eth-Trunk32
6    qqq  interface Eth-Trunk32
7    fff  interface Eth-Trunk32
8     hh  interface Eth-Trunk32
10    vv  interface Eth-Trunk33
user_71757130
Related