How To Use Pandas To Read A Line From A Csv, Proceed A VLOOKUP Action And Save The Results Into Another File?

From this question, I found how to use pandas to proceed VLOOKUPs. So, as suggested by jezrael, I did this: df1 = pd.read_csv('df1.csv', names=['a','b']) print (df1)

Solution 1:

First create DataFrame - header=None means no csv header:

df1 = pd.read_csv('df1.csv', sep=';',header=None)

Reshape to Series by stack and split by regex '\s*,\s* means double zero or more whitespaces between comma:

df1 = df1.stack().str.split('\s*,\s*', expand=True)
print (df1)
                    0            1
0 0            Symbol            A
  1              Goal         1.07
  2             Range  0.72 - 1.07
  3  Return over time       15.91%
1 0            Symbol            B
  1              Goal         1.06
  2             Range   0.5 - 1.32
  3  Return over time        9.91%
  4          Maturity            5

Remove second level by reset_index and add new level by set_index, last reshape by unstack:

df1 = df1.reset_index(level=1, drop=True).set_index(0, append=True)[1].unstack()
print (df1)
0  Goal Maturity        Range Return over time Symbol   Total
0  1.07     None  0.72 - 1.07           15.91%      A    None
1  1.06        5   0.5 - 1.32            9.91%      B  13.555