Pandas: pivot_table-CodePudding

Suppose I have a datafreme like this one:

data = [
    {'column1': 'asd', 'column2': 'a', 'column3': 'mileage', 'column4': 7889, 'column5': None},
    {'column1': 'fsd', 'column2': 'a', 'column3': 'temp', 'column4': 4557, 'column5': None},
    {'column1': None, 'column2': 'a', 'column3': 'string', 'column4': None, 'column5': 'value_string1'},
    {'column1': 'asd', 'column2': 'b', 'column3': 'mileage', 'column4': 5678, 'column5': None},
    {'column1': 'fsd', 'column2': 'b', 'column3': 'temp', 'column4': 5830, 'column5': None},
    {'column1': None, 'column2': 'b', 'column3': 'string', 'column4': None, 'column5': 'value_string2'},
    {'column1': None, 'column2': 'c', 'column3': 'temp', 'column4': 10, 'column5': None},
    {'column1': None, 'column2': 'c', 'column3': 'temp', 'column4': 8, 'column5': None},

]

df = pd.DataFrame(data)

    column1 column2 column3 column4 column5
0   asd       a      mileage 7889.0  None
1   fsd       a        temp  4557.0  None
2   None      a       string  NaN  value_string1
3   asd       b       mileage 5678.0 None
4   fsd       b        temp   5830.0 None
5   None      b       string  NaN  value_string2
6   None      c        temp   10.0   None
7   None      c        temp   8.0    None

I want to transform this dataframe in something like:

        
column2 mileage temp_1  temp_2  
a       7889.0  4557.0  NaN
b       5678.0  5830.0  NaN
c        NaN    10.0    NaN
c        NaN     Nan    8.0

i tried to use pandas function "pivot_table"

pivot_table = df.pivot_table(index='column2', columns='column3', values='column4')

but the result is

column3 mileage temp
column2     
a       7889.0  4557.0
b       5678.0  5830.0
c         NaN    9.0

so this function combines the equal values on column 2 but if they also have the same value on column 3 the value in column 4 will be the average of the values in the original table.

Is there any way to modify this function to get the result I want? or is there another way to do it?

CodePudding user response：

It's not exactly what you want but take a look to:

>>> (df.loc[df['column4'].notna(), ['column2', 'column3', 'column4']]
       .assign(index=lambda x: x.groupby('column3').cumcount())
       .pivot_table(index=['index', 'column2'], columns='column3', values='column4')
       .reset_index('column2').rename_axis(index=None, columns=None))

  column2  mileage    temp
0       a   7889.0  4557.0
1       b   5678.0  5830.0
2       c      NaN    10.0
3       c      NaN     8.0

CodePudding user response：

A similar solution to @Corralien, a bit different wording:

# create a counter for duplicate values of column2 and column3
df['cumcount'] = df.groupby(['column2', 'column3']).cumcount()
# adding pivot table
df.pivot_table(index=['column2','cumcount'],
               columns='column3',
               values='column4',
               aggfunc={'column4':['first']})

                   first        
column3          mileage    temp
column2 cumcount                
a       0         7889.0  4557.0
b       0         5678.0  5830.0
c       0            NaN    10.0
        1            NaN     8.0

First two columns are a composite index.