Can't pivot or transfer from long to wide format?

user_1030099 0
Viewed 21

I have the following csv https://justpaste.it/8spqq

I am trying to pivot it and transfer from long to wide format.

import pandas as pd

pd.DataFrame(pd.pivot_table(df,
                            values='data_point_value',
                            index='requestId',
                            columns='data_point_key',
                            aggfunc=','.join).to_records())

But for some reason I am getting:

enter image description here

Please advise what I am missing here?

When trying to do it with

df.pivot(values='data_point_value',
         index='requestId',
         columns='data_point_key')

I am getting an error:

ValueError: Index contains duplicate entries, cannot reshape
pandas pivot-table dataframe pivot python-3.x
1 Answers

So, with data you posted:

df = pd.read_table("8spqq.txt", sep=",").drop(columns="Unnamed: 0")
print(df)  # [598 rows x 3 columns]

The problem comes from the fact that the following values in data_point_key are repeated several times for each requestId:

print(
    df.loc[
        df.duplicated(subset=["requestId", "data_point_key"], keep=False),
        "data_point_key",
    ].unique()
)
# duplicated values
['requestColumnGrants.columnInfoDetails.tags.name'
 'requestColumnGrants.columnInfoDetails.name'
 'requestColumnGrants.columnInfoDetails.trinoName'
 'requestColumnGrants.operationType'
 'requestColumnGrants.operationPayload']

So, you can either drop them and pivot:

new_df = (
    df
    .drop_duplicates(subset=["requestId", "data_point_key"], keep=False)
    .pivot(values="data_point_value", index="requestId", columns="data_point_key")
)

print(new_df)
# Output
data_point_key requestColumnGrants.columnInfoDetails.tags.riskFactor  ... userIds.0
requestId                                                             ...
17                                                           HIGH     ...        12
19                                                           HIGH     ...        12
20                                                           HIGH     ...        12
21                                                           HIGH     ...        12
22                                                           HIGH     ...        12
23                                                           HIGH     ...        12
24                                                           HIGH     ...        12
25                                                           HIGH     ...        12
26                                                           HIGH     ...        12
27                                                           HIGH     ...        12

[10 rows x 33 columns]

Or, if you need to keep them, then I suggest dealing with them first after extracting them, then merge them back and pivot:

temp_df = df.loc[df.duplicated(subset=["requestId", "data_point_key"], keep=False), :]
df = df.drop(temp_df.index)

temp_df = (
    temp_df.groupby(by=["requestId", "data_point_key"])
    .agg(",".join)
    .reset_index(drop=False)
)


new_df = (
    pd.concat([df, temp_df], axis=0)
    .drop_duplicates(subset=["requestId", "data_point_key"], keep=False)
    .pivot(values="data_point_value", index="requestId", columns="data_point_key")
)

print(new_df)
# Ouput
data_point_key         requestColumnGrants.columnInfoDetails.name  ... userIds.0
requestId                                                          ...
17              credit_score,age,region,gender,year_income,job...  ...        12
19              region,year_income,job,firstname,join_date,cre...  ...        12
20              join_date,year_income,id,credit_score,age,last...  ...        12
21              firstname,age,lastname,id,job,year_income,regi...  ...        12
22              job,firstname,gender,lastname,region,age,id,cr...  ...        12
23              age,gender,lastname,credit_score,id,firstname,...  ...        12
24              id,gender,join_date,age,region,firstname,year_...  ...        12
25              year_income,id,lastname,credit_score,age,job,f...  ...        12
26              job,year_income,region,id,join_date,credit_sco...  ...        12
27              age,lastname,gender,region,firstname,year_inco...  ...        12

[10 rows x 36 columns]
user_112460560
Related