Try to include a column based on input and file name in Pandas Dataframe in Python

Question

I have a several csv files which have the following structure:

            Erster    Hoch    Tief  Schlusskurs    Stuecke      Volumen
Datum                                                                  
14.02.2017  151.55  152.35  151.05       152.25    110.043   16.687.376
13.02.2017  149.85  152.20  149.25       151.25     415.76   62.835.200
10.02.2017  149.00  150.05  148.65       149.40    473.664   70.746.088
09.02.2017  144.75  148.45  144.35       148.00    642.175   94.348.392


             Erster     Hoch     Tief  Schlusskurs  Stuecke Volumen
Datum                                                              
14.02.2017  111.454  111.776  111.454      111.776       44   4.918
13.02.2017  110.570  110.989  110.570      110.989      122  13.535
10.02.2017  109.796  110.705  109.796      110.705        0       0
09.02.2017  107.993  108.750  107.993      108.750      496  53.933

all are different based on the naming of the file name:

wkn_A1EWWW_historic.csv 
wkn_A0YAQA_historic.csv

I want to have the following Output:

Date        wkn      Open    High    low     Close    pieced     Volume

14.02.2017  A1EWWW   151.55  152.35  151.05  152.25    110.043   16.687.376
13.02.2017  A1EWWW   149.85  152.20  149.25  151.25     415.76   62.835.200
10.02.2017  A1EWWW   149.00  150.05  148.65  149.40    473.664   70.746.088
09.02.2017  A1EWWW   144.75  148.45  144.35  148.00    642.175   94.348.392

Date        wkn      Open    High    low     Close    pieced     Volume
14.02.2017  A0YAQA   111.454  111.776  111.454      111.776       44   4.918
13.02.2017  A0YAQA   110.570  110.989  110.570      110.989      122  13.535
10.02.2017  A0YAQA   109.796  110.705  109.796      110.705        0       0
09.02.2017  A0YAQA   107.993  108.750  107.993      108.750      496  53.933

The code looks like the following:

import pandas as pd

wkn_list_dummy = {'A0YAQA','A1EWWW'}

for w_list in wkn_list_dummy:
    url = 'C:/wkn_'+str(w_list)+'_historic.csv'
    df = pd.read_csv(url, encoding='cp1252', sep=';', decimal=',', index_col=0)
    print(df)

I tried using melt but it was not working.

MCM · Accepted Answer · 2017-02-14 14:52:23Z

2

You can add column by just assigning a value to it:

df['new_column'] = 'string'

All together:

import pandas as pd

wkn_list_dummy = {'A0YAQA','A1EWWW'}

final_df = pd.DataFrame()
for w_list in wkn_list_dummy:
    url = 'C:/wkn_'+str(w_list)+'_historic.csv'
    df = pd.read_csv(url, encoding='cp1252', sep=';', decimal=',', index_col=0)
    df['wkn'] = w_list
    final_df = final_df.append(df)
final_df.reset_index(inplace=True) 
print(final_df)

edited Feb 14, 2017 at 14:52

MCM

1,5113 gold badges19 silver badges22 bronze badges

answered Feb 14, 2017 at 13:10

Jan Zeiseweis

3,7482 gold badges19 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MCM Over a year ago

Thanks a lot this was what I was looking for. How can I reset the index ´Datum´ to be equally handled like the other columns Erste etc.?

MCM Over a year ago

thanks changed it. Just had problems that I had it first in the loop.

Collectives™ on Stack Overflow

Try to include a column based on input and file name in Pandas Dataframe in Python

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related