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.

Filter by
Sorted by
Tagged with
0 votes
0 answers
68 views

Amazon Kendra's performance on data rich in text vs data rich in numbers

I used Amazon's Kendra to build a semantic search engine. I took 2 datasets for this task - csv & excel. The csv dataset is mostly text. The excel dataset is mostly numbers. The data to be used is ...
Apoorva's user avatar
  • 275
0 votes
0 answers
8 views

Displaying PowerBI and Microsoft Excel Files for Data Analytics Portfolio

I tried uploading a PowerBI file and an Excel file to GitHub, but it doesn't render them as GitHub would a Jupyter Notebook, for example. What service can I upload the aforementioned files to for a ...
Faith Alone's user avatar
0 votes
0 answers
41 views

How can GPT4 be used to create structured meta-data for unstructured data?

I often ingest unstructured datasets in Excel format, create meta-data, and transform the data in R to create a structured dataset. This includes normalization, so that there is little redundancy in ...
ahorn's user avatar
  • 101
0 votes
0 answers
16 views

Help creating a SCORING model/formula.......am I on the right track?

Trying to create a basic machine scoring model, that takes in 4 parameters: Number of maintenance events Years of life left Manufacturer support (bit - either yes or no) Visual condition The ...
manavjn's user avatar
0 votes
1 answer
193 views

Python 3.11 | How to have Python collect the values of a certain cell in a readable Excel file IF the cell's row has a certain value in another column

I'm quite new to this data-collecting bit of coding, just trying to make a process a bit more automated. Currently, I have this excel sheet that python can read just fine, however, I am trying to find ...
Fen's user avatar
  • 1
0 votes
0 answers
12 views

How to use text-to-columns or an equivalent operation on records in 1 field with varying lengths & no clear delimiters built into them

If this question in inappropriate for Data Science Stack Exchange, but there is another one within this stack it would be appropriate for, please let me know! It looks borderline to me. A member of my ...
Marlen's user avatar
  • 157
0 votes
0 answers
22 views

PowerQuery/BI: How to group sub-populations of variable size and perform calculations

I have data for planned and actual produced quantities for a number of items (articles) and batches. Please see example data in image and attached table. I wish to calculate: Note: For a specific item ...
Bartiez's user avatar
1 vote
1 answer
52 views

Converting numeric column to range

I have this data below. I want to convert temperature column into range of data like class interval and get the sum of sales in that range. How can I achieve that?
Mr Ak's user avatar
  • 13
0 votes
1 answer
623 views

Network Visualisation (Python/Excel)

I have a large data set similar to that in the screenshot below: and I want to visualise the whole data set like the diagram below (made with a lot of effort in PowerPoint!) Is there any way to do ...
Barry O'Keeffe's user avatar
3 votes
3 answers
1k views

Giving each person in order their top choice which is still available in Google Sheets

The problem I want to solve is my residential building's garage choices. There will be a random distribution of parking spaces. I thought that it would be better if each person writes down which ...
Heleno Paiva's user avatar
4 votes
1 answer
278 views

How to interpret standard deviation calculated by excel

I have some percentage values in my excel spreadsheet like: When i use the STDEV function to calculate the standard deviation with these values, i get ...
heresthebuzz's user avatar
1 vote
1 answer
662 views

Markov Process and transition matrix

I would like to find some good courses but also a quick response on how to model transition matrix given the states. Imagine having 4 states and the following array [1,2,4,1,3,4,2 etc etc]. What ...
minattosama's user avatar
0 votes
2 answers
933 views

How to find duplicate rows in a column then find out if two cells in another column sum up to a third cell in an Excel tab in Python?

I need to find all duplicate rows (string values) in "Name" column and then find out if two numerical values in "Amount" column sum up to a third value also in the "Amount&...
itsmenick212's user avatar
0 votes
1 answer
3k views

Deleting a Column in a csv or Excel file using Pandas

I am trying to delete a column from my csv file (column 'A' called "Film Number") but have tried numerous variations of code and while it deletes the column in the dataFrame it doesn't do so ...
Nat's user avatar
  • 11
1 vote
1 answer
53 views

How to make the values in different columns in the correct order based on another data frame (mapping) in Python Pandas

I am pretty new to Python and Pandas and I struggle with combining a messy dataframe from excel with a mapping. I have tried to find some solutions on the Internet, however with no success. My first ...
sianko's user avatar
  • 11
