How to transform a single column dataframe to a single row dataframe in Pandas?

Question

I got the following dataframe:

                        0
0                  Aachen
1                       1
2                   Valid
3                      L5
4                      21
5                    Fell
6  01/01/1880 12:00:00 AM
7                  50.775
8                 6.08333
9   (50.775000, 6.083330)

And I want it to look like:

name    id  nametype    recclass    mass (g)    fall    year    reclat  reclong GeoLocation
Aachen  1   Valid   L5  21  Fell    1-1-1880 12:00:00 AM    50.775000   6.083330    (50.775000, 6.083330)

I tried using pivot() an pivot_table() in pandas, but I always get a KeyError:

pv_df = df.pivot_table(index=None, columns='0')

Traceback (most recent call last):
  File "/home/alaaeddine/PycharmProjects/test/expectations.py", line 18, in <module>
    pv_df = df.pivot_table(index=None, columns='0')
  File "/usr/local/lib/python3.6/dist-packages/pandas/core/frame.py", line 5303, in pivot_table
    margins_name=margins_name)
  File "/usr/local/lib/python3.6/dist-packages/pandas/core/reshape/pivot.py", line 86, in pivot_table
    grouped = data.groupby(keys, observed=False)
  File "/usr/local/lib/python3.6/dist-packages/pandas/core/generic.py", line 6665, in groupby
    observed=observed, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/pandas/core/groupby/groupby.py", line 2152, in groupby
    return klass(obj, by, **kwds)
  File "/usr/local/lib/python3.6/dist-packages/pandas/core/groupby/groupby.py", line 599, in __init__
    mutated=self.mutated)
  File "/usr/local/lib/python3.6/dist-packages/pandas/core/groupby/groupby.py", line 3291, in _get_grouper
    raise KeyError(gpr)
KeyError: '0'

Also tried to set the index and stack/unstack but it does not seem to work.

Any clues on what I am might be missing/doing worng?

Thanks!

You can always transpose the dataframe using df.T. How did you create the dataframe? May be the problem can be solved easily by fixing that. — Vaishali
– Vaishali, Commented Jan 30, 2019 at 18:56
Is your DataFrame really only 10 rows long, or is it this just an example (and you actually have like a 10*N long DataFrame)? In that case a simple .T won't help, and you'll need to pivot after a groupby.cumcount(), assuming your rows always appear in the same order — ALollz
– ALollz, Commented Jan 30, 2019 at 19:15
@ALollz indeed this is a sample and not the big dataframe. My idea was as follows: - load a csv in a dataset. - convert every row of the dataset into dataframe. - test the dataframe agaist some expectations. The way I found to create a dataframe form a dataset row is dt.values[loop_index]. This creates the dataframe I posted in my question. — ds_enth
– ds_enth, Commented Jan 30, 2019 at 19:21
So you just have one long column, and you need to take every 10 rows and pivot them to a single row with 10 columns? — ALollz
– ALollz, Commented Jan 30, 2019 at 19:22
@ALollz Yes. Every 10 rows should be converted in a single row with 10 columns. May be I'm missing a much easier way to do it. But since I'm new to Pandas, I could not find something more simple. — ds_enth
– ds_enth, Commented Jan 30, 2019 at 19:26

Felipe Gonzalez · Accepted Answer · 2019-01-30 18:54:30Z

3

What you need is not to pivot, but to transpose the dataframe. Here is the documentation.

df = df.transpose()

answered Jan 30, 2019 at 18:54

Felipe Gonzalez

3531 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Edeki Okoh Over a year ago

Or to be more pythonic: df = df.T

G. Anderson Over a year ago

Or, even simpler, df.T

harpan Over a year ago

Would that match with OP's output? Have you tried it?

ds_enth Over a year ago

df.transpose output (does not seem to work): ``` <bound method DataFrame.transpose of 0 0 Aachen 1 1 2 Valid 3 L5 4 21 5 Fell 6 01/01/1880 12:00:00 AM 7 50.775 8 6.08333 9 (50.775000, 6.083330)> ```

r.ook Over a year ago

Don't forget to actually call it. df.transpose(). The () are important.

|

Edeki Okoh · Accepted Answer · 2019-01-30 18:55:04Z

1

You can use the reshape tool to do this. In this case you will want to make sure to access the values of DataFrame, and reshape it so that it is 1 row and as many columns as needed. To do this, you will need to use the following code below:

pv_df = pd.DataFrame(df.values.reshape(1,-1))

answered Jan 30, 2019 at 18:55

Edeki Okoh

1,85917 silver badges28 bronze badges

Comments

harpan · Accepted Answer · 2019-01-30 19:02:56Z

0

You need:

df = pd.DataFrame(data.T)
df.columns = ['name', 'id',  'nametype',    'recclass',    'mass (g)',    'fall',    'year',    'reclat',  'reclong', 'GeoLocation']

Output:

    name    id  nametype    recclass    mass (g)    fall    year        reclat  reclong GeoLocation
0   Aachen   1  Valid       L5          21          Fell      01/01/1880 12:00:00 AM                      50.775                     6.08333       (50.775000, 6.083330)

answered Jan 30, 2019 at 19:02

harpan

8,6412 gold badges22 silver badges40 bronze badges

Comments

ds_enth · Accepted Answer · 2019-01-30 19:55:07Z

0

Thanks for your help guys, I could make work using the following code:

import pandas as pd

#create the dataset
dt = pd.read_csv('/path/to/file.csv')

# extract the first row in a new dataframe
df = pd.DataFrame(dt.values[0])

# transpose the new dataframe
df = df.transpose()

# rename the columns of the new dataframe 
df.columns = ['name', 'id', 'nametype', 'recclass', 'mass (g)', 'fall', 'year', 'reclat', 'reclong', 'GeoLocation']

This is the output:

name id nametype recclass mass (g)  fall                    year  reclat  \
0  Aachen  1    Valid       L5       21  Fell  01/01/1880 12:00:00 AM  50.775   

   reclong            GeoLocation  
0  6.08333  (50.775000, 6.083330)

answered Jan 30, 2019 at 19:55

ds_enth

591 gold badge4 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to transform a single column dataframe to a single row dataframe in Pandas?

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related