Excel data extraction from column
siri
I have data from hospital on patients deaths. The data is not arranged properly, the data is as in link below
All the dates should in either column DOA (H Column) or DOD (I Column) or MB (J Column) and the rest of text should arranged in a separate column. Can anyone please help me to clean this data, as I have more than 5000 observations. enter link description here
+-------+-----------+-------------+---------------+---------+-------------------+----------------------------------------------------+------------------+---------------------------------+------------+-----------------------+
| Sl.NO | District | State P No | Age In Years | Sex | Symptoms | Co-Morbidities | DOA | DOD | MB Date | Notes |
+=======+===========+=============+===============+=========+===================+====================================================+==================+=================================+============+=======================+
| 10 | X4 | 6553 | 53 | F | Fever | Cold | Cough | Thyroid disease | 10-06-2020 | 20-06-2020 |
+-------+-----------+-------------+---------------+---------+-------------------+----------------------------------------------------+------------------+---------------------------------+------------+-----------------------+
| 11 | X5 | 8872 | 62 | M | Fever | Diabetes Mellitus | 16-06-2020 | 16-06-2020 | 21-06-2020 | |
+-------+-----------+-------------+---------------+---------+-------------------+----------------------------------------------------+------------------+---------------------------------+------------+-----------------------+
| 12 | X5 | 8880 | 55 | M | Pneumonia | Respiratory distress Obese, Chronic Alcoholic | 18-06-2020 | 20-06-2020 | 21-06-2020 | |
+-------+-----------+-------------+---------------+---------+-------------------+----------------------------------------------------+------------------+---------------------------------+------------+-----------------------+
| 13 | X2 | 9149 | 70 | M | Loss of Appetite | Weakness, Hypertension | 18-06-2020 | 18-06-2020 | 21-06-2020 | |
+-------+-----------+-------------+---------------+---------+-------------------+----------------------------------------------------+------------------+---------------------------------+------------+-----------------------+
| 14 | X3 | 9150 | 46 | M | Weakness | Convulsions, Hypertension | 17-06-2020 | 18-06-2020 | 21-06-2020 | |
+-------+-----------+-------------+---------------+---------+-------------------+----------------------------------------------------+------------------+---------------------------------+------------+-----------------------+
| 15 | X4 | 7732 | 60 | Female | Fever | Cough | Breathlessness | uncontrolled Diabetes Mellitus | 17-06-2020 | 22-06-2020 |
+-------+-----------+-------------+---------------+---------+-------------------+----------------------------------------------------+------------------+---------------------------------+------------+-----------------------+
| 16 | X5 | 9237 | 90 | M | Asymptomatic | Hypertension | | 20-06-2020 | 22-06-2020 | Died at his residence |
+-------+-----------+-------------+---------------+---------+-------------------+----------------------------------------------------+------------------+---------------------------------+------------+-----------------------+
Ron Rosenfeld
You can obtain your desired output using Power Query, available in Windows Excel 2010+ and Office 365 Excel
- Select some cell in your original table
Data => Get&Transform => From Table/Range- When the PQ UI opens, navigate to
Home => Advanced Editor - Make note of the Table Name in Line 2 of the code.
- Replace the existing code with the M-Code below
- Change the table name in line 2 of the pasted code to your "real" table name
- Examine any comments, and also the
Applied Stepswindow, to better understand the algorithm and steps
I have assumed, based on your data sample, that when there is an error, it is because there are three co-morbidities that have been split on the commas.
That being the case, I have tested to see if the DOD column contains a date.
- if DOD contains a date, then
- Concatentate the Co-Morbitides with DOA and DOD
- Get the DOA date from the MB Date Column
- Get the DOD date from the Notes Column
- Set the MB date to null
If that logic doesn't work for all, or if it is in error, it can be easily changed.
すべての処理の魔法は、Table.Group関数の引数で発生します
Mコード
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
//Replace any blanks ("") with null for easier processing down the road
#"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Sl.NO", "District", "State P No", "Age In Years", "Sex", "Symptoms", "Co-Morbidities", "DOA", "DOD", "MB Date", "Notes"}),
//Group the table by the first six columns
#"Grouped Rows" = Table.Group(#"Replaced Value", {"Sl.NO", "District", "State P No", "Age In Years", "Sex", "Symptoms"}, {
//Extract the columns to be processed using the logic shown
{"Co-Morbidities", each
if Value.Is([DOD]{0}, type datetime)
then [#"Co-Morbidities"]{0}
else Text.Combine({[#"Co-Morbidities"]{0},[DOA]{0}, [DOD]{0}},", "), type text},
{"DOA", each
if Value.Is([DOD]{0}, type datetime)
then DateTime.Date([DOA]{0})
else if [DOA]{0} = null then null
else DateTime.Date([#"MB Date"]{0}), type date},
{"DOD", each
if Value.Is([DOD]{0}, type datetime)
then DateTime.Date([DOD]{0})
else DateTime.Date([Notes]{0}), type date},
{"MB Date", each
if Value.Is([DOD]{0}, type datetime)
then DateTime.Date([#"MB Date"]{0})
else null, type date},
{"Notes", each
if Value.Is([Notes]{0}, type datetime)
then null
else [Notes]{0}}
})
in
#"Grouped Rows"
結果
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
編集
関連記事
Related 関連記事
- 1
How to extract data from an excel column to a list in Python?
- 2
Extract data from the column
- 3
Excel VBA - Get a Cells value by using Offset, based on the data from another column
- 4
Translating column-wise JSON data from Excel into a hierarchical structure with python
- 5
Unable to copy data from one Excel sheet to another when not copying to column A
- 6
Import data headers from excel doc, search web using pandas, then export to specific row/column in same excel doc
- 7
HTML Extraction through VBA/Excel
- 8
JavaScript object data extraction get data from json.stringify to php
- 9
Writing large data to a excel column cell with looping
- 10
Excel Barcode Scanner Column Data to Row
- 11
Print the data from second column
- 12
Importing data from Excel to MVC
- 13
Aggregating data from Excel into Access
- 14
How to populate array from a sheet column in Excel
- 15
Copying text from file to specified Excel column
- 16
Populate one column with data from a list and match other column data
- 17
Extract the last column from a list of data frames
- 18
Clearing certain data from column dataframe
- 19
Chi square for filtered data from column
- 20
Subtract column data from two unrelated tables
- 21
Extracting quoted and labelled data from a given column
- 22
Import data to SQL Server table from Excel
- 23
Get data from URL using Excel VBA
- 24
Exporting data from jqgrid to excel sheet
- 25
Get data from another Excel file
- 26
Import Data from Excel to Mysql Python
- 27
Auto data input from lists in excel
- 28
changing excel data formatting from vertical to horizontal
- 29
Openpyxl : need the max number of rows in a column that has data in Excel
コメントを追加