0 votes
1 answer
46 views

How can I extract an optimized matrix of correlations from a larger data set?

Consider an Excel sheet containing a matrix of correlations between individual stocks and the combined portfolio as a whole: How can I extract an optimized matrix such that most stocks have a low ...
Zesty's user avatar
  • 103
1 vote
0 answers
45 views

Why is a column detected as text and not as categorical when opening excel file?

I have the following values in an excel sheet Aguascalientes Baja California Baja California Sur Campeche Chiapas Chihuahua Ciudad de México Coahuila Colima Durango Estado de México Guanajuato ...
Gabriel Barrera's user avatar
1 vote
0 answers
13 views

Evaluation of reliability of suppliers by on time delivery

I am trying to create a weight that can help me properly evaluate the reliability of a company’s on time delivery ratios. I’m using historic delivery data. For example, comparing company A that ...
Kate Nguyen's user avatar
0 votes
3 answers
362 views

Calculating optimal 'phase shift' when comparing two linear datasets to maximise correlation?

Let's say I have two columns of data, when graphed they look like this: This does not have a particularly wonderful correlation. On the other hand if I shift one graph 10 datapoints relative to the ...
Mr. Boy's user avatar
  • 53
0 votes
0 answers
22 views

Importing data directly into excel sheet, from data accessed through Material Project opensource API's

I had a general doubt. If I want to extract some data, say the information regarding a particular chemical using API of material projects(a site that has open-source info on, say elements) available ...
oprah's user avatar
  • 1
1 vote
0 answers
88 views

How to maximize a log linear regression equation satisfying a constraint?

I have a log linear equation of the form $y = w_1(\log{X1}) + w_2(\log{X2}) + ... + w_n(\log{Xn})$. How can I find the value of X's that maximize the value of y subject to a constraint $(X_1+X_2+...+...
my_cse lab's user avatar
0 votes
0 answers
49 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,...
Tuna Yüce's user avatar
1 vote
0 answers
110 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 ...
Arvi's user avatar
  • 11
0 votes
0 answers
2k 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 ...
Monta's user avatar
  • 1
-1 votes
1 answer
2k 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: ...
Loretta's user avatar
  • 27
0 votes
3 answers
341 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 ...
yunus_rouhan's user avatar
2 votes
1 answer
107 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", ...
SchrodingersStat's user avatar
0 votes
2 answers
488 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), ...
ColRow's user avatar
  • 53
0 votes
1 answer
55 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 ...
Michal Charemza's user avatar
1 vote
0 answers
22 views

Histogram with financial (decimal) amounts vs. normal numeric

Take the following historgram data: This is an item of "bin size" 1 from 0 onwards. However, I do not think this looks appropriate, as every time I have seen a histogram (or someone has requested it),...
David542's user avatar
  • 111
1 vote
1 answer
2k 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. ...
Casper's user avatar
  • 13
0 votes
2 answers
71 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 ...
Yván Ecarri's user avatar
0 votes
1 answer
1k 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 ...
Hemananth's user avatar
1 vote
1 answer
393 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 ...
Stuart Houghton's user avatar
1 vote
0 answers
25 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 ...
motevalizadeh's user avatar
1 vote
1 answer
45 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 ...
harish215s's user avatar
1 vote
1 answer
193 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 ...
AndyK's user avatar
  • 11
3 votes
2 answers
5k 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: ...
Uday T's user avatar
  • 322
0 votes
3 answers
241 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 ...
Atharva Kale's user avatar
2 votes
0 answers
243 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 ...
R Greg Stacey's user avatar
1 vote
1 answer
210 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 ...
Luke's user avatar
  • 21
1 vote
1 answer
856 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 ...
UZOCHUKWU URUAKPA's user avatar
0 votes
1 answer
4k 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 ...
KeyPi's user avatar
  • 135
2 votes
1 answer
360 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: ...
dreamerboy's user avatar
1 vote
1 answer
6k 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 ...
Uday T's user avatar
  • 322
2 votes
5 answers
107 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 ...
Stephen Collins's user avatar
1 vote
1 answer
43 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 ...
WhoCares's user avatar
  • 113
0 votes
4 answers
116 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 ...
StephenB's user avatar
2 votes
1 answer
47 views

Analyze additions and subtractions to/from a data set

I have a data set in the following form: ...
Berbatov's user avatar
  • 121
1 vote
1 answer
320 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 ...
Sympa's user avatar
  • 121