Assigning values to parameters using read_excel from pandas in python

Question

I want to read an Excel file using pandas. I want to assign specific cells to certain parameters.

So my Excel contains 4 columns. First columns contains locations "s", 2nd contains the time "t" in years and 3rd and 4th column are 2 different materials that are available at this certain location at a certain time. The first few rows look like this:

s        t          Biomasse       KWS
AT1     2025         234234        2323
AT1     2025.25      238208        0990
AT1     2025.5       20323         2939
AT2     2025         8888          2323

df = pd.read_excel("Inputdaten_Strom.xlsx", sheetname="Angebot_Nachfrage")
for m in M:
    if m == "Biomasse":
       i = 0
        for s in S:
            for t in T:
                Ang[m,s,t] = df["Biomasse"][i]
                i = i + 1
    if m == "KWS":
        i = 0
        for s in S:
            for t in T:
                Ang[m,s,t] = df["KWS"][i]
                i = i + 1
print Ang["Biomasse","AT1",2025.25]

This works but is very static, since if the set S does not match the s column in the sheet, it won't work correctly. I tried something like:

Ang = {}
df = pd.read_excel("Inputdaten_Strom.xlsx", sheetname="Angebot_Nachfrage")
i = 0
for m in M:
    if m == "Biomasse":
        for s in df["s"]:
            for t in T:
                Ang[m,s,t] = df["Biomasse"][i]
                i = i + 1

But it gives me a key error. Can anyone help me on how to read in values correctly and efficiently?

could you show the expected output. What is M and S and T? — pythonic833
– pythonic833, Commented Aug 2, 2019 at 12:43
The expected output for Ang["Biomasse", "AT1", 2025] would be 234234. Ang["KWS", "AT1", 2025.25] should be 0990 e. g. — Shady_94
– Shady_94, Commented Aug 3, 2019 at 19:47
M is a set of materials (Biomasse, KWS and some others), S is a set of locations s(AT1, AT2 etc.) and T is a set of timeperiods t (2025,2025.25 etc) — Shady_94
– Shady_94, Commented Aug 3, 2019 at 19:49

pythonic833 · Accepted Answer · 2019-08-02 13:31:49Z

1

I would use the pivot function and return the pivotized frame as a dictionaries. We need to remove the keys with empty values from the dictionary and iterate to unite the dictionaries.

df_pivot = pd.pivot_table(df, columns=['s','t'], index='Biomasse', values='KWS')
lst= [{k: v for k, v in dct.items() if not math.isnan(v)} for dct in df_pivot.to_dict(orient='row')]
Ang = {}
for el in lst:
    Ang.update({("Biomasse", key[0],key[1]):value for key, value in el.items()})

result

{('Biomasse', 'AT2', 2025.0): 2323.0,
 ('Biomasse', 'AT1', 2025.5): 2939.0,
 ('Biomasse', 'AT1', 2025.0): 2323.0,
 ('Biomasse', 'AT1', 2025.25): 990.0}

edited Aug 2, 2019 at 13:31

answered Aug 2, 2019 at 13:19

pythonic833

3,2341 gold badge16 silver badges29 bronze badges

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

Comments

Shady_94 · Accepted Answer · 2019-08-03 19:58:43Z

0

Thanks for your answer :) Any downside on doing it with itertuples()?

For row in df.itertuples():
    Ang["Biomasse", row.s, row.t] = 
    row.Biomasse

answered Aug 3, 2019 at 19:58

Shady_94

475 bronze badges

Collectives™ on Stack Overflow

Assigning values to parameters using read_excel from pandas in python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related