Change a Dataframe timeseries such that it only contains the most updated value based on a changes log tableSQL update from one Table to another based on a ID matchHow to return multiple values from a function?SQLite - UPSERT *not* INSERT or REPLACEPeak detection in a 2D arrayWhat are the options for storing hierarchical data in a relational database?“Large data” work flows using pandasSelect rows from a DataFrame based on values in a column in pandasDeleting DataFrame row in Pandas based on column valueChange one value based on another value in pandasUpdate Dataframe values by using another dataframe (Python)
ssTTsSTtRrriinInnnnNNNIiinngg
Ambiguity in the definition of entropy
Are there any examples of a variable being normally distributed that is *not* due to the Central Limit Theorem?
Im going to France and my passport expires June 19th
Why no variance term in Bayesian logistic regression?
Why was the shrinking from 8″ made only to 5.25″ and not smaller (4″ or less)?
How did the Super Star Destroyer Executor get destroyed exactly?
Can a virus destroy the BIOS of a modern computer?
GFCI outlets - can they be repaired? Are they really needed at the end of a circuit?
How can I determine if the org that I'm currently connected to is a scratch org?
How do I gain back my faith in my PhD degree?
How seriously should I take size and weight limits of hand luggage?
Alternative to sending password over mail?
Watching something be piped to a file live with tail
Mathematica command that allows it to read my intentions
Am I breaking OOP practice with this architecture?
Assassin's bullet with mercury
Avoiding direct proof while writing proof by induction
Can compressed videos be decoded back to their uncompresed original format?
Why is consensus so controversial in Britain?
Cursor Replacement for Newbies
Can my sorcerer use a spellbook only to collect spells and scribe scrolls, not cast?
Examples of smooth manifolds admitting inbetween one and a continuum of complex structures
How do I handle a potential work/personal life conflict as the manager of one of my friends?
Change a Dataframe timeseries such that it only contains the most updated value based on a changes log table
SQL update from one Table to another based on a ID matchHow to return multiple values from a function?SQLite - UPSERT *not* INSERT or REPLACEPeak detection in a 2D arrayWhat are the options for storing hierarchical data in a relational database?“Large data” work flows using pandasSelect rows from a DataFrame based on values in a column in pandasDeleting DataFrame row in Pandas based on column valueChange one value based on another value in pandasUpdate Dataframe values by using another dataframe (Python)
Hello everyone and thank you for all your help.
I am trying to replace the old values on a time series based on a log table that recorded all the changes to each particular column, values might have changed multiple times.
One has three columns with values that change over time.
The second table recorded the changes to the columns of the first table, it contains the old values the new values ones on separate columns it also has the dates when those values were changed.
What I am trying to accomplish would look like this,
python sql pandas dataframe series
asked Mar 7 at 22:54
Claudio MazzoniClaudio Mazzoni
359
add a comment |
Hello everyone and thank you for all your help.
I am trying to replace the old values on a time series based on a log table that recorded all the changes to each particular column, values might have changed multiple times.
One has three columns with values that change over time.
The second table recorded the changes to the columns of the first table, it contains the old values the new values ones on separate columns it also has the dates when those values were changed.
What I am trying to accomplish would look like this,
python sql pandas dataframe series
asked Mar 7 at 22:54
Claudio MazzoniClaudio Mazzoni
359
add a comment |
Hello everyone and thank you for all your help.
I am trying to replace the old values on a time series based on a log table that recorded all the changes to each particular column, values might have changed multiple times.
One has three columns with values that change over time.
The second table recorded the changes to the columns of the first table, it contains the old values the new values ones on separate columns it also has the dates when those values were changed.
What I am trying to accomplish would look like this,
python sql pandas dataframe series
asked Mar 7 at 22:54
Claudio MazzoniClaudio Mazzoni
359
Hello everyone and thank you for all your help.
I am trying to replace the old values on a time series based on a log table that recorded all the changes to each particular column, values might have changed multiple times.
One has three columns with values that change over time.
The second table recorded the changes to the columns of the first table, it contains the old values the new values ones on separate columns it also has the dates when those values were changed.
What I am trying to accomplish would look like this,
python sql pandas dataframe series
python sql pandas dataframe series
asked Mar 7 at 22:54
Claudio MazzoniClaudio Mazzoni
359
asked Mar 7 at 22:54
Claudio MazzoniClaudio Mazzoni
359
edited Mar 8 at 1:00
Claudio Mazzoni
asked Mar 7 at 22:54
Claudio MazzoniClaudio Mazzoni
359
asked Mar 7 at 22:54
Claudio MazzoniClaudio Mazzoni
359
asked Mar 7 at 22:54
Claudio MazzoniClaudio Mazzoni
359
359
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
One line solution is this:
df['new_value'] = df.merge(changes, how = 'left')['new_value'].
fillna(method = 'ffill').fillna(df.old_value)
In details:
First create sample DataFrame with the initial values:
df = pd.DataFrame('dates':['2017-01-01',
'2017-01-02', '2017-01-03', '2017-01-04', '2017-01-05'],
'old_value': ['AAA','AAA','AAA','AAA','AAA'])
dates old_value
0 2017-01-01 AAA
1 2017-01-02 AAA
2 2017-01-03 AAA
3 2017-01-04 AAA
4 2017-01-05 AAA
And a DataFrame with changes:
changes = pd.DataFrame('dates':['2017-01-02','2017-01-04' ],
'new_value':['BBB', 'CCC'])
dates new_value
0 2017-01-02 BBB
1 2017-01-04 CCC
Now join changes with to original DataFrame:
df = df.merge(changes, how = 'left')
dates old_value new_value
0 2017-01-01 AAA NaN
1 2017-01-02 AAA BBB
2 2017-01-03 AAA NaN
3 2017-01-04 AAA CCC
4 2017-01-05 AAA NaN
Then fill NA's from top to down using forward NA fill:
df['new_value'] = df['new_value'].fillna(method = 'ffill')
dates old_value new_value
0 2017-01-01 AAA NaN
1 2017-01-02 AAA BBB
2 2017-01-03 AAA BBB
3 2017-01-04 AAA CCC
4 2017-01-05 AAA CCC
Finally, fix beginning of the timeline with the original values:
df['new_value'] = df['new_value'].fillna(df.old_value)
Result:
dates old_value new_value
0 2017-01-01 AAA AAA
1 2017-01-02 AAA BBB
2 2017-01-03 AAA BBB
3 2017-01-04 AAA CCC
4 2017-01-05 AAA CCC
answered Mar 8 at 1:40
Dennis LyubyvyDennis Lyubyvy
1116
Dennis, thank you so much for your response. However what I am trying to accomplish is the opposite of what your solution is for. I have a table with values that are constantly changing across time and need to replace those old values with the most recent values.
– Claudio Mazzoni
Mar 8 at 5:10
Please host cab files somewhere or create a colander shared notebook
– Dennis Lyubyvy
Mar 9 at 1:34
add a comment |
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%2f55054107%2fchange-a-dataframe-timeseries-such-that-it-only-contains-the-most-updated-value%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
One line solution is this:
df['new_value'] = df.merge(changes, how = 'left')['new_value'].
fillna(method = 'ffill').fillna(df.old_value)
In details:
First create sample DataFrame with the initial values:
df = pd.DataFrame('dates':['2017-01-01',
'2017-01-02', '2017-01-03', '2017-01-04', '2017-01-05'],
'old_value': ['AAA','AAA','AAA','AAA','AAA'])
dates old_value
0 2017-01-01 AAA
1 2017-01-02 AAA
2 2017-01-03 AAA
3 2017-01-04 AAA
4 2017-01-05 AAA
And a DataFrame with changes:
changes = pd.DataFrame('dates':['2017-01-02','2017-01-04' ],
'new_value':['BBB', 'CCC'])
dates new_value
0 2017-01-02 BBB
1 2017-01-04 CCC
Now join changes with to original DataFrame:
df = df.merge(changes, how = 'left')
dates old_value new_value
0 2017-01-01 AAA NaN
1 2017-01-02 AAA BBB
2 2017-01-03 AAA NaN
3 2017-01-04 AAA CCC
4 2017-01-05 AAA NaN
Then fill NA's from top to down using forward NA fill:
df['new_value'] = df['new_value'].fillna(method = 'ffill')
dates old_value new_value
0 2017-01-01 AAA NaN
1 2017-01-02 AAA BBB
2 2017-01-03 AAA BBB
3 2017-01-04 AAA CCC
4 2017-01-05 AAA CCC
Finally, fix beginning of the timeline with the original values:
df['new_value'] = df['new_value'].fillna(df.old_value)
Result:
dates old_value new_value
0 2017-01-01 AAA AAA
1 2017-01-02 AAA BBB
2 2017-01-03 AAA BBB
3 2017-01-04 AAA CCC
4 2017-01-05 AAA CCC
answered Mar 8 at 1:40
Dennis LyubyvyDennis Lyubyvy
1116
Dennis, thank you so much for your response. However what I am trying to accomplish is the opposite of what your solution is for. I have a table with values that are constantly changing across time and need to replace those old values with the most recent values.
– Claudio Mazzoni
Mar 8 at 5:10
Please host cab files somewhere or create a colander shared notebook
– Dennis Lyubyvy
Mar 9 at 1:34
add a comment |
One line solution is this:
df['new_value'] = df.merge(changes, how = 'left')['new_value'].
fillna(method = 'ffill').fillna(df.old_value)
In details:
First create sample DataFrame with the initial values:
df = pd.DataFrame('dates':['2017-01-01',
'2017-01-02', '2017-01-03', '2017-01-04', '2017-01-05'],
'old_value': ['AAA','AAA','AAA','AAA','AAA'])
dates old_value
0 2017-01-01 AAA
1 2017-01-02 AAA
2 2017-01-03 AAA
3 2017-01-04 AAA
4 2017-01-05 AAA
And a DataFrame with changes:
changes = pd.DataFrame('dates':['2017-01-02','2017-01-04' ],
'new_value':['BBB', 'CCC'])
dates new_value
0 2017-01-02 BBB
1 2017-01-04 CCC
Now join changes with to original DataFrame:
df = df.merge(changes, how = 'left')
dates old_value new_value
0 2017-01-01 AAA NaN
1 2017-01-02 AAA BBB
2 2017-01-03 AAA NaN
3 2017-01-04 AAA CCC
4 2017-01-05 AAA NaN
Then fill NA's from top to down using forward NA fill:
df['new_value'] = df['new_value'].fillna(method = 'ffill')
dates old_value new_value
0 2017-01-01 AAA NaN
1 2017-01-02 AAA BBB
2 2017-01-03 AAA BBB
3 2017-01-04 AAA CCC
4 2017-01-05 AAA CCC
Finally, fix beginning of the timeline with the original values:
df['new_value'] = df['new_value'].fillna(df.old_value)
Result:
dates old_value new_value
0 2017-01-01 AAA AAA
1 2017-01-02 AAA BBB
2 2017-01-03 AAA BBB
3 2017-01-04 AAA CCC
4 2017-01-05 AAA CCC
answered Mar 8 at 1:40
Dennis LyubyvyDennis Lyubyvy
1116
Dennis, thank you so much for your response. However what I am trying to accomplish is the opposite of what your solution is for. I have a table with values that are constantly changing across time and need to replace those old values with the most recent values.
– Claudio Mazzoni
Mar 8 at 5:10
Please host cab files somewhere or create a colander shared notebook
– Dennis Lyubyvy
Mar 9 at 1:34
add a comment |
One line solution is this:
df['new_value'] = df.merge(changes, how = 'left')['new_value'].
fillna(method = 'ffill').fillna(df.old_value)
In details:
First create sample DataFrame with the initial values:
df = pd.DataFrame('dates':['2017-01-01',
'2017-01-02', '2017-01-03', '2017-01-04', '2017-01-05'],
'old_value': ['AAA','AAA','AAA','AAA','AAA'])
dates old_value
0 2017-01-01 AAA
1 2017-01-02 AAA
2 2017-01-03 AAA
3 2017-01-04 AAA
4 2017-01-05 AAA
And a DataFrame with changes:
changes = pd.DataFrame('dates':['2017-01-02','2017-01-04' ],
'new_value':['BBB', 'CCC'])
dates new_value
0 2017-01-02 BBB
1 2017-01-04 CCC
Now join changes with to original DataFrame:
df = df.merge(changes, how = 'left')
dates old_value new_value
0 2017-01-01 AAA NaN
1 2017-01-02 AAA BBB
2 2017-01-03 AAA NaN
3 2017-01-04 AAA CCC
4 2017-01-05 AAA NaN
Then fill NA's from top to down using forward NA fill:
df['new_value'] = df['new_value'].fillna(method = 'ffill')
dates old_value new_value
0 2017-01-01 AAA NaN
1 2017-01-02 AAA BBB
2 2017-01-03 AAA BBB
3 2017-01-04 AAA CCC
4 2017-01-05 AAA CCC
Finally, fix beginning of the timeline with the original values:
df['new_value'] = df['new_value'].fillna(df.old_value)
Result:
dates old_value new_value
0 2017-01-01 AAA AAA
1 2017-01-02 AAA BBB
2 2017-01-03 AAA BBB
3 2017-01-04 AAA CCC
4 2017-01-05 AAA CCC
answered Mar 8 at 1:40
Dennis LyubyvyDennis Lyubyvy
1116
One line solution is this:
df['new_value'] = df.merge(changes, how = 'left')['new_value'].
fillna(method = 'ffill').fillna(df.old_value)
In details:
First create sample DataFrame with the initial values:
df = pd.DataFrame('dates':['2017-01-01',
'2017-01-02', '2017-01-03', '2017-01-04', '2017-01-05'],
'old_value': ['AAA','AAA','AAA','AAA','AAA'])
dates old_value
0 2017-01-01 AAA
1 2017-01-02 AAA
2 2017-01-03 AAA
3 2017-01-04 AAA
4 2017-01-05 AAA
And a DataFrame with changes:
changes = pd.DataFrame('dates':['2017-01-02','2017-01-04' ],
'new_value':['BBB', 'CCC'])
dates new_value
0 2017-01-02 BBB
1 2017-01-04 CCC
Now join changes with to original DataFrame:
df = df.merge(changes, how = 'left')
dates old_value new_value
0 2017-01-01 AAA NaN
1 2017-01-02 AAA BBB
2 2017-01-03 AAA NaN
3 2017-01-04 AAA CCC
4 2017-01-05 AAA NaN
Then fill NA's from top to down using forward NA fill:
df['new_value'] = df['new_value'].fillna(method = 'ffill')
dates old_value new_value
0 2017-01-01 AAA NaN
1 2017-01-02 AAA BBB
2 2017-01-03 AAA BBB
3 2017-01-04 AAA CCC
4 2017-01-05 AAA CCC
Finally, fix beginning of the timeline with the original values:
df['new_value'] = df['new_value'].fillna(df.old_value)
Result:
dates old_value new_value
0 2017-01-01 AAA AAA
1 2017-01-02 AAA BBB
2 2017-01-03 AAA BBB
3 2017-01-04 AAA CCC
4 2017-01-05 AAA CCC
answered Mar 8 at 1:40
Dennis LyubyvyDennis Lyubyvy
1116
edited Mar 8 at 2:17
answered Mar 8 at 1:40
Dennis LyubyvyDennis Lyubyvy
1116
answered Mar 8 at 1:40
Dennis LyubyvyDennis Lyubyvy
1116
answered Mar 8 at 1:40
Dennis LyubyvyDennis Lyubyvy
1116
1116
Dennis, thank you so much for your response. However what I am trying to accomplish is the opposite of what your solution is for. I have a table with values that are constantly changing across time and need to replace those old values with the most recent values.
– Claudio Mazzoni
Mar 8 at 5:10
Please host cab files somewhere or create a colander shared notebook
– Dennis Lyubyvy
Mar 9 at 1:34
add a comment |
Dennis, thank you so much for your response. However what I am trying to accomplish is the opposite of what your solution is for. I have a table with values that are constantly changing across time and need to replace those old values with the most recent values.
– Claudio Mazzoni
Mar 8 at 5:10
Please host cab files somewhere or create a colander shared notebook
– Dennis Lyubyvy
Mar 9 at 1:34
Dennis, thank you so much for your response. However what I am trying to accomplish is the opposite of what your solution is for. I have a table with values that are constantly changing across time and need to replace those old values with the most recent values.
– Claudio Mazzoni
Mar 8 at 5:10
Dennis, thank you so much for your response. However what I am trying to accomplish is the opposite of what your solution is for. I have a table with values that are constantly changing across time and need to replace those old values with the most recent values.
– Claudio Mazzoni
Mar 8 at 5:10
Please host cab files somewhere or create a colander shared notebook
– Dennis Lyubyvy
Mar 9 at 1:34
Please host cab files somewhere or create a colander shared notebook
– Dennis Lyubyvy
Mar 9 at 1:34
add a comment |
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%2f55054107%2fchange-a-dataframe-timeseries-such-that-it-only-contains-the-most-updated-value%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
