How to convert a string that has the headers and values with ID per string into a dataframe in R Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern) Data science time! April 2019 and salary with experience The Ask Question Wizard is Live!How to sort a dataframe by multiple column(s)How do I replace NA values with zeros in an R dataframe?Use a list of values to select rows from a pandas dataframeHow to change the order of DataFrame columns?How to drop rows of Pandas DataFrame whose value in certain columns is NaNHow do I get the row count of a pandas DataFrame?How to iterate over rows in a DataFrame in Pandas?Select rows from a DataFrame based on values in a column in pandasGet list from pandas DataFrame column headersConvert list of dictionaries to a pandas DataFrame
/bin/ls sorts differently than just ls
Calculating the expected value of truncated normal
Not within Jobscope - Aggravated injury
What is the evidence that custom checks in Northern Ireland are going to result in violence?
How do I deal with an erroneously large refund?
Like totally amazing interchangeable sister outfit accessory swapping or whatever
What does こした mean?
Is it accepted to use working hours to read general interest books?
How long can a nation maintain a technological edge over the rest of the world?
Has a Nobel Peace laureate ever been accused of war crimes?
Translate text contents of an existing file from lower to upper case and copy to a new file
Did war bonds have better investment alternatives during WWII?
Does a Draconic Bloodline sorcerer's doubled proficiency bonus for Charisma checks against dragons apply to all dragon types or only the chosen one?
Where to find documentation for `whois` command options?
SQL Server placement of master database files vs resource database files
Is a self contained air-bullet cartridge feasible?
My admission is revoked after accepting the admission offer
Married in secret, can marital status in passport be changed at a later date?
Processing ADC conversion result: DMA vs Processor Registers
The 'gros' functor from schemes into (strictly) locally ringed topoi
Why is water being consumed when my shutoff valve is closed?
Does Prince Arnaud cause someone holding the Princess to lose?
When does Bran Stark remember Jamie pushing him?
Why do people think Winterfell crypts is the safest place for women, children & old people?
How to convert a string that has the headers and values with ID per string into a dataframe in R
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern)
Data science time! April 2019 and salary with experience
The Ask Question Wizard is Live!How to sort a dataframe by multiple column(s)How do I replace NA values with zeros in an R dataframe?Use a list of values to select rows from a pandas dataframeHow to change the order of DataFrame columns?How to drop rows of Pandas DataFrame whose value in certain columns is NaNHow do I get the row count of a pandas DataFrame?How to iterate over rows in a DataFrame in Pandas?Select rows from a DataFrame based on values in a column in pandasGet list from pandas DataFrame column headersConvert list of dictionaries to a pandas DataFrame
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I need to know how to convert strings in the text file into a data frame for analysis.
I got one line which has an ID per customer, which has the column heading and value in and is separated by semi colon ';'
For example:
ID=12345;TimeStamp=""2019-02-26 00:15:42"";Event=StatusEvent;Status=""WiLoMonitorStart"";Text=""mnew inactivity failure on cable"";
The column headings are ID, TimeStamp, Event, Status, Text and any others that come before the equal "=" sign.
The values under the column headings will be after the equals sign "=", see the picture this is the end result I want to achieve.
Statements
"ID=12345;TimeStamp=""2019-02-26 00:15:42"";Event=StatusEvent;Status=""WiLoMonitorStart"";Text=""mnew inactivity failure on cable"";"
"ID=12346;TimeStamp=""2019-02-26 00:15:43"";Event=StatusEvent;Status=""MetroCode"";Text=""AU"";"
"ID=12347;TimeStamp=""2019-02-26 00:15:43"";Event=StatusEvent;Status=""LoWiValidation"";Text=""Password validation 2.5GHz for AES: BigBong"";"
"ID=12349;TimeStamp=""2019-02-26 00:15:42"";Event=DomainEvent;MacAddress=""AB:23:34:EF:YN:OT"";LogTime=""2019-02-26 00:15:48"";Domain=""Willing ind"";SecondaryDomain=""No_Perl"";"
"ID=12351;TimeStamp=""2019-02-26 00:15:45"";Event=CollectionCallEvent;SerialNumber=""34121"";"
"ID=12352;TimeStamp=""2019-02-26 00:15:46"";Event=CollectionCallEvent;SerialNumber=""34151"";Url=""werlkdfa/vierjwerret/vre34f3/df343rsdf343+t45rf/dfgr3443"";"
}
You can see the semi colon ";" separates each variable, can someone be able to separate and make R identify what is a column heading and what is a value to be placed underneath the respected heading with respect to the customer ID (the primary key).
Note that each line may not have the same column headings for the next one.
The image supplied is what I want to achieve in the end but I am having great difficulty to do so in R. It is not a json file or a XML format it is a file that was dumped in text format where I need to extract and analyse the information in a dataframe format before I can do any insights.
Any suggestions? Would there be a better way than say using regular expressions? E.g. stringr package?
r dataframe
edited Mar 9 at 8:12
Dinesh Vishwakarma
1881115
asked Mar 9 at 4:28
JustinJustin
11
add a comment |
I need to know how to convert strings in the text file into a data frame for analysis.
I got one line which has an ID per customer, which has the column heading and value in and is separated by semi colon ';'
For example:
ID=12345;TimeStamp=""2019-02-26 00:15:42"";Event=StatusEvent;Status=""WiLoMonitorStart"";Text=""mnew inactivity failure on cable"";
The column headings are ID, TimeStamp, Event, Status, Text and any others that come before the equal "=" sign.
The values under the column headings will be after the equals sign "=", see the picture this is the end result I want to achieve.
Statements
"ID=12345;TimeStamp=""2019-02-26 00:15:42"";Event=StatusEvent;Status=""WiLoMonitorStart"";Text=""mnew inactivity failure on cable"";"
"ID=12346;TimeStamp=""2019-02-26 00:15:43"";Event=StatusEvent;Status=""MetroCode"";Text=""AU"";"
"ID=12347;TimeStamp=""2019-02-26 00:15:43"";Event=StatusEvent;Status=""LoWiValidation"";Text=""Password validation 2.5GHz for AES: BigBong"";"
"ID=12349;TimeStamp=""2019-02-26 00:15:42"";Event=DomainEvent;MacAddress=""AB:23:34:EF:YN:OT"";LogTime=""2019-02-26 00:15:48"";Domain=""Willing ind"";SecondaryDomain=""No_Perl"";"
"ID=12351;TimeStamp=""2019-02-26 00:15:45"";Event=CollectionCallEvent;SerialNumber=""34121"";"
"ID=12352;TimeStamp=""2019-02-26 00:15:46"";Event=CollectionCallEvent;SerialNumber=""34151"";Url=""werlkdfa/vierjwerret/vre34f3/df343rsdf343+t45rf/dfgr3443"";"
}
You can see the semi colon ";" separates each variable, can someone be able to separate and make R identify what is a column heading and what is a value to be placed underneath the respected heading with respect to the customer ID (the primary key).
Note that each line may not have the same column headings for the next one.
The image supplied is what I want to achieve in the end but I am having great difficulty to do so in R. It is not a json file or a XML format it is a file that was dumped in text format where I need to extract and analyse the information in a dataframe format before I can do any insights.
Any suggestions? Would there be a better way than say using regular expressions? E.g. stringr package?
r dataframe
edited Mar 9 at 8:12
Dinesh Vishwakarma
1881115
asked Mar 9 at 4:28
JustinJustin
11
1
by the way besides values from ID and Event headings all having double quotation marks on the values. The quotation marks are a nuisance and need to be removed.
– Justin
Mar 9 at 4:30
2
Please do not attempt to amend your question by using comments (and don't even respond to clarifying remarks in comments). Learn to use the edit facilities in SO. You should also clarify in what form this test currently resides. Is it a text file?
– 42-
Mar 9 at 4:44
add a comment |
I need to know how to convert strings in the text file into a data frame for analysis.
I got one line which has an ID per customer, which has the column heading and value in and is separated by semi colon ';'
For example:
ID=12345;TimeStamp=""2019-02-26 00:15:42"";Event=StatusEvent;Status=""WiLoMonitorStart"";Text=""mnew inactivity failure on cable"";
The column headings are ID, TimeStamp, Event, Status, Text and any others that come before the equal "=" sign.
The values under the column headings will be after the equals sign "=", see the picture this is the end result I want to achieve.
Statements
"ID=12345;TimeStamp=""2019-02-26 00:15:42"";Event=StatusEvent;Status=""WiLoMonitorStart"";Text=""mnew inactivity failure on cable"";"
"ID=12346;TimeStamp=""2019-02-26 00:15:43"";Event=StatusEvent;Status=""MetroCode"";Text=""AU"";"
"ID=12347;TimeStamp=""2019-02-26 00:15:43"";Event=StatusEvent;Status=""LoWiValidation"";Text=""Password validation 2.5GHz for AES: BigBong"";"
"ID=12349;TimeStamp=""2019-02-26 00:15:42"";Event=DomainEvent;MacAddress=""AB:23:34:EF:YN:OT"";LogTime=""2019-02-26 00:15:48"";Domain=""Willing ind"";SecondaryDomain=""No_Perl"";"
"ID=12351;TimeStamp=""2019-02-26 00:15:45"";Event=CollectionCallEvent;SerialNumber=""34121"";"
"ID=12352;TimeStamp=""2019-02-26 00:15:46"";Event=CollectionCallEvent;SerialNumber=""34151"";Url=""werlkdfa/vierjwerret/vre34f3/df343rsdf343+t45rf/dfgr3443"";"
}
You can see the semi colon ";" separates each variable, can someone be able to separate and make R identify what is a column heading and what is a value to be placed underneath the respected heading with respect to the customer ID (the primary key).
Note that each line may not have the same column headings for the next one.
The image supplied is what I want to achieve in the end but I am having great difficulty to do so in R. It is not a json file or a XML format it is a file that was dumped in text format where I need to extract and analyse the information in a dataframe format before I can do any insights.
Any suggestions? Would there be a better way than say using regular expressions? E.g. stringr package?
r dataframe
edited Mar 9 at 8:12
Dinesh Vishwakarma
1881115
asked Mar 9 at 4:28
JustinJustin
11
I need to know how to convert strings in the text file into a data frame for analysis.
I got one line which has an ID per customer, which has the column heading and value in and is separated by semi colon ';'
For example:
ID=12345;TimeStamp=""2019-02-26 00:15:42"";Event=StatusEvent;Status=""WiLoMonitorStart"";Text=""mnew inactivity failure on cable"";
The column headings are ID, TimeStamp, Event, Status, Text and any others that come before the equal "=" sign.
The values under the column headings will be after the equals sign "=", see the picture this is the end result I want to achieve.
Statements
"ID=12345;TimeStamp=""2019-02-26 00:15:42"";Event=StatusEvent;Status=""WiLoMonitorStart"";Text=""mnew inactivity failure on cable"";"
"ID=12346;TimeStamp=""2019-02-26 00:15:43"";Event=StatusEvent;Status=""MetroCode"";Text=""AU"";"
"ID=12347;TimeStamp=""2019-02-26 00:15:43"";Event=StatusEvent;Status=""LoWiValidation"";Text=""Password validation 2.5GHz for AES: BigBong"";"
"ID=12349;TimeStamp=""2019-02-26 00:15:42"";Event=DomainEvent;MacAddress=""AB:23:34:EF:YN:OT"";LogTime=""2019-02-26 00:15:48"";Domain=""Willing ind"";SecondaryDomain=""No_Perl"";"
"ID=12351;TimeStamp=""2019-02-26 00:15:45"";Event=CollectionCallEvent;SerialNumber=""34121"";"
"ID=12352;TimeStamp=""2019-02-26 00:15:46"";Event=CollectionCallEvent;SerialNumber=""34151"";Url=""werlkdfa/vierjwerret/vre34f3/df343rsdf343+t45rf/dfgr3443"";"
}
You can see the semi colon ";" separates each variable, can someone be able to separate and make R identify what is a column heading and what is a value to be placed underneath the respected heading with respect to the customer ID (the primary key).
Note that each line may not have the same column headings for the next one.
The image supplied is what I want to achieve in the end but I am having great difficulty to do so in R. It is not a json file or a XML format it is a file that was dumped in text format where I need to extract and analyse the information in a dataframe format before I can do any insights.
Any suggestions? Would there be a better way than say using regular expressions? E.g. stringr package?
r dataframe
r dataframe
edited Mar 9 at 8:12
Dinesh Vishwakarma
1881115
asked Mar 9 at 4:28
JustinJustin
11
edited Mar 9 at 8:12
Dinesh Vishwakarma
1881115
asked Mar 9 at 4:28
JustinJustin
11
edited Mar 9 at 8:12
Dinesh Vishwakarma
1881115
edited Mar 9 at 8:12
Dinesh Vishwakarma
1881115
edited Mar 9 at 8:12
Dinesh Vishwakarma
1881115
1881115
asked Mar 9 at 4:28
JustinJustin
11
asked Mar 9 at 4:28
JustinJustin
11
asked Mar 9 at 4:28
JustinJustin
11
11
1
by the way besides values from ID and Event headings all having double quotation marks on the values. The quotation marks are a nuisance and need to be removed.
– Justin
Mar 9 at 4:30
2
Please do not attempt to amend your question by using comments (and don't even respond to clarifying remarks in comments). Learn to use the edit facilities in SO. You should also clarify in what form this test currently resides. Is it a text file?
– 42-
Mar 9 at 4:44
add a comment |
1
by the way besides values from ID and Event headings all having double quotation marks on the values. The quotation marks are a nuisance and need to be removed.
– Justin
Mar 9 at 4:30
2
Please do not attempt to amend your question by using comments (and don't even respond to clarifying remarks in comments). Learn to use the edit facilities in SO. You should also clarify in what form this test currently resides. Is it a text file?
– 42-
Mar 9 at 4:44
1
1
by the way besides values from ID and Event headings all having double quotation marks on the values. The quotation marks are a nuisance and need to be removed.
– Justin
Mar 9 at 4:30
by the way besides values from ID and Event headings all having double quotation marks on the values. The quotation marks are a nuisance and need to be removed.
– Justin
Mar 9 at 4:30
2
2
Please do not attempt to amend your question by using comments (and don't even respond to clarifying remarks in comments). Learn to use the edit facilities in SO. You should also clarify in what form this test currently resides. Is it a text file?
– 42-
Mar 9 at 4:44
Please do not attempt to amend your question by using comments (and don't even respond to clarifying remarks in comments). Learn to use the edit facilities in SO. You should also clarify in what form this test currently resides. Is it a text file?
– 42-
Mar 9 at 4:44
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55073985%2fhow-to-convert-a-string-that-has-the-headers-and-values-with-id-per-string-into%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55073985%2fhow-to-convert-a-string-that-has-the-headers-and-values-with-id-per-string-into%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
by the way besides values from ID and Event headings all having double quotation marks on the values. The quotation marks are a nuisance and need to be removed.
– Justin
Mar 9 at 4:30
2
Please do not attempt to amend your question by using comments (and don't even respond to clarifying remarks in comments). Learn to use the edit facilities in SO. You should also clarify in what form this test currently resides. Is it a text file?
– 42-
Mar 9 at 4:44