Questions tagged [excel]
Excel is a spreadsheet based software, for numerical input, developed by Microsoft and part of the broader set of the Microsoft Office Suite.
61
questions
0
votes
0answers
14 views
I want to prepare meaningful charts and polarization chart with the data I have
I am a bachelor student that is preparing a project for my network science course. I have some data that I got via questionnaire. This data shows people that they prefer studying online or in presence,...
0
votes
0answers
5 views
Horizontal Bar Char Issues in Tableau with Excel Spreadsheet Beginner Visualization Question
Hello i did a good graphic vertical chart with a dark theme.
but now i want to do it in horizontal chart and i have some issues.
i had used too much drag and drop and move option in Tableau maybe this ...
0
votes
0answers
5 views
Using VLOOKUP to match each value of cell (cell has multiple values separated by comma) in table A with values in table B
Problem Statement:
Table 1: It has multiple values in each cell of Column B.
Table 2: Is the reference table Employees and their category is mentioned
Output Required:
Table 3: This is what the ...
0
votes
0answers
22 views
0
votes
0answers
164 views
Excel error may be caused by pandas writing or big data? advise needed
I am reading multiple xml files extracting some data then forming a pandas Dataframe with my data. These are the main steps that I do:
open an xml file
extract some elements
create a pandas dataframe ...
-1
votes
1answer
28 views
How can I get all column values from a panda dataframe and save them into one string [closed]
this question has been ask couple of times but none of the answer is working for that what I want.
For example I have this excel Dokument:
...
0
votes
2answers
19 views
Perform fuzzy lookup to partially match the address column
I have two separate datasets (eg. dataset1.xlsx and dataset2.xlsx). Dataset1 has 2 columns, serial number and service address. Similarly dataset2 has 2 columns service address and customer number. The ...
2
votes
1answer
32 views
Cleaning data with two fields mixed in the same column?
I am working on a template for a dashboard that is giving me some serious trouble. The format the data comes in is highly unstructured. In the image below, circled items are "Site Names", ...
0
votes
2answers
36 views
Calculating Median and Mode of numerical variables for different subgroups in R
I have customer call data and I want to get the median and mode for the call success rate for different subgroups.
My variables are: Customer ID, Employment Status (Retired, Employed, Unemployed), ...
0
votes
1answer
32 views
CSVs: how to output missing data to make processing easier?
If supplying data (that can be either string or numeric) via CSVs, what's a good strategy for marking that a value is missing?
Some non-empty sentinel, like NA
...
0
votes
0answers
31 views
error in pandas dataframe when trying to convert dtype object to float
I am trying to aggregate data converted from an xlsx file to a dataframe but it is all dtype object.
I tried to convert it to strings and float so I can aggregate it but this is the error I get :
<...
1
vote
1answer
126 views
Help improving my “read_excel” execution time in python. My code reads slowly
My first question here so please bare with me.
I'm trying to feed my neural network with training data read in from an excel file. It works perfectly fine when i have less than 50 rows in the sheet. ...
0
votes
2answers
27 views
How does Excel obtain the R² determination coefficient in an exponetial regression?
How does Excel obtain the R² determination coefficient in an exponetial regression? This may seem a silly question but Excel actually shows a R² coefficient for non-linear regressions. How does Excel ...
0
votes
1answer
120 views
I have a pandas dataframe and i need to clear all the special characters other than space
Input:
import pandas as pd
df=pd.read_excel("OCRFinal.xlsx")
df['OCR_Text']=df['OCR_Text'].str.replace(r'\W+'," ")
print(df['OCR_Text'])
Output:
The excel ...
1
vote
1answer
93 views
Comparing excel data sets in Pandas
Pretty new to Python, but as an SEO I'm looking at the benefits of using notebooks in my workflow.
I've got two excel files which I've cleaned and imported into a new notebook using pandas.
I'm ...
1
vote
0answers
17 views
On ttest why t-stat is greater than one?
I have my data in two culomns as below:
On excel did this ttest and this is my result:
I can not Understand why Hypothesized Mean Difference =0 but tStat = -1.6367...?
I mean my NULL/Zero ...
1
vote
1answer
22 views
How to group by ID, and output without repeating IDs? [closed]
Input
UserID Profile
101 apple
102 banana
103 pie
101 banana
102 pine
103 berry
Output
...
1
vote
1answer
37 views
Relationship between two sets of dates
My wife has seizures a few times a month and takes medication for the seizures once a month. So I have two sets of dates - the dates of the seizures and the dates she got her medication.
I'm trying ...
2
votes
2answers
2k views
Pandas datetime error when reading from excel file
I am trying to read an excel file that has two columns using pandas.
This is how the data looks in excel file:
...
0
votes
3answers
84 views
What is the better to pre-process data, MS-Excel or Python/R [closed]
While handling data, many anomalies occur that might change the results. For example, outliers, missing values, etc. So in this context, what is the better tool to pre process data? A visual tool like ...
2
votes
0answers
134 views
Tools for reading data from large, irregular csv files (aka excel file hell)
I have a csv file with 1000 columns and 50 rows that was collected over a few months. Embedded randomly in the file are ~1000 small datasets with semi-standard formats (some columns differ, so does ...
1
vote
1answer
114 views
Starting search from rightmost column and moving left pandas
I'm very new to pandas and I'm trying to implement the code to search for something on an excel spreadsheet.
Basically, the most recent price can be found on the furthest right column named ...
1
vote
1answer
638 views
how to implement Excel vlookup on Python
I have a dataset A and dataset B, both data has a column in common which is the ID, i want to check if dataset A ID values can be found in dataset B ID values. Also get the records of dataset B that ...
0
votes
1answer
760 views
VlookUp: how to loop it? [closed]
I am not an experienced user of Excel because I usually use python but I am obliged to use it this time.
This is the big picture of my case.
The problem is:
I have a sheet with a column with some ...
2
votes
1answer
29 views
Adding Group Average Line to Bar Chart
I am trying to create a PivotChart in the form of a bar chart and I would like to add a group average line on the chart.
I have this (sample) table in my PowerPivot Data Model:
Group averages are A: ...
1
vote
1answer
3k views
Changing Timestamp format for Date-Time in Excel/Pandas/Python?
I have a excel data with time stamp format like this "2019-06-10T14:05:00+05:30"
[YYYY-MM-DDTHH:MM:SS], I want it to be converted into "2019-06-10 14:05" [YYYY-MM-DD HH:MM],
Is it possible to convert ...
2
votes
5answers
92 views
30 Years Of Excel Test Data
I am a CS intern at an industrial company that has 30 years of excel files that need to be analyzed. Looking at the data, only a fraction of the files need to be looked at and used. After those files ...
1
vote
0answers
121 views
Cleaning Excel Data in Merged Cells [closed]
I need to use an Excel file that have many merged cells with no specific order.
I guess that the file creator thought that it would be more readable to merge the cells that have the same value.
...
1
vote
1answer
33 views
Different t-test results from R and LibreCalc
while I was trying to apply one-tailed t-test to two vector of data on R, I just wanted to apply same thing on librecalc also and then I noticed that p-values are different. Here is a summary what I ...
0
votes
4answers
88 views
How should we manage large database
We currently manage a large volume of economic data in Excel in my organisation. All of the data is downloaded from different online databases into Excel spreadsheets (one for each data frequency ...
0
votes
1answer
31 views
1
vote
1answer
217 views
Importing Excel format data into R/R Studio and using glmnet package?
I have no problem importing Excel formatted data into R/R Studio and use all other R packages that I use. But, when I want to use the glmnet package to develop a regularization model, I invariably ...
1
vote
0answers
11k views
Pandas Excel writer update existing excel file [closed]
How do I update an excel file and not overwrite it?
...
8
votes
4answers
38k views
How to Write Multiple Data Frames in an Excel Sheet
I have multiple data frames with same column names. I want to write them together to an excel sheet stacked vertically on top of each other. And between each, there will be a text occupying a row. ...
0
votes
1answer
998 views
Categorical data into numeric in excel
I have a large dataset and I would like to convert these categorical data into numeric in binary form to perform k means clustering in R. However, I get an error in value. This is the formula that I ...
3
votes
2answers
127 views
Expectations of the skill set of a Data Scientist [closed]
Although there are more powerful tools than Excel which a data scientist should know how to use, would it be expected that someone indicating they are a Data Scientist know how to use Excel?
1
vote
0answers
45 views
Excel All Possible Combinations for MacOS
Is there a formula in Excel to calculate all possible combinations for multiple lists for MacOS. For instance, I am trying to draft a fantasy team and I have the following combinations:
...
4
votes
3answers
659 views
How to predict whether or not a customer will renew
I have a dataset of customer contracts that specify a start date and if applicable an end date. Each month a customer is up for renewal. Below is an example of how the data is organized in excel:
<...
1
vote
1answer
120 views
Drop NA Values with SAS
I have an xlsx file that I wish to delete the NA values in SAS. I'm new to ...
2
votes
1answer
662 views
Calculate weighted mean for two columns and hundreds of rows?
I'm trying to teach myself basics of R and I couldn't find the answer:
Say, I have a csv file and I want to calculate weighted mean for each subject such that I have a mean mu = 0.015*1030+0.16*26930+...
1
vote
1answer
56 views
Problem when exporting scored labels to excel
I have done a regression using Azure ML Studio and it ok. The r squared is 95% on the test sample.
When predicting, the results look ok when visualizing the data.
The results appear as expected, with ...
-2
votes
1answer
281 views
Adding values to new row to a csv file in Python
So what I'm trying to do is iterate over a grayscale image and look at each pixel and write that pixel's grayscale value to the second row (the first row being the pixel number). The following code ...
8
votes
1answer
414 views
How far can one go with excel? [closed]
in my business we handle all analytics through Excel. This includes mostly scheduling, production planning and accounting operations. We currently are looking into adding a bit of predictive modelling ...
1
vote
1answer
75 views
How can discard “peaks” values from a chart?
I have this chart
where you can see that some points are far away from the "trend" of the curve. The chart represents $5,000$ values where each value corresponds to a specific hour of a day, month ...
0
votes
2answers
27k views
Search and Replace in specific column of csv with python
I created a program that search and replaces over an entire csv file but I need to make so it is column specific. Is there a way to only search via a column. Here is my code, I am pretty new to python ...
2
votes
0answers
60 views
Orange “Scatter plot” widget to excel, best way
So I have chosen "Orange" for my research as a main app for clustering ant it's analysis.
Excel 2016 is primary app used for dashboard (via "pivot table").
Problem - how to add graphs from "scatter ...
0
votes
1answer
471 views
How can I transpose a high dimensional dataset?
I have a (.csv) file with more than 35,000 rows and 100 columns, where the rows represent the attributes and columns represent the instances. In Excel the maximum number of columns is 16,384. ...
1
vote
1answer
62 views
How to manipulate this column of less than/greater than?
I have a dataset in which one column is as given in the picture.
What will be the best way to handle such a column?
0
votes
2answers
2k views
Compound Annual Growth Rate Function in R Statistics
I need to calculate the compound annual growth rate for two periods of time: 2014 & 2009. I'm currently working with Excel, I have two columns with data for each year (One column with only data of ...
1
vote
2answers
56 views
Find value and return row in excel spreadsheet with over 1 mil rows [closed]
I have an excel spreadsheet that has over 1 million rows and about 120 columns. I frequently need to find a value or a part of a value (value is name or number) and return the row where the value is ...
