Cleaning data from excel rows into columns ( Can be done in Excel or Python ?) [closed]
up vote
-4
down vote
favorite
I have following data in (rows)excel
]1
I want to manipulate data into tabular format(like below) in excel or what ever will be feasible according to your suggestion ??
]2
Input
row1) Type: Name: Dat
row2) Address: 123 main st Age:45
row3) Phone: 23454 Zip- 456656
row4) Comment: this is notes
row5) continued in next line too
row6)
row7) Type:A Name: Alex Date: 2/3/2016
row8) Address: 45 main st Age:33
row9) Phone: 656567 Zip: 34556
row10) Comment: notessss
Expected Output
Type Name Date Address age Phone Zip Comment
123 main st 45 23454 456656 this is notes continued in next
A Alex 2/3/2016 45 main st 33 656567 34556 notesssss
python excel dataframe data-manipulation data-cleaning
asked Nov 9 at 16:51
um123
104
closed as off-topic by jpp, petezurich, pnuts, stovfl, K.Dᴀᴠɪs Nov 10 at 2:07
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example." – jpp, petezurich, K.Dᴀᴠɪs
add a comment |
up vote
-4
down vote
favorite
I have following data in (rows)excel
]1
I want to manipulate data into tabular format(like below) in excel or what ever will be feasible according to your suggestion ??
]2
Input
row1) Type: Name: Dat
row2) Address: 123 main st Age:45
row3) Phone: 23454 Zip- 456656
row4) Comment: this is notes
row5) continued in next line too
row6)
row7) Type:A Name: Alex Date: 2/3/2016
row8) Address: 45 main st Age:33
row9) Phone: 656567 Zip: 34556
row10) Comment: notessss
Expected Output
Type Name Date Address age Phone Zip Comment
123 main st 45 23454 456656 this is notes continued in next
A Alex 2/3/2016 45 main st 33 656567 34556 notesssss
python excel dataframe data-manipulation data-cleaning
asked Nov 9 at 16:51
um123
104
closed as off-topic by jpp, petezurich, pnuts, stovfl, K.Dᴀᴠɪs Nov 10 at 2:07
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example." – jpp, petezurich, K.Dᴀᴠɪs
3
I'd recommend not posting images of text without also pasting the text itself into your post - take a look at this meta post if you don't understand why.
– Random Davis
Nov 9 at 16:55
This is a very broad question for this site. What is your background? What have you tried? Are you looking for code-based solutions or application-based solutions?
– G. Anderson
Nov 9 at 16:58
Input row1) Type: Name: Dat row2) Address: 123 main st Age:45 row3) Phone: 23454 Zip- 456656 row4) Comment: this is notes row5) continued in next line too row6) row7) Type:A Name: Alex Date: 2/3/2016 row8) Address: 45 main st Age:33 row9) Phone: 656567 Zip: 34556 row10) Comment: notessss Expected Output Type Name Date Address age Phone Zip Comment 123 main st 45 23454 456656 this is notes continued in next A Alex 2/3/2016 45 main st 33 656567 34556 notesssss
– um123
Nov 9 at 17:10
I dont have experience with advance Excel or python. My background is basic sql . I am looking for code based solution
– um123
Nov 9 at 17:11
add a comment |
up vote
-4
down vote
favorite
up vote
-4
down vote
favorite
I have following data in (rows)excel
]1
I want to manipulate data into tabular format(like below) in excel or what ever will be feasible according to your suggestion ??
]2
Input
row1) Type: Name: Dat
row2) Address: 123 main st Age:45
row3) Phone: 23454 Zip- 456656
row4) Comment: this is notes
row5) continued in next line too
row6)
row7) Type:A Name: Alex Date: 2/3/2016
row8) Address: 45 main st Age:33
row9) Phone: 656567 Zip: 34556
row10) Comment: notessss
Expected Output
Type Name Date Address age Phone Zip Comment
123 main st 45 23454 456656 this is notes continued in next
A Alex 2/3/2016 45 main st 33 656567 34556 notesssss
python excel dataframe data-manipulation data-cleaning
asked Nov 9 at 16:51
um123
104
I have following data in (rows)excel
]1
I want to manipulate data into tabular format(like below) in excel or what ever will be feasible according to your suggestion ??
]2
Input
row1) Type: Name: Dat
row2) Address: 123 main st Age:45
row3) Phone: 23454 Zip- 456656
row4) Comment: this is notes
row5) continued in next line too
row6)
row7) Type:A Name: Alex Date: 2/3/2016
row8) Address: 45 main st Age:33
row9) Phone: 656567 Zip: 34556
row10) Comment: notessss
Expected Output
Type Name Date Address age Phone Zip Comment
123 main st 45 23454 456656 this is notes continued in next
A Alex 2/3/2016 45 main st 33 656567 34556 notesssss
python excel dataframe data-manipulation data-cleaning
python excel dataframe data-manipulation data-cleaning
asked Nov 9 at 16:51
um123
104
asked Nov 9 at 16:51
um123
104
edited Nov 9 at 17:20
asked Nov 9 at 16:51
um123
104
asked Nov 9 at 16:51
um123
104
asked Nov 9 at 16:51
um123
104
104
closed as off-topic by jpp, petezurich, pnuts, stovfl, K.Dᴀᴠɪs Nov 10 at 2:07
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example." – jpp, petezurich, K.Dᴀᴠɪs
closed as off-topic by jpp, petezurich, pnuts, stovfl, K.Dᴀᴠɪs Nov 10 at 2:07
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example." – jpp, petezurich, K.Dᴀᴠɪs
3
I'd recommend not posting images of text without also pasting the text itself into your post - take a look at this meta post if you don't understand why.
– Random Davis
Nov 9 at 16:55
This is a very broad question for this site. What is your background? What have you tried? Are you looking for code-based solutions or application-based solutions?
– G. Anderson
Nov 9 at 16:58
Input row1) Type: Name: Dat row2) Address: 123 main st Age:45 row3) Phone: 23454 Zip- 456656 row4) Comment: this is notes row5) continued in next line too row6) row7) Type:A Name: Alex Date: 2/3/2016 row8) Address: 45 main st Age:33 row9) Phone: 656567 Zip: 34556 row10) Comment: notessss Expected Output Type Name Date Address age Phone Zip Comment 123 main st 45 23454 456656 this is notes continued in next A Alex 2/3/2016 45 main st 33 656567 34556 notesssss
– um123
Nov 9 at 17:10
I dont have experience with advance Excel or python. My background is basic sql . I am looking for code based solution
– um123
Nov 9 at 17:11
add a comment |
3
I'd recommend not posting images of text without also pasting the text itself into your post - take a look at this meta post if you don't understand why.
– Random Davis
Nov 9 at 16:55
This is a very broad question for this site. What is your background? What have you tried? Are you looking for code-based solutions or application-based solutions?
– G. Anderson
Nov 9 at 16:58
Input row1) Type: Name: Dat row2) Address: 123 main st Age:45 row3) Phone: 23454 Zip- 456656 row4) Comment: this is notes row5) continued in next line too row6) row7) Type:A Name: Alex Date: 2/3/2016 row8) Address: 45 main st Age:33 row9) Phone: 656567 Zip: 34556 row10) Comment: notessss Expected Output Type Name Date Address age Phone Zip Comment 123 main st 45 23454 456656 this is notes continued in next A Alex 2/3/2016 45 main st 33 656567 34556 notesssss
– um123
Nov 9 at 17:10
I dont have experience with advance Excel or python. My background is basic sql . I am looking for code based solution
– um123
Nov 9 at 17:11
3
3
I'd recommend not posting images of text without also pasting the text itself into your post - take a look at this meta post if you don't understand why.
– Random Davis
Nov 9 at 16:55
I'd recommend not posting images of text without also pasting the text itself into your post - take a look at this meta post if you don't understand why.
– Random Davis
Nov 9 at 16:55
This is a very broad question for this site. What is your background? What have you tried? Are you looking for code-based solutions or application-based solutions?
– G. Anderson
Nov 9 at 16:58
This is a very broad question for this site. What is your background? What have you tried? Are you looking for code-based solutions or application-based solutions?
– G. Anderson
Nov 9 at 16:58
Input row1) Type: Name: Dat row2) Address: 123 main st Age:45 row3) Phone: 23454 Zip- 456656 row4) Comment: this is notes row5) continued in next line too row6) row7) Type:A Name: Alex Date: 2/3/2016 row8) Address: 45 main st Age:33 row9) Phone: 656567 Zip: 34556 row10) Comment: notessss Expected Output Type Name Date Address age Phone Zip Comment 123 main st 45 23454 456656 this is notes continued in next A Alex 2/3/2016 45 main st 33 656567 34556 notesssss
– um123
Nov 9 at 17:10
Input row1) Type: Name: Dat row2) Address: 123 main st Age:45 row3) Phone: 23454 Zip- 456656 row4) Comment: this is notes row5) continued in next line too row6) row7) Type:A Name: Alex Date: 2/3/2016 row8) Address: 45 main st Age:33 row9) Phone: 656567 Zip: 34556 row10) Comment: notessss Expected Output Type Name Date Address age Phone Zip Comment 123 main st 45 23454 456656 this is notes continued in next A Alex 2/3/2016 45 main st 33 656567 34556 notesssss
– um123
Nov 9 at 17:10
I dont have experience with advance Excel or python. My background is basic sql . I am looking for code based solution
– um123
Nov 9 at 17:11
I dont have experience with advance Excel or python. My background is basic sql . I am looking for code based solution
– um123
Nov 9 at 17:11
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
You could use PyExcel to read the data in the image as an array,then convert that to a numpy array and transpose it:
import pyexcel
import numpy as np
sheet = np.array(pyexcel.get_array(file_name='FileName.xlsx'))
print(sheet.transpose())
Some articles regarding this process in Excel:
https://www.techrepublic.com/blog/microsoft-office/transpose-excel-data-from-rows-to-columns-or-vice-versa/
https://support.office.com/en-us/article/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744
answered Nov 9 at 17:19
Vikhyat Agarwal
400214
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
You could use PyExcel to read the data in the image as an array,then convert that to a numpy array and transpose it:
import pyexcel
import numpy as np
sheet = np.array(pyexcel.get_array(file_name='FileName.xlsx'))
print(sheet.transpose())
Some articles regarding this process in Excel:
https://www.techrepublic.com/blog/microsoft-office/transpose-excel-data-from-rows-to-columns-or-vice-versa/
https://support.office.com/en-us/article/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744
answered Nov 9 at 17:19
Vikhyat Agarwal
400214
add a comment |
up vote
0
down vote
You could use PyExcel to read the data in the image as an array,then convert that to a numpy array and transpose it:
import pyexcel
import numpy as np
sheet = np.array(pyexcel.get_array(file_name='FileName.xlsx'))
print(sheet.transpose())
Some articles regarding this process in Excel:
https://www.techrepublic.com/blog/microsoft-office/transpose-excel-data-from-rows-to-columns-or-vice-versa/
https://support.office.com/en-us/article/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744
answered Nov 9 at 17:19
Vikhyat Agarwal
400214
add a comment |
up vote
0
down vote
up vote
0
down vote
You could use PyExcel to read the data in the image as an array,then convert that to a numpy array and transpose it:
import pyexcel
import numpy as np
sheet = np.array(pyexcel.get_array(file_name='FileName.xlsx'))
print(sheet.transpose())
Some articles regarding this process in Excel:
https://www.techrepublic.com/blog/microsoft-office/transpose-excel-data-from-rows-to-columns-or-vice-versa/
https://support.office.com/en-us/article/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744
answered Nov 9 at 17:19
Vikhyat Agarwal
400214
You could use PyExcel to read the data in the image as an array,then convert that to a numpy array and transpose it:
import pyexcel
import numpy as np
sheet = np.array(pyexcel.get_array(file_name='FileName.xlsx'))
print(sheet.transpose())
Some articles regarding this process in Excel:
https://www.techrepublic.com/blog/microsoft-office/transpose-excel-data-from-rows-to-columns-or-vice-versa/
https://support.office.com/en-us/article/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744
answered Nov 9 at 17:19
Vikhyat Agarwal
400214
answered Nov 9 at 17:19
Vikhyat Agarwal
400214
answered Nov 9 at 17:19
Vikhyat Agarwal
400214
answered Nov 9 at 17:19
Vikhyat Agarwal
400214
400214
add a comment |
add a comment |
3
I'd recommend not posting images of text without also pasting the text itself into your post - take a look at this meta post if you don't understand why.
– Random Davis
Nov 9 at 16:55
This is a very broad question for this site. What is your background? What have you tried? Are you looking for code-based solutions or application-based solutions?
– G. Anderson
Nov 9 at 16:58
Input row1) Type: Name: Dat row2) Address: 123 main st Age:45 row3) Phone: 23454 Zip- 456656 row4) Comment: this is notes row5) continued in next line too row6) row7) Type:A Name: Alex Date: 2/3/2016 row8) Address: 45 main st Age:33 row9) Phone: 656567 Zip: 34556 row10) Comment: notessss Expected Output Type Name Date Address age Phone Zip Comment 123 main st 45 23454 456656 this is notes continued in next A Alex 2/3/2016 45 main st 33 656567 34556 notesssss
– um123
Nov 9 at 17:10
I dont have experience with advance Excel or python. My background is basic sql . I am looking for code based solution
– um123
Nov 9 at 17:11