please advise how to perform the following permutations:
array = [1, 3, 2] # numpy.ndarray
l1 = ['foo_qwe1_ert1', 'bar_qwe2_ert2', 'baz_qwe3_ert3'] # list
I need to get the following pandas dataframe:
| Column1 | Column2 | Column3 |
|---|---|---|
| foo | qwe1 | ert1 |
| baz | qwe3 | ert3 |
| bar | qwe2 | ert2 |
the problem is the list contains text labels from 0 to 30 (format: XXX_YYY_ZZZ) and numpy.array has shape (3536,) and contains numbers from 0 to 30. I need to assign label for each number in array and save it as pandas dataframe
First use DataFrame constructor with split:
df = pd.DataFrame([x.split('_') for x in l1], columns=['Column1', 'Column2', 'Column3'])
print (df)
Column1 Column2 Column3
0 foo qwe1 ert1
1 bar qwe2 ert2
2 baz qwe3 ert3
And then change order by array by extract last integer from last column:
df.index = df['Column3'].str.extract('(\d+)$', expand=False).astype(int)
df = df.loc[array].reset_index(drop=True)
print (df)
Column1 Column2 Column3
0 foo qwe1 ert1
1 baz qwe3 ert3
2 bar qwe2 ert2
EDIT:
array = np.array([1, 3, 2])
l1 = ['foo_qwe1_ert1', 'bar_qwe2_ert2', 'baz_qwe3_ert3']
L = [x.split('_') for x in l1]
a, b, c = L[0]
b = b.replace('1','')
c = c.replace('1','')
print (b, c)
qwe ert
out = [(y[0], f'{b}{x}', f'{c}{x}') for x, y in zip(array, L)]
print (out)
[('foo', 'qwe1', 'ert1'), ('bar', 'qwe3', 'ert3'), ('baz', 'qwe2', 'ert2')]
Or:
out = [(y[0], f'qwe{x}', f'ert{x}') for x, y in zip(array, L)]
print (out)
[('foo', 'qwe1', 'ert1'), ('bar', 'qwe3', 'ert3'), ('baz', 'qwe2', 'ert2')]
df = pd.DataFrame(out, columns=['Column1', 'Column2', 'Column3'])
print (df)
Column1 Column2 Column3
0 foo qwe1 ert1
1 bar qwe3 ert3
2 baz qwe2 ert2