Pandas - Grouping data by index

Question

This is not my actual data, just a representation of a larger set.

I have a dataframe (df) that looks like this:

id   text_field   text_value
1    date         2021-07-01
1    hour         07:04
2    available    yes
2    sold         no

Due to project demand i need to manipulate this data to a certain point. The main part is this one:

df.set_index(['id','text_field'], append=True).unstack().droplevel(0,1).droplevel(0)

Leaving me with something like this:

text_field      date          hour      available    sold 
id              
1               2021-07-01    NaN          NaN        NaN
1               NaN           07:04        NaN        NaN
2               NaN           NaN          yes        NaN
2               NaN           NaN          NaN        no

That is very close to what i need, but i'm failing to achieve the next step. I need to group this data by id, leaving only one id on each line.

Something like this:

  text_field      date          hour      available   sold 
    id              
    1             2021-07-01    07:04      NaN        NaN
    2             NaN           NaN        yes        no

Can somebody help me?

you may need to use the pandas piviot table , see here https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.pivot_table.html — Umer, Jul 08 '21 at 21:26
also see this https://stackoverflow.com/questions/38606393/pandas-pivot-table-arrangement-no-aggregation — Umer, Jul 08 '21 at 21:27

score 0 · Answer 1 · answered Jul 09 '21 at 12:03

As mentioned by @Nk03 in the comments, you could use the pivot feature of pandas:

import pandas as pd

# Creating example dataframe
data = {
    'id': [1, 1, 2, 2],
    'text_field': ['date', 'hour', 'available', 'sold'],
    'text_value': ['2021-07-01', '07:04', 'yes', 'no']
}
df = pd.DataFrame(data)

# Pivoting on dataframe
df_pivot = df.pivot(index='id', columns='text_field')
print(df_pivot)

Console output:

           text_value                        
text_field  available        date   hour sold
id                                           
1                 NaN  2021-07-01  07:04  NaN
2                 yes         NaN    NaN   no

Pandas - Grouping data by index

1 Answers1