CODEWITHSUNDEEP
pythonpandasdataframe
Lostsoul
Lostsoul

Reputation: 26067

In pandas, can I add a new row with the colum names?

I'm dealing with messy data and upon import the header of the DF has values I actually need, Can I convert these to rows and perhaps remove the column name or rename them? My end goal here is to convert this to a json from pandas.

df

    Url                     https://www.intuit.com/oidam/intuit/ic/en_us/content/intuit-cr-report-2020-icom.pdf
0   company_id              53
1   current_value scope 1   1912
2   current_value scope 2   5136
3   current_value scope 3   30476

You can see it's all key values except the header for the first column is Url so when I later do, df.to_json() I get:

{"Url":{"0":"company_id","1":"current_value scope 1","2":"current_value scope 2","3":"current_value scope 3"},"https:\/\/www.intuit.com\/oidam\/intuit\/ic\/en_us\/content\/intuit-cr-report-2020-icom.pdf":{"0":53,"1":1912,"2":5136,"3":30476}}

Upvotes: 0

Views: 256

Answers (1)

user7864386
user7864386

Reputation:

You could transpose and reset_index twice:

out = df.T.reset_index().T.reset_index(drop=True)

Output:

                          0                                                  1
0                       Url  https://www.intuit.com/oidam/intuit/ic/en_us/c...
1                company_id                                                 53
2     current_value scope 1                                               1912
3     current_value scope 2                                               5136
4     current_value scope 3                                              30476

Then again, if we look at the json, it seems the below line makes more sense:

out = df.T.reset_index().T.set_index(0)

which produces the json string below:

'{"1":{"Url":"https:\\/\\/www.intuit.com\\/oidam\\/intuit\\/ic\\/en_us\\/content\\/intuit-cr-report-2020-icom.pdf","   company_id":53,"   current_value scope 1":1912,"   current_value scope 2":5136,"   current_value scope 3":30476}}'

Upvotes: 1

Related Questions