python xlsx converteer rij waarden naar kolommen

[satmarco]

Ik probeer een database export in excel om te bouwen naar een leesbaarder formaat.

De xlsx ziet er ongeveer zo uit, maar dan met meer verschillende eigenschappen:

nummer	eigenschap	waarde
100	eigenschap1	Z50
100	eigenschap2	Z60
200	eigenschap1	Z80
200	eigenschap2	Z90
300	eigenschap1	Z80

En dan wil ik die ombouwen naar dit formaat:

nummer	eigenschap1	eigenschap2
100	Z50	Z60
200	Z80	Z90
300	Z80	-

Nu ben ik nog maar een aantal weken bezig om python te leren maar kom er niet uit hoe ik dit voor elkaar kan krijgen.
Ik ben vooral met pandas aan het proberen geweest met onderstaande regels.

Python:

for row in df.itertuples(index=False)

for ind,row in df.iterrows():

for k,v in df.iteritems():

df_grouped = df.groupby('nummer')

Maar in alle gevallen bleven de eigenschappen met bijbehorende waarde onder elkaar staan en kreeg ik ze niet achter elkaar op een regel.
Ik merk dat zoeken naar de oplossing lastig is al je niet weet hoe je de vraag op wil gaan lossen, en zoektermen als 'python convert / transpose xlsx row values to columns' geven mij niet de juiste richting om het op te lossen.

Zou iemand mij op weg kunnen helpen met een stukje voorbeeld code?

[heintjeput]

In excel zou ik het oplossen met een pivottabel, je zou de panda’s functie daarvoor eens kunnen proberen panda’s docs

Als je grouped by wil gebruiken probeer dan eerst eens zonder een for loop handmatig wat te bereiken.

[hostname]

Kijk eens naar de unstack method

[satmarco]

De pivot functie kom ik nog niet uit.

De unstack doet ook nog niet wat ik dacht dat het zou doen.
Als ik mijn excel gebruik in de voorbeeld code

Python:

import pandas as pd
import numpy as np

index = pd.MultiIndex.from_tuples([('100', 'eigenschap1','Z50'), ('100', 'eigenschap2','Z60'),
                                   ('200', 'eigenschap1','Z80'), ('200', 'eigenschap2','Z90')])
print(index)
s = pd.Series(np.arange(1.0, 5.0), index=index)
print(s)
print(s.unstack(level=-1))

Dan geeft dat deze output

		Z50	Z60	Z80	Z90
100	eigenschap1	1.0	NaN	NaN	NaN
	eigenschap2	NaN	2.0	NaN	NaN
200	eigenschap1	NaN	NaN	3.0	NaN
	eigenschap2	NaN	NaN	NaN	4.0

Heb wat getest met verschillende levels maar daar werd het nog niet beter van.

[satmarco]

Wat ik wil heb ik nu zo'n beetje in beeld met deze code.

Python:

for index, row in df.iterrows():
    x = row['nummer']
    if row['eigenschap'] == 'eigenschap1':
        y = row['waarde']
    if row['eigenschap'] == 'eigenschap2':
       z= row['waarde']
       final = x, y, z
       print(final)

De uitvoer is dan
(100, Z50, Z60)
(200, Z80, Z90)
enz.

De print final geeft het volledige beeld, wel met dubbele regels doordat de loop meerdere keren langs hetzelfde nummer loopt maar dat is er later wel weer uit te filteren.

Hoe krijg ik dit nu uitgevoerd naar een nieuw dataframe?

[Morrar]

Eigenlijk is iterrows nooit het goede antwoord

Maar die pivot is toch niet zo complex?

Python:

df.pivot(
  index='<kolom met IDs>' ,
  columns='<kolom met de nieuwe kolomnamen>' ,
  values='<kolom met de waardes>' , 
)

Als je je originele DataFrame bekijkt kun je het zo invullen. Eventueel een reset_index() erbij, zodat je IDs weer als kolom te benaderen zijn...

[ Voor 7% gewijzigd door Morrar op 01-02-2021 19:23 ]

[satmarco]

Dank je voor het extra voorbeeldje met pivot, ik zag ineens de logica.
En achteraf is het simpel zat..
Omdat de excel meer kolommen heeft liep ik nog wel tegen de melding "Index contains duplicate entries, cannot reshape" aan. Die heb ik opgelost door de drop_duplicates toe te voegen.

Python:

import pandas as pd
pd.set_option('display.width', 4000)
pd.set_option('display.max_columns', None)

df = pd.read_excel("test.xlsx")
df = df[['nummer','eigenschap' ,'waarde']]
df = df.drop_duplicates()

df_new = df.pivot(
  index='nummer' ,
  columns='eigenschap' ,
  values='waarde' ,
)

print(df_new)