Pandas DataFrame Pivot Duplciate Row Entries - Mixed Data without Aggregation

Prydz_Toon 0
Viewed 57

How would I go about re-shaping the following data frame so the unique entries in the keys column become new columns, with the values column becoming the row values for each. The data is mixed in each element which I'd planned to change once the data is "flipped" / transposed.

I've tried pivot / pivot_table but I end up with a lot of NaN due to aggregating. I don't want to aggregate I'd like to do something like transpose, but without transposing all the duplicates Key entries to new columns (the JSON File No. entry can be also be dropped if its required as it's not really needed).

The data repeats throughout the file as follows, with the next entry repeating at 'race_id'. It's worth noting for the race_id element, every other key below it (e.g. date, course_id, course etc) is common to it e.g. the race_id in each block is in fact the index for each block of 24 (the 2nd picture below titled "Intended Structure" represents this better)

Jupyter Console Output

The columns I'd like are as follows:

'race_id'
'date'
'course_id'
'course'
'off_time'
'race_name'
'distance_round'
'distance'
'distance_f'
'region'
'pattern'
'race_class'
'type'
'age_band'
'prize'
'field_size'
'going_detailed'
'rail_movements'
'stalls'
'weather'
'going'
'surface'
'runners'

The final element 'runners' is actually a nested list which I'd like to explode.

I actually got it to work using the following code, however it only works on the first element, but it should provide a better explanation for what I'm aiming for:

df_3 = pd.pivot_table(df_3, columns='Keys', index='JSON File No.', values='Values', aggfunc='first')

Intended Structure - only applied to one repeated element

Any assistance would be appreciated

pivot pandas python
1 Answers

There seems to be multiple questions here - but I think your first step is to broadcast the race_id to the 22 fields that follow it, for each race_id. Once you've done that, your other requirements should be fairly straightforward.

someDF["RaceIndex"] = someDF.apply(lambda x: x[0] if x["Keys"]=="race_id" else None, axis=1).fillna(method='ffill')

Then, for example, you can pivot or index, using the RACE column.

e.g. - set multi-index and unstack so you get the Keys as columns

someDF.set_index(["RaceIndex", "Keys"]).unstack()

Notes:

  • it seems you had a JSON source on this - if you look at the Pandas read_json() command, you may find that changing your options there will load the JSON into a form that will be easier to work with
  • if not every race has every key, you can stick a .fillna(value="") after the unstack command to fill in the blanks
  • you didn't provide data in a form I could easily recreate, so I just made some up - you can see how the multi-index/unstack process works below

In the event you get this error: ValueError: Index contains duplicate entries, cannot reshape, it means you have one or more duplicates in the key (remember that we've selected an arbitrary key here, so there are no guarantees that it is unique in the source data).

You can view your duplicates like so:

someDF[someDF.duplicated(subset=["Keys", "RaceIndex"])]

Resolution depends on the underlying situation:

  1. if the same key value is used twice to represent two different things, you'll need to revisit your choice of key (since it's not actually unique)

  2. If there are just some duplicate records in the data, just snip them like so (just before you do your set_index/unstack())

    someDF = someDF[~(someDF.duplicated(subset=["Keys", "RaceIndex"]))]

Here's what the process looks like in general:

enter image description here

James_SO0
Related