How to transpose all columns to rows?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
For example, I have data like below:
F1 | F2 | F3 | F4 | F5 | F6 | F7 | F8 |
AAA0001 | AAA0002 | AAA0003 | AAA0004 | AAA0005 | AAA0006 | AAA0007 | AAA0008 |
973,880.89 | 4,782,158.16 | 121,393.21 | 282,760.28 | 298,333.72 | 89,445.06 | 106,041.15 | 104,381.48 |
The number of columns is not fixed (could be more than 1K).
I want to change my data to below:
F1 | AAA0001 | 973,880.89 |
F2 | AAA0002 | 4,782,158.16 |
F3 | AAA0003 | 121,393.21 |
F4 | AAA0004 | 282,760.28 |
F5 | AAA0005 | 298,333.72 |
F6 | AAA0006 | 89,445.06 |
F7 | AAA0007 | 106,041.15 |
F8 | AAA0008 | 104,381.48 |
I have tried to use "Transpose", but I do not get what I want.
Any suggestion? Thanks.
Solved! Go to Solution.
- Labels:
- Parse
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Use the transpose tool and have everything else as a data field..
Then use a summarize tool. Grouping by the name field and then concatenating the value field..
Finally use the text to columns on the concatenated field to split it out to different columns.
Ben
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Just to add to this, having though about it for a bit longer, would be the following:
Record ID your data. When doing the transpose use the recordID as a key field and then transpose everything else.
Then use the cross-tab tool. Your name field will be your 'group by', your RecordID field will be the header field, and then the value field will be your 'value', as you will only have one record in each at the bottom of the cross-tab tool select 'concatenate'.
This will be dynamic to give you three columns if you had three rows. Whereas the first solution you would have to change the number of columns to split to.
Ben
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Answer:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This is what i have i am trying to use your idea but it deos not seem working for me. I have this data set:
Col A Col B Col C
Light on AB WT 0
Light on AB WT 0
Light on AB WT 3072
Light on AB WT 704
Light on AB WT 626
Light on AB WT 634
Light on AB WT 1924
Light on AB WT 2472
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on AB 272 25 µM 0
Light on AB 272 25 µM 0
Light on AB 272 25 µM 1376
Light on AB 272 25 µM 0
Light on AB 272 25 µM 814
Light on AB 272 25 µM 0
Light on AB 272 25 µM 4
Light on AB 272 25 µM 0
Light on ndufs2 272 25 µM 0
Light on ndufs2 272 25 µM 0
Light on ndufs2 272 25 µM 60
Light on ndufs2 272 25 µM 1594
Light on ndufs2 272 25 µM 146
Light on ndufs2 272 25 µM 2104
Light on ndufs2 272 25 µM 0
Light on ndufs2 272 25 µM 0
Light on AB 259 50 µM 0
Light on AB 259 50 µM 276
Light on AB 259 50 µM 126
Light on AB 259 50 µM 222
Light on AB 259 50 µM 0
Light on AB 259 50 µM 0
Light on AB 259 50 µM 70
Light on AB 259 50 µM 4522
what i want to do is to transpose and get a matrix like this:
1. | 0. | 0. | 3072. | 704. | 626. | 634. | 1924. | 2472. |
2. | 0. | 0. | 3406. | 2036. | 398. | 1222. | 2628. | 0. |
3. | 20. | 8. | 2744. | 1926. | 306. | 126. | 2492. | 0. |
4. | 0. | 10. | 2490. | 2798. | 0. | 2268. | 2442. | 40. |
How do i solve this thanks
