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)
                    0100            Symbol            A
  1              Goal         1.072Range0.72-1.073Returnovertime15.91%10            Symbol            B
  1              Goal         1.062Range0.5-1.323Returnovertime9.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        RangeReturnovertime Symbol   Total
01.07None0.72-1.0715.91%      A    None11.0650.5-1.329.91%      B  13.555