Mixing
how to remove duplication from a CSVfile by making some checks?
4
I have a CSV like:
col-1(ID) col-2(val-List)
1 [1]
1 [1,2,3]
2 [1,2]
2 [1]
3 [10]
3 [10]
and I want to remove duplicate from this file and at the end, I just need a single row which has greater list length, like:
edited:
I want to keep a single row if there are those rows which have the same ID and same length of the inner list.
col-1(ID) col-2(Val-List)
1 [1,2,3]
2 [1,2]
3 [10]
I tried a lot but no luck:
I'm giving try by using CSV module but do not have an idea that how should I maintain the length of previous Val-List and compare with next matching ID.
import csv
list_1 =
with open('test123.csv', 'r', encoding='latin-1') as file:
csvReader = csv.reader(file, delimiter=',')
for row in csvReader:
key = (row[0])
# but how should I use this id to get my desired results?
python
asked Nov 19 '18 at 16:20
GigaByte
13911
add a comment |
4
I have a CSV like:
col-1(ID) col-2(val-List)
1 [1]
1 [1,2,3]
2 [1,2]
2 [1]
3 [10]
3 [10]
and I want to remove duplicate from this file and at the end, I just need a single row which has greater list length, like:
edited:
I want to keep a single row if there are those rows which have the same ID and same length of the inner list.
col-1(ID) col-2(Val-List)
1 [1,2,3]
2 [1,2]
3 [10]
I tried a lot but no luck:
I'm giving try by using CSV module but do not have an idea that how should I maintain the length of previous Val-List and compare with next matching ID.
import csv
list_1 =
with open('test123.csv', 'r', encoding='latin-1') as file:
csvReader = csv.reader(file, delimiter=',')
for row in csvReader:
key = (row[0])
# but how should I use this id to get my desired results?
python
asked Nov 19 '18 at 16:20
GigaByte
13911
Are you sure you want ',' as delimiter? This way it splits also on the commas which are inside your col-2(val-List)
– arudzinska
Nov 19 '18 at 16:36
Yeah, comma delimiter. I tried by checking the inside list which is Val-list. so far it's good to go.
– GigaByte
Nov 19 '18 at 16:45
add a comment |
4
4
4
I have a CSV like:
col-1(ID) col-2(val-List)
1 [1]
1 [1,2,3]
2 [1,2]
2 [1]
3 [10]
3 [10]
and I want to remove duplicate from this file and at the end, I just need a single row which has greater list length, like:
edited:
I want to keep a single row if there are those rows which have the same ID and same length of the inner list.
col-1(ID) col-2(Val-List)
1 [1,2,3]
2 [1,2]
3 [10]
I tried a lot but no luck:
I'm giving try by using CSV module but do not have an idea that how should I maintain the length of previous Val-List and compare with next matching ID.
import csv
list_1 =
with open('test123.csv', 'r', encoding='latin-1') as file:
csvReader = csv.reader(file, delimiter=',')
for row in csvReader:
key = (row[0])
# but how should I use this id to get my desired results?
python
asked Nov 19 '18 at 16:20
GigaByte
13911
I have a CSV like:
col-1(ID) col-2(val-List)
1 [1]
1 [1,2,3]
2 [1,2]
2 [1]
3 [10]
3 [10]
and I want to remove duplicate from this file and at the end, I just need a single row which has greater list length, like:
edited:
I want to keep a single row if there are those rows which have the same ID and same length of the inner list.
col-1(ID) col-2(Val-List)
1 [1,2,3]
2 [1,2]
3 [10]
I tried a lot but no luck:
I'm giving try by using CSV module but do not have an idea that how should I maintain the length of previous Val-List and compare with next matching ID.
import csv
list_1 =
with open('test123.csv', 'r', encoding='latin-1') as file:
csvReader = csv.reader(file, delimiter=',')
for row in csvReader:
key = (row[0])
# but how should I use this id to get my desired results?
python
python
asked Nov 19 '18 at 16:20
GigaByte
13911
asked Nov 19 '18 at 16:20
GigaByte
13911
edited Nov 21 '18 at 10:34
asked Nov 19 '18 at 16:20
GigaByte
13911
asked Nov 19 '18 at 16:20
GigaByte
13911
asked Nov 19 '18 at 16:20
GigaByte
13911
13911
Are you sure you want ',' as delimiter? This way it splits also on the commas which are inside your col-2(val-List)
– arudzinska
Nov 19 '18 at 16:36
Yeah, comma delimiter. I tried by checking the inside list which is Val-list. so far it's good to go.
– GigaByte
Nov 19 '18 at 16:45
add a comment |
Are you sure you want ',' as delimiter? This way it splits also on the commas which are inside your col-2(val-List)
– arudzinska
Nov 19 '18 at 16:36
Yeah, comma delimiter. I tried by checking the inside list which is Val-list. so far it's good to go.
– GigaByte
Nov 19 '18 at 16:45
Are you sure you want ',' as delimiter? This way it splits also on the commas which are inside your col-2(val-List)
– arudzinska
Nov 19 '18 at 16:36
Are you sure you want ',' as delimiter? This way it splits also on the commas which are inside your col-2(val-List)
– arudzinska
Nov 19 '18 at 16:36
Yeah, comma delimiter. I tried by checking the inside list which is Val-list. so far it's good to go.
– GigaByte
Nov 19 '18 at 16:45
Yeah, comma delimiter. I tried by checking the inside list which is Val-list. so far it's good to go.
– GigaByte
Nov 19 '18 at 16:45
add a comment |
1 Answer
1
active
oldest
votes
3
Why not let pandas do the work?
import pandas
# Read in the CSV
df = pandas.read_csv('test123.csv', encoding='latin-1')
# Compute the list lengths
df['lst_len'] = df['col-2(val-List)'].map(lambda x: len(list(x)))
# Sort in reverse order by list lengths
df = df.sort_values('lst_len', ascending=False)
# Drop duplicates, preserving first (longest) list by ID
df = df.drop_duplicates(subset='col-1(ID)')
# Remove extra column that we introduced, write to file
df = df.drop('lst_len', axis=1)
df.to_csv('clean_test123.csv', index=False)
answered Nov 19 '18 at 16:24
rvd
43117
Are you sure this performs the logic to keep only the row that has the longestcol-2(Val-List)? As I understood this is what OP wants.
– arudzinska
Nov 19 '18 at 16:41
yeah I want to keep the row which has longest col-2(Val-List)
– GigaByte
Nov 19 '18 at 16:46
Understood and edited accordingly.
– rvd
Nov 19 '18 at 16:50
I tried this and its working but now having one exception which i haven't explained above.
– GigaByte
Nov 19 '18 at 17:00
can you please check the edit and suggest me a solution in above script?
– GigaByte
Nov 19 '18 at 17:08
|
show 4 more comments
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
});
}
});
draft saved
draft discarded
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%2f53378775%2fhow-to-remove-duplication-from-a-csvfile-by-making-some-checks%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
3
Why not let pandas do the work?
import pandas
# Read in the CSV
df = pandas.read_csv('test123.csv', encoding='latin-1')
# Compute the list lengths
df['lst_len'] = df['col-2(val-List)'].map(lambda x: len(list(x)))
# Sort in reverse order by list lengths
df = df.sort_values('lst_len', ascending=False)
# Drop duplicates, preserving first (longest) list by ID
df = df.drop_duplicates(subset='col-1(ID)')
# Remove extra column that we introduced, write to file
df = df.drop('lst_len', axis=1)
df.to_csv('clean_test123.csv', index=False)
answered Nov 19 '18 at 16:24
rvd
43117
Are you sure this performs the logic to keep only the row that has the longestcol-2(Val-List)? As I understood this is what OP wants.
– arudzinska
Nov 19 '18 at 16:41
yeah I want to keep the row which has longest col-2(Val-List)
– GigaByte
Nov 19 '18 at 16:46
Understood and edited accordingly.
– rvd
Nov 19 '18 at 16:50
I tried this and its working but now having one exception which i haven't explained above.
– GigaByte
Nov 19 '18 at 17:00
can you please check the edit and suggest me a solution in above script?
– GigaByte
Nov 19 '18 at 17:08
|
show 4 more comments
3
Why not let pandas do the work?
import pandas
# Read in the CSV
df = pandas.read_csv('test123.csv', encoding='latin-1')
# Compute the list lengths
df['lst_len'] = df['col-2(val-List)'].map(lambda x: len(list(x)))
# Sort in reverse order by list lengths
df = df.sort_values('lst_len', ascending=False)
# Drop duplicates, preserving first (longest) list by ID
df = df.drop_duplicates(subset='col-1(ID)')
# Remove extra column that we introduced, write to file
df = df.drop('lst_len', axis=1)
df.to_csv('clean_test123.csv', index=False)
answered Nov 19 '18 at 16:24
rvd
43117
Are you sure this performs the logic to keep only the row that has the longestcol-2(Val-List)? As I understood this is what OP wants.
– arudzinska
Nov 19 '18 at 16:41
yeah I want to keep the row which has longest col-2(Val-List)
– GigaByte
Nov 19 '18 at 16:46
Understood and edited accordingly.
– rvd
Nov 19 '18 at 16:50
I tried this and its working but now having one exception which i haven't explained above.
– GigaByte
Nov 19 '18 at 17:00
can you please check the edit and suggest me a solution in above script?
– GigaByte
Nov 19 '18 at 17:08
|
show 4 more comments
3
3
3
Why not let pandas do the work?
import pandas
# Read in the CSV
df = pandas.read_csv('test123.csv', encoding='latin-1')
# Compute the list lengths
df['lst_len'] = df['col-2(val-List)'].map(lambda x: len(list(x)))
# Sort in reverse order by list lengths
df = df.sort_values('lst_len', ascending=False)
# Drop duplicates, preserving first (longest) list by ID
df = df.drop_duplicates(subset='col-1(ID)')
# Remove extra column that we introduced, write to file
df = df.drop('lst_len', axis=1)
df.to_csv('clean_test123.csv', index=False)
answered Nov 19 '18 at 16:24
rvd
43117
Why not let pandas do the work?
import pandas
# Read in the CSV
df = pandas.read_csv('test123.csv', encoding='latin-1')
# Compute the list lengths
df['lst_len'] = df['col-2(val-List)'].map(lambda x: len(list(x)))
# Sort in reverse order by list lengths
df = df.sort_values('lst_len', ascending=False)
# Drop duplicates, preserving first (longest) list by ID
df = df.drop_duplicates(subset='col-1(ID)')
# Remove extra column that we introduced, write to file
df = df.drop('lst_len', axis=1)
df.to_csv('clean_test123.csv', index=False)
answered Nov 19 '18 at 16:24
rvd
43117
edited Nov 19 '18 at 16:50
answered Nov 19 '18 at 16:24
rvd
43117
answered Nov 19 '18 at 16:24
rvd
43117
answered Nov 19 '18 at 16:24
rvd
43117
43117
Are you sure this performs the logic to keep only the row that has the longestcol-2(Val-List)? As I understood this is what OP wants.
– arudzinska
Nov 19 '18 at 16:41
yeah I want to keep the row which has longest col-2(Val-List)
– GigaByte
Nov 19 '18 at 16:46
Understood and edited accordingly.
– rvd
Nov 19 '18 at 16:50
I tried this and its working but now having one exception which i haven't explained above.
– GigaByte
Nov 19 '18 at 17:00
can you please check the edit and suggest me a solution in above script?
– GigaByte
Nov 19 '18 at 17:08
|
show 4 more comments
Are you sure this performs the logic to keep only the row that has the longestcol-2(Val-List)? As I understood this is what OP wants.
– arudzinska
Nov 19 '18 at 16:41
yeah I want to keep the row which has longest col-2(Val-List)
– GigaByte
Nov 19 '18 at 16:46
Understood and edited accordingly.
– rvd
Nov 19 '18 at 16:50
I tried this and its working but now having one exception which i haven't explained above.
– GigaByte
Nov 19 '18 at 17:00
can you please check the edit and suggest me a solution in above script?
– GigaByte
Nov 19 '18 at 17:08
Are you sure this performs the logic to keep only the row that has the longest
col-2(Val-List)? As I understood this is what OP wants.– arudzinska
Nov 19 '18 at 16:41
Are you sure this performs the logic to keep only the row that has the longest
col-2(Val-List)? As I understood this is what OP wants.– arudzinska
Nov 19 '18 at 16:41
yeah I want to keep the row which has longest col-2(Val-List)
– GigaByte
Nov 19 '18 at 16:46
yeah I want to keep the row which has longest col-2(Val-List)
– GigaByte
Nov 19 '18 at 16:46
Understood and edited accordingly.
– rvd
Nov 19 '18 at 16:50
Understood and edited accordingly.
– rvd
Nov 19 '18 at 16:50
I tried this and its working but now having one exception which i haven't explained above.
– GigaByte
Nov 19 '18 at 17:00
I tried this and its working but now having one exception which i haven't explained above.
– GigaByte
Nov 19 '18 at 17:00
can you please check the edit and suggest me a solution in above script?
– GigaByte
Nov 19 '18 at 17:08
can you please check the edit and suggest me a solution in above script?
– GigaByte
Nov 19 '18 at 17:08
|
show 4 more comments
draft saved
draft discarded
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
draft saved
draft discarded
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%2f53378775%2fhow-to-remove-duplication-from-a-csvfile-by-making-some-checks%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
Are you sure you want ',' as delimiter? This way it splits also on the commas which are inside your col-2(val-List)
– arudzinska
Nov 19 '18 at 16:36
Yeah, comma delimiter. I tried by checking the inside list which is Val-list. so far it's good to go.
– GigaByte
Nov 19 '18 at 16:45