Ask Your Question

Convert table from long to wide format [closed]

asked 2015-12-05 17:25:13 +0100

Devon Buchanan gravatar image

updated 2015-12-12 14:04:25 +0100

Given this data in long format:

Day      Meal       Food
Monday   Breakfast  Cereal
Monday   Lunch      Ham sandwich
Monday   Dinner     Tuna bake
Tuesday  Breakfast  Porridge
Tuesday  Lunch      Enchilada
Tuesday  Dinner     Spaghetti

It is possible to turn it into the equivalent wide format:

         Breakfast  Lunch         Dinner
Monday   Cereal     Ham sandwich  Tuna bake
Tuesday  Porridge   Enchilada     Spaghetti

Note how the categories in the first column of the long format have turned into row names, while the categories in the second column have turned into column names. The data displayed in the cells is the third column.

I've tried to use pivot tables in Libreoffice on OS X 10.11.1, but these only allow me to display aggregate data about the dinner column such as SUM or AVG, not the data entries themselves.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-12 19:21:40.512452


What do you want to do with the two values for Tuesday-Lunch?

m.a.riosv gravatar imagem.a.riosv ( 2015-12-08 00:30:17 +0100 )edit

Sorry, there shouldn't have been two values for Tuesday-Lunch. Edited to fix.

Devon Buchanan gravatar imageDevon Buchanan ( 2015-12-12 14:04:55 +0100 )edit

I guess 2 values it is a copying error (dinner expected) but I have another question: is it for one week or more?

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-12-12 14:24:58 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2015-12-12 18:11:52 +0100

m.a.riosv gravatar image

Please take a look if this example does what you like.
If there are duplicates only the last is retrieved.


image description

edit flag offensive delete link more


That does what I asked, thank you.

Devon Buchanan gravatar imageDevon Buchanan ( 2015-12-12 19:00:23 +0100 )edit

Question Tools

1 follower


Asked: 2015-12-05 17:25:13 +0100

Seen: 332 times

Last updated: Dec 12 '